Filter rows with WHERE

Rows that match

Most tables have many rows, and you often want only some of them. The WHERE clause keeps the rows for which a condition is true. It goes after FROM.

SELECT title, year FROM books WHERE year > 2018;

Comparing values

Operator	Meaning
=	is equal to
<>	is not equal to
< and >	less than, greater than
<= and >=	less than or equal to, greater than or equal to

SQL uses a single = to compare. Text goes in single quotation marks, such as 'Mystery'. Numbers do not need quotation marks.

More than one condition

Join conditions with AND and OR.

• AND: both conditions must be true.
• OR: at least one condition must be true.
• NOT: turns a condition around.

SELECT title FROM books WHERE genre = 'Mystery' AND year >= 2020;

When you mix AND and OR, use parentheses to show which part goes first.

Patterns

LIKE compares text to a pattern. In a pattern, % stands for any number of characters, and _ stands for exactly one character.

SELECT title FROM books WHERE title LIKE '%Map%';

Two other useful forms: year BETWEEN 2015 AND 2018, and genre IN ('Fantasy', 'Mystery').

SQL keywords such as SELECT and WHERE are not case-sensitive. Programmers often write them in capital letters, so they are easy to see.

Your turn

Write one query for each task, one after the other. Each query ends with a semicolon. Result 1 is checked by task 1, result 2 by task 2, and so on.