More SQL Please: SELECT

A SELECT statement is a read-only query used to retrieve data from a table. It's typically the most commonly used query.

SELECT id, email
FROM users;

| id | email | |----|---------------------------| | 1 | marcus@mcrawford.ca | | 2 | elena@hellocorp.com | | 3 | simon@chenanalytics.io | | 4 | priya@aminiconsulting.com | | 5 | zara@aminiconsulting.com |

You can specify individual columns to retrieve, or * to retrieve all columns.

SELECT *
FROM users;

| id | name_first | name_last | email | |----|------------|-----------|---------------------------| | 1 | Marcus | Crawford | marcus@mcrawford.ca | | 2 | Elena | Moreno | elena@hellocorp.com | | 3 | Simon | Chen | simon@chenanalytics.io | | 4 | Priya | Amini | priya@aminiconsulting.com | | 5 | Zara | Amini | zara@aminiconsulting.com |

Most database engines allow you to perform computations on the columns you retrieve.

Sometimes combining columns can be useful. In the case of SQLite, || is used to concatenate. Other engines may use +, and most engines include a concat() function which serves the same purpose.

Here we concatenate first and last name with a space in between. Single quotes are used to begin and end strings in SQL.

SELECT name_first || ' ' || name_last
FROM users;

| name_first || ' ' || name_last | |--------------------------------| | Marcus Crawford | | Elena Moreno | | Simon Chen | | Priya Amini | | Zara Amini |

The column name returned above is difficult to read. The keyword AS can be used to alias columns with any custom name.

SELECT name_first || ' ' || name_last AS name_full
FROM users;

| name_full | |-----------------| | Marcus Crawford | | Elena Moreno | | Simon Chen | | Priya Amini | | Zara Amini |

If you want to alias a column with a value that includes spaces, surround the name with double quotation marks.

SELECT name_first || ' ' || name_last AS "Full Name"
FROM users;

| Full Name | |-----------------| | Marcus Crawford | | Elena Moreno | | Simon Chen | | Priya Amini | | Zara Amini |

SELECT DISTINCT can be used when you want to return only unique values.

Notice how in the example the last name "Amini" appears once where previous examples included both people with the same last name.

SELECT DISTINCT name_last
FROM users;

| name_last | |-----------| | Crawford | | Moreno | | Chen | | Amini |

Some database engines allow a SELECT clause without a table to allow you to include arbitrary computations in your results. This can be useful as a test query or when using a UNION operator.

Note that if you don't supply an alias for computed columns, the column name will typically default to the same value as the row.

SELECT 
    1, 
    2+2, 
    60 * 60 * 24 AS seconds_per_day, 
    'Hello!';

| 1 | 2+2 | seconds_per_day | 'Hello!' | |---|-----|-----------------|----------| | 1 | 4 | 86400 | Hello! |