SQL WHERE clause explained with examples

Filtering rows with WHERE

SELECT chooses columns. WHERE chooses rows. It goes after FROM and holds a condition that each row either passes or fails:

SELECT name, score FROM students WHERE score >= 80;

The database checks every row: is this score at least 80? Yes, it goes in the result. No, it is skipped. The table itself does not change.

The comparison operators:

= equal (just one = in SQL, not two)
<> or != not equal
< less than, > greater than
<= at most, >= at least

Numbers are written as they are. Text needs single quotes:

SELECT name FROM students WHERE club = 'Chess';

Without the quotes, SQL thinks Chess is the name of a column, can't find it, and complains. Double quotes are a trap here too: in SQL they are meant for names of columns and tables, not for text, so stick to single quotes for values.

Text comparisons with = are exact. In SQLite, 'chess' and 'Chess' are different values, and so are 'Chess' and 'Chess ' with a sneaky space on the end. If a query you are sure is right returns nothing, check the spelling and capitals of the value first.

You can still pick whatever columns you like. The WHERE column does not have to be one of the columns you return:

SELECT name FROM students WHERE year = 10;

That gives you a list of names, filtered by a column you never show. The order is always SELECT ... FROM ... WHERE ..., and swapping them around is a syntax error.

Tables in this lesson

students 9 rows

id	name	year	club	score
1	Aisha	10	Chess	91
2	Ben	11	Drama	64
3	Chloe	10	Robotics	78
4	Dev	12	Chess	85
5	Ella	11	Robotics	99
6	Finn	10	Drama	52
7	Grace	12	Esports	73
8	Hugo	11	Esports	88
9	Ivy	12	Robotics	60

Show the SQL that built them

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, year INTEGER, club TEXT, score INTEGER);
INSERT INTO students VALUES
 (1, 'Aisha', 10, 'Chess', 91),
 (2, 'Ben', 11, 'Drama', 64),
 (3, 'Chloe', 10, 'Robotics', 78),
 (4, 'Dev', 12, 'Chess', 85),
 (5, 'Ella', 11, 'Robotics', 99),
 (6, 'Finn', 10, 'Drama', 52),
 (7, 'Grace', 12, 'Esports', 73),
 (8, 'Hugo', 11, 'Esports', 88),
 (9, 'Ivy', 12, 'Robotics', 60);

Try it yourself

Edit it. Break it. Run it again.

-- Only students with a score of 80 or more
SELECT name, club, score
FROM students
WHERE score >= 80;

Your turn

Type it yourself. That is the whole trick.

1Robot people

Return the name and year of every student whose club is Robotics.

SELECT name, year
FROM students;

2Everyone but year 12

Return the name and year of every student who is not in year 12.

SELECT name, year
FROM students
WHERE year = 12;