SELECT and WHERE

Game: chapter I

Every SQL query starts here: say which columns you want, from which table, and which rows to keep.

Reading a table

A relational database stores its data in tables. A table has named columns, and each row is one record: one customer, one product, one order. To read a table you write a SELECT statement. The words after SELECT name the columns you want back, and the word FROM names the table they come from. The star, *, means every column, in the order the table defines them.

The examples on this page, like every page of the handbook, run on the small bakery database described on the bakery database page. Its staff table lists the six people who work there. Each example ends with ORDER BY id, which lists the rows in id order; without it the database may return them in any order, as the ORDER BY page explains.

SELECT name, role
FROM staff
ORDER BY id;

The order of the columns in the result is the order you wrote them, not the order in the table.

Keeping only some rows: WHERE

Most of the time you do not want the whole table. The WHERE clause sets a condition, and the database keeps only the rows for which the condition is true. A condition compares a column with a value or with another column, using =, <> (not equal; != also works), <, <=, > and >=. Text values go between single quotes; numbers do not.

SELECT name, price, price * 2 AS two_of_them
FROM product
WHERE category = 'pastry'
ORDER BY id;

Notice that the database reads FROM first, then WHERE, and only then SELECT. That is why the condition can use any column of the table, even one you do not display. The example also computes a new column, price * 2, and names it with AS: the name is an alias, which labels the column in the result and changes nothing in the table.

Combining conditions

Conditions combine with AND (both must be true) and OR (at least one must be true), and NOT turns a condition around. AND binds more tightly than OR, exactly as multiplication binds more tightly than addition, so when you mix them, add parentheses to say what you mean. Two shortcuts save typing: IN (...) tests against a list of values, and BETWEEN a AND b tests a range that includes both ends.

SELECT name, category, price
FROM product
WHERE (category = 'bread' OR category = 'cake')
  AND price BETWEEN 3.2 AND 3.9
ORDER BY id;

The Carrot cake slice (3.2) and the Lemon tart (3.9) sit exactly on the bounds, and both are kept.

Dates in SQLite are usually stored as text in the form YYYY-MM-DD. Because that form sorts in calendar order, you can compare dates as ordinary text: day >= '2026-03-05' keeps every order from the fifth of March onwards. The same is true of times written HH:MM with a leading zero; the dates and times page explains why. The query below keeps the orders of the first weekend, Saturday 7 and Sunday 8 March, taken before nine in the morning.

SELECT id, customer_id, day, time
FROM orders
WHERE day IN ('2026-03-07', '2026-03-08')
  AND time < '09:00'
ORDER BY id;

Missing values: IS NULL

A column may hold no value at all. SQL writes that as NULL, which means "unknown" rather than zero or an empty string. Nothing is ever equal to NULL, not even another NULL, so email = NULL keeps no rows. To find missing values, write IS NULL; to exclude them, IS NOT NULL. In the bakery, a customer who never gave an email has no email. The NULL page covers the rest of the rules.

SELECT name, town
FROM customer
WHERE email IS NULL
ORDER BY id;

A way of working

Start wide and narrow down. Look at a few rows of the table first, then add one condition at a time and check that each one removes what you expected. When a result is empty, remove the last condition and look at what was there before it. Reading the result after every step is faster than writing a long query and wondering why it returns nothing.

Grammar: double quotes

A query with a red edge is one the game's terminal refuses: the answer under it is the terminal's own, and the query after it is the correct version.

Single quotes make a text value: 'bread'. Double quotes make a name, of a column or a table: "category". So "bread" asks for a column called bread, and there is none. Names in double quotes also keep their exact case, so "Category" is not category.

SELECT name
FROM product
WHERE category = "bread";

SELECT name
FROM product
WHERE category = 'bread'
ORDER BY id;

The same goes for aliases: an alias is a name, so write AS total, or AS "Total" to keep a capital or a space, never AS 'total'. Names are quoted with double quotes only, not with backticks or square brackets.

Grammar: one equals sign

SQL compares with a single =. The double == belongs to programming languages, not to SQL.

SELECT name
FROM product
WHERE id == 4;

SELECT name
FROM product
WHERE id = 4;

Not equal is <> or !=; both are accepted.

Grammar: types

Every column has a type, written in the schema: whole numbers (INTEGER), decimals (REAL), text (TEXT). A number is compared with a number, text with text; a comparison of two different kinds is an error. So a number is written without quotes, a text value with them, and LIKE only works on text. SUM and AVG need numbers too.

The example below is this terminal's own rule, not PostgreSQL's: PostgreSQL would read the quoted '1' as the number 1 and run the query. The terminal refuses it on purpose, so that a number is always written as a number and its type shows in the query.

SELECT name
FROM customer
WHERE referred_by = '1';

SELECT name
FROM customer
WHERE referred_by = 1
ORDER BY id;

The other way round, a text column against a number, such as day = 20260302, is just as wrong: day holds text, so write day = '2026-03-02', in the column's own format. To use digits stored as text as a number, convert them first with CAST(... AS INTEGER). And || joins text: on two numbers, as in 2 || 3, it is refused; add them with +, or make one of them text first, CAST(2 AS TEXT) || 3.

Grammar: aliases in WHERE and HAVING

An alias given in SELECT (AS double_price) names a column of the result. WHERE and HAVING are worked out before the result exists (see Order of execution), so they cannot use it: repeat the expression instead. ORDER BY and GROUP BY may use an alias, but only on its own, not inside a larger expression (for GROUP BY, which runs before SELECT, this is a PostgreSQL extension to the order of execution).

SELECT name, price * 2 AS double_price
FROM product
WHERE double_price > 7;

SELECT name, price * 2 AS double_price
FROM product
WHERE price * 2 > 7
ORDER BY double_price DESC, id;

Grammar: conditions

WHERE, HAVING, ON and each WHEN of a CASE WHEN need a condition, something true or false. A bare column such as WHERE manager_id is a value, not a condition: write the comparison out. The same holds for each side of AND and OR, and for NOT.

SELECT name
FROM staff
WHERE manager_id;

SELECT name
FROM staff
WHERE manager_id IS NOT NULL
ORDER BY id;

Common mistakes

Using double quotes around text: in SQL, 'bread' is a value, while "bread" is the name of a column, so "bread" asks for a column called bread (see Grammar: double quotes). Writing = NULL instead of IS NULL. Forgetting parentheses when mixing AND and OR, which silently changes the meaning. Comparing text with the wrong case: 'Bread' is not equal to 'bread'. Writing a date in another form, such as '5/3/2026', which compares as text and matches nothing.

Try it: The cheap shelf, on the exercises page.

In the game: Chapter I, The Night of the 17th, practises SELECT and WHERE.