Filtering Rows with WHERE

Ask the table for only the rows you actually want

Big Idea: A table can hold thousands of rows, but you rarely want all of them. The WHERE clause is a test you write. SQL checks it against every row and keeps only the rows that pass.

By the end you will be able to:

compare values with =, <>, <, >, <=, >=,
combine tests with AND, OR, NOT and use parentheses to control the order,
match a range with BETWEEN,
match a short list with IN,
match text patterns with LIKE and the % and _ wildcards.

1. What does WHERE do?

Think of a doorkeeper at a party. Every guest walks up, the doorkeeper checks one rule ("are you on the list?"), and only guests who pass are let in. WHERE is that doorkeeper for your rows. You write one rule, SQL checks it against each row, and only the rows that make the rule true come back.

You met SELECT ... FROM ... in the last topic. WHERE is a new line you add after FROM:

SELECT columns
FROM table
WHERE condition;

Read it out loud: "Get these columns from this table, but only the rows where the condition is true."

2. Our example table

Every query below runs against one small students table. Here is how it is built, so you can picture exactly what is inside:

CREATE TABLE students (
  id     INT,
  name   VARCHAR(20),
  age    INT,
  grade  INT,
  city   VARCHAR(20),
  score  INT
);

INSERT INTO students VALUES
  (1, 'Sara', 12, 7, 'London', 85),
  (2, 'Omar', 13, 8, 'Paris',  60),
  (3, 'Mia',  12, 7, 'London', 92),
  (4, 'Leo',  14, 9, 'Berlin', 45),
  (5, 'Nina', 13, 8, 'Paris',  78),
  (6, 'Adam', 12, 7, 'Madrid', 88);

That gives us these six rows:

id	name	age	grade	city	score
1	Sara	12	7	London	85
2	Omar	13	8	Paris	60
3	Mia	12	7	London	92
4	Leo	14	9	Berlin	45
5	Nina	13	8	Paris	78
6	Adam	12	7	Madrid	88

Keep this table in mind. For each query we will ask a question, and you can check the answer against these rows yourself.

Filtering Rows with WHERE