SELECT and FROM

Beginner

It's time to write your first line of SQL!

We'll start with the SELECT and FROM operators, which are used to specify which columns you want to retrieve from a table.

For example, let's say you've got a table called students which contains information about some students at a local university:

+----+-------+-------------+-----+----------------+
| id | name  | major       | age | nationality    |
+----+-------+-------------+-----+----------------+
| 1  | Matt  | Geography   | 21  | United Kingdom |
| 2  | Roger | Physics     | 20  | Brazil         |
| 3  | Shawn | English     | 19  | United Kingdom |
| 4  | Bella | Mathematics | 25  | Frane          |
| 5  | Helen | Geography   | 27  | Ghana          |
| 6  | Juno  | English     | 22  | Tuvalu         |
| 7  | Rapha | English     | 22  | Brazil         |
+----+-------+-------------+-----+----------------+

Using SQL, we can query this table (that's just a fancy way of saying we can write code which will fetch rows/columns from this table).

For example, if you wanted to fetch the name and age columns from this table, you’d write the following query:

SELECT name, age
FROM students

and you'd get:

+-------+-----+
| name  | age |
+-------+-----+
| Matt  | 21  |
| Roger | 20  |
| Shawn | 19  |
| Bella | 25  |
| Helen | 27  |
| Juno  | 22  |
| Rapha | 22  |
+-------+-----+

Or, if you wanted to select the id, name and major columns, you’d write:

SELECT id, name, major
FROM students

and you’d get:

+----+-------+-------------+
| id | name  | major       |
+----+-------+-------------+
| 1  | Matt  | Geography   |
| 2  | Roger | Physics     |
| 3  | Shawn | English     |
| 4  | Bella | Mathematics |
| 5  | Helen | Geography   |
| 6  | Juno  | English     |
| 7  | Rapha | English     |
+----+-------+-------------+

If you look closely at these queries, you'll see what the SELECT and FROM clauses are used for:

  1. the SELECT clause is where we specify the columns we want to retrieve, and
  2. the FROM clause is where we specify the name of the table which contains the data we want

Select all columns

If you wanted to retrieve all the columns in a table, it would get pretty boring (and messy) writing out all the column names.

Luckily, SQL provides us with the SELECT * command, which enables us to fetch all the columns in a table. For example, if you write:

SELECT * 
FROM students

you’d get all the columns:

+----+-------+-------------+-----+----------------+
| id | name  | major       | age | nationality    |
+----+-------+-------------+-----+----------------+
| 1  | Matt  | Geography   | 21  | United Kingdom |
| 2  | Roger | Physics     | 20  | Brazil         |
| 3  | Shawn | English     | 19  | United Kingdom |
| 4  | Bella | Mathematics | 25  | Frane          |
| 5  | Helen | Geography   | 27  | Ghana          |
| 6  | Juno  | English     | 22  | Tuvalu         |
| 7  | Rapha | English     | 22  | Brazil         |
+----+-------+-------------+-----+----------------+

Exclude specific columns

Sometimes, it’s quicker to specify the columns we don’t want to retrieve.

In SQL, it’s easy to do this with the EXCEPT operator. For example, if we wanted to retrieve all the columns in our students table except the nationality column, we could write:

SELECT * EXCEPT (nationality)
FROM students

# Result
# +----+-------+-------------+-----+
# | id | name  | major       | age |
# +----+-------+-------------+-----+
# | 1  | Matt  | Geography   | 21  |
# | 2  | Roger | Physics     | 20  |
# | 3  | Shawn | English     | 19  |
# | 4  | Bella | Mathematics | 25  |
# | 5  | Helen | Geography   | 27  |
# | 6  | Juno  | English     | 22  |
# | 7  | Rapha | English     | 22  |
# +----+-------+-------------+-----+

Quick tip

In the examples above, you'll notice that I tend to put each new column name and operator on a new line. However, indentation isn't technically required in SQL. You’ll get the same result if you write:

SELECT 
  id, 
  name, 
  major
FROM students

or:

SELECT id, name, major FROM students

The important thing is the code you write, not the use of newline characters or indentations.

It's also not necessary to capitalise SQL operators/keywords like SELECT and FROM. The following code, for example, is a perfectly valid way to rewrite our query:

select id, name, major from students

Personally, I think it makes your code more readable when you capitalise SQL operators like SELECT and FROM and put each operator on a new line. But that’s just a personal preference. You’re free to write SQL code however you like!

Practice

Now it's your turn! Open the following questions in a new tab to try out the SELECT operator:

Previous Next