WHERE Clauses

SELECT picks columns; WHERE picks rows. By the end of this lesson you can turn a sentence like "long science-fiction books" into a condition the database checks against every row.

One condition

WHERE goes after FROM and holds a condition. The engine tests it on each row and keeps only the rows where it is true. Every example below uses this table:

CREATE TABLE books (title TEXT, genre TEXT, pages INTEGER);
INSERT INTO books VALUES ('Dune', 'scifi', 412);
INSERT INTO books VALUES ('Emma', 'classic', 474);
INSERT INTO books VALUES ('Neuromancer', 'scifi', 271);
INSERT INTO books VALUES ('Hamlet', 'classic', 104);
INSERT INTO books VALUES ('Foundation', 'scifi', 255);

SELECT title FROM books WHERE genre = 'classic';

Emma
Hamlet

The comparison operators are =, <> (not equal; != also works), <, <=, > and >=. Equality is a single =. Text values take single quotes, just as in the first lesson. A number is written bare: pages > 300.

Boundaries: > and >= differ by exactly the edge row

SELECT title FROM books WHERE pages > 255;    -- Dune, Emma, Neuromancer
SELECT title FROM books WHERE pages >= 255;   -- the same three, plus Foundation

Foundation has exactly 255 pages, so only >= keeps it. Translate the English word for word: "more than" and "above" mean >, while "at least" and "or more" mean >=.

Combining conditions

AND keeps a row only when both sides are true. OR keeps it when either side is true.

SELECT title FROM books WHERE genre = 'scifi' AND pages < 300;

Neuromancer
Foundation

Dune is science fiction, but 412 is not below 300, so AND drops it. Hamlet is short but not science fiction, so it is dropped too.

AND binds tighter than OR, the same way * binds tighter than +:

WHERE genre = 'classic' OR genre = 'scifi' AND pages > 400     -- Dune, Emma, Hamlet
WHERE (genre = 'classic' OR genre = 'scifi') AND pages > 400   -- Dune, Emma

The first line reads as classic OR (scifi AND long), so short Hamlet slips in. Put parentheses around any condition that mixes the two.

Shorthand operators

WHERE genre IN ('classic', 'poetry')      -- equal to any value in the list
WHERE pages BETWEEN 255 AND 412           -- 255 <= pages <= 412, both ends included
WHERE title LIKE 'N%'                     -- % matches any run of characters

LIKE also accepts _, which matches exactly one character. In SQLite LIKE ignores case for ASCII letters. = does not: 'Dune' = 'dune' is false.

Missing values

A missing value is NULL. Comparing anything with it, even pages = NULL, gives unknown, not true, so that row is never kept. Write pages IS NULL or pages IS NOT NULL instead. A later lesson covers NULL in full.

Common mistakes

• Testing the wrong column. WHERE title = 'scifi' runs without an error and returns nothing, because no book is titled scifi. The fact you want lives in genre.
• Leaving out the quotes. WHERE genre = scifi stops with no such column: scifi, because a bare word is read as a column name.
• Being one operator off at the edge. > where the sentence said "at least" loses the row that sits exactly on the boundary. The output is one line short, and nothing warns you.
• Checking only half of the sentence. If the question has two requirements and your WHERE tests one, every row that meets only that one gets through.

Your exercise

The cities table is already in the editor. You write the one SELECT that keeps only the rows the question describes. Before you type, underline each requirement in the sentence and pick its operator. The row most often lost is the city that sits exactly on the population boundary.