Learn/SQL/2. Filtering with WHERE
Lesson 2 of 8
Sign in

SQL, lesson 2

Filtering with WHERE

A table can hold thousands of rows, and you rarely want all of them. WHERE adds a condition. SQLite checks the condition for each row and keeps only the rows where it is true.

WHERE comes after FROM. The columns you test do not have to be the columns you print.

Comparisons

OperatorMeaning
=equal (SQLite also accepts ==)
<> or !=not equal

2 exercises to try what you just read.

Exercise 1

Cheap snacks

Press Ctrl Enter or Check to test your code.

Exercise 2

Newsletter list

Press Ctrl Enter or Check to test your code.

<, <=, >, >=less / greater than

Text comparisons with = are exact and case-sensitive: 'Tea' = 'tea' is false. Put text in single quotes, numbers without quotes.

Combining conditions

Join conditions with AND (both must hold) and OR (at least one must hold). Flip one with NOT. AND binds more tightly than OR, so use parentheses whenever you mix them; it saves you from a bug and tells the reader what you meant.

Shortcuts for common tests

  • x BETWEEN 10 AND 20 means x >= 10 AND x <= 20. Both ends are included.
  • x IN ('red', 'blue') is true when x matches any value in the list.
  • x LIKE 'Ch%' matches text patterns: % stands for any run of characters (even none), and _ stands for exactly one character. In SQLite, LIKE ignores the case of English letters.

Missing values: NULL

NULL means "no value here". It is not zero and not empty text, and it is not equal to anything, not even another NULL. So email = NULL is never true. Use IS NULL and IS NOT NULL instead:

This is one of the most common mistakes in SQL, so it is worth remembering early.

Further reading

  • SQLite: SELECT and the WHERE clause
  • SQLite: expressions, LIKE, BETWEEN, IN and IS

The table snacks has the columns name, category and price:

Print the name and price of every snack in the category sweet that costs less than 2, in the order they are stored. Expected output:

The table members has the columns name, email and city. Some members never gave an email address, so their email is NULL:

Print the name and email of members who have an email address and live in Leeds or York, in the order they are stored. Expected output:

SELECT name, price FROM snacks WHERE price < 2;
SELECT name FROM snacks
WHERE category = 'fruit' AND (price < 1 OR stock > 50);
SELECT name FROM snacks WHERE name LIKE '%choc%';    -- contains "choc", any case
SELECT name FROM snacks WHERE category IN ('nuts', 'fruit');
SELECT name FROM customers WHERE email IS NULL;
name            | category | price
Apple           | fruit    | 0.5
Chocolate bar   | sweet    | 1.5
Trail mix       | nuts     | 3.0
Banana          | fruit    | 0.4
Gummy bears     | sweet    | 2.2
Salted almonds  | nuts     | 2.8
Toffee          | sweet    | 0.9
Chocolate bar|1.5
Toffee|0.9
name    | email              | city
Ava     | ava@example.com    | Leeds
Ben     | NULL               | York
Chloe   | chloe@example.com  | Bath
Dev     | dev@example.com    | York
Elena   | NULL               | Leeds
Finn    | finn@example.com   | Hull
Ava|ava@example.com
Dev|dev@example.com