Chapter 6: Retrieving Data with SELECT

The SELECT statement reads data from tables. This chapter covers choosing columns, filtering rows with WHERE, sorting them with ORDER BY, removing duplicates with DISTINCT, and limiting how many rows come back. It works with one table at a time. Chapter 7 covers the functions you can use in a query, chapter 10 covers subqueries, and chapter 12 covers joins and grouping.

The sample table

The examples use the users table from chapters 4 and 5, with two more columns, age and city. Run this script to create the table and add six rows:

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100),
  age INTEGER,
  city VARCHAR(50)
);

INSERT INTO users (id, username, email, age, city)
VALUES (1, 'john_doe', '[email protected]', 34, 'London'),
       (2, 'jane_doe', '[email protected]', 29, 'London'),
       (3, 'bob_smith', '[email protected]', 41, 'Toronto'),
       (4, 'ana_lima', '[email protected]', 25, 'Lisbon'),
       (5, 'raj_patel', '[email protected]', 38, 'Toronto'),
       (6, 'sita_rai', '[email protected]', NULL, 'Kathmandu');

DROP TABLE IF EXISTS removes any users table left over from the earlier chapters, so you can run the script more than once. No age has been recorded for sita_rai, so that row’s age is NULL, which means the value is missing or unknown.

The results in this chapter come from SQLite. Other databases return the same rows, and the places where they differ are pointed out.

Selecting columns

To read every column of every row, use SELECT *:

SELECT * FROM users;

The * means all columns, and FROM names the table to read. A SELECT only reads the table. It never changes it.

Without ORDER BY, which comes later in this chapter, a database can return rows in any order. SQLite returns this table’s rows in id order, but don’t rely on that.

To get only some of the columns, list them in the order you want them:

SELECT username, city FROM users;

SELECT * is handy for a quick look at a table. In application code, name the columns you need, as chapter 11 recommends. The query then returns less data, and its result keeps the same columns when someone adds a column to the table.

Aliases and calculated columns

A column in the result can be a calculation instead of a stored value. AS gives a result column a new name, called an alias:

SELECT username AS name, age, age + 10 AS age_in_ten_years
FROM users;

The alias only renames the column in the result. The table’s column is still called username. Arithmetic with NULL gives NULL, because an unknown age plus 10 is still unknown.

Filtering rows with WHERE

WHERE keeps only the rows for which its condition is true:

SELECT username, age
FROM users
WHERE age > 30;

Chapter 5 used WHERE to choose which rows UPDATE and DELETE change. In a SELECT, it chooses which rows to return. The comparison operators are:

• = equal to
• <> not equal to (most databases also accept !=)
• < less than, and > greater than
• <= less than or equal to, and >= greater than or equal to

Put text values in single quotes:

SELECT username, age
FROM users
WHERE city = 'London';

Whether 'london' would also match depends on the database. MySQL and SQL Server ignore case when they compare text, unless you set them up otherwise. PostgreSQL, Oracle, and SQLite don’t, so in those city = 'london' returns no rows.

Combining conditions

AND keeps a row only if both conditions are true, and OR keeps it if either one is. NOT reverses a condition.

SELECT username, city, age
FROM users
WHERE (city = 'London' OR city = 'Toronto') AND age > 35;

SQL evaluates AND before OR, so without the parentheses the condition would mean city = 'London' OR (city = 'Toronto' AND age > 35), and john_doe and jane_doe would be in the result too. When a condition mixes AND and OR, use parentheses to say what you mean.

IN, BETWEEN, and LIKE

Three operators make common conditions shorter.

IN checks a value against a list, so city IN ('London', 'Lisbon') means the same as city = 'London' OR city = 'Lisbon'. NOT IN keeps the rows whose value isn’t in the list:

SELECT username, city
FROM users
WHERE city NOT IN ('London', 'Toronto');

BETWEEN checks a range, and the range includes both ends:

SELECT username, age
FROM users
WHERE age BETWEEN 25 AND 34;

LIKE matches text against a pattern. In the pattern, % stands for any number of characters, including none, and _ stands for exactly one character:

SELECT username, city
FROM users
WHERE city LIKE 'L%';

Whether LIKE ignores case also varies between databases. SQLite’s LIKE ignores the case of English letters, even though its = doesn’t.

Checking for NULL

You can’t compare NULL with =. The result of any comparison with NULL is unknown, and WHERE keeps only the rows where the condition is true. That’s why sita_rai was missing from the age > 30 result, and the row wouldn’t appear for age <= 30 either. Even WHERE age = NULL returns no rows. Use IS NULL instead:

SELECT username, age
FROM users
WHERE age IS NULL;

IS NOT NULL does the opposite and returns the five rows that have an age.

Sorting with ORDER BY

ORDER BY sorts the result. It sorts in ascending order unless you add DESC after the column:

SELECT username, age
FROM users
ORDER BY age;

Databases disagree about where NULL goes. SQLite, MySQL, and SQL Server sort it before every other value, so sita_rai comes first here. PostgreSQL and Oracle sort it after every other value, so the row would come last.

To sort by more than one column, list them. The second column sets the order of rows that tie on the first, and each column gets its own direction:

SELECT city, username, age
FROM users
ORDER BY city, age DESC;

The cities are sorted from A to Z, and within London and Toronto the older user comes first. DESC applies only to age. You can write ASC for ascending, but it’s the default, so you don’t have to. If two rows have the same values in every ORDER BY column, their order is still not guaranteed.

Removing duplicates with DISTINCT

DISTINCT removes duplicate rows from the result:

SELECT DISTINCT city
FROM users
ORDER BY city;

Six users live in four cities, so the result has four rows. With more than one column, DISTINCT removes a row only if every column matches another row. SELECT DISTINCT city, age would return all six rows here, because no two users share both a city and an age.

Limiting the number of rows

LIMIT returns only the first rows of the result. Together with ORDER BY, it answers questions like “who are the three oldest users?”:

SELECT username, age
FROM users
WHERE age IS NOT NULL
ORDER BY age DESC
LIMIT 3;

The WHERE clause leaves out the user with no age. Without it, PostgreSQL and Oracle would sort that row first in descending order, and it would take one of the three places.

Without ORDER BY, LIMIT returns whichever rows the database finds first. OFFSET skips rows before the limit starts, so LIMIT 3 OFFSET 3 returns the fourth to sixth rows. That’s a common way to show results one page at a time.

LIMIT works in MySQL, PostgreSQL, and SQLite. The SQL standard writes it as FETCH FIRST 3 ROWS ONLY at the end of the query, which PostgreSQL and Oracle accept. SQL Server puts TOP 3 right after SELECT instead: SELECT TOP 3 username, age.

The order of the clauses

The clauses of a SELECT statement must be written in this order:

SELECT columns
FROM table
WHERE condition
ORDER BY columns
LIMIT count;

Every clause after FROM is optional, but the ones you use must come in this order. GROUP BY, which chapter 12 uses, goes between WHERE and ORDER BY.

The database doesn’t run the clauses in the order you write them. It reads the table in FROM, keeps the rows that pass WHERE, works out the columns in SELECT, sorts them with ORDER BY, and then applies LIMIT. That’s why ORDER BY can use an alias from the SELECT list and WHERE can’t:

SELECT username, age + 10 AS age_in_ten_years
FROM users
WHERE age + 10 > 45
ORDER BY age_in_ten_years;

WHERE repeats the calculation, because the alias doesn’t exist yet when WHERE runs. MySQL, PostgreSQL, and SQL Server report an error for WHERE age_in_ten_years > 45. SQLite accepts it, but a query written that way won’t run in most other databases.