WHERE: The Picky Eater

Filtering rows

WHERE keeps only the rows where a condition is true. Comparison operators: =, <> (not equal, != also works), <, >, <=, >=.

Text values go in single quotes: 'Classic'. Numbers do not.

Example: Cheap pizzas only

SELECT name, price FROM pizzas WHERE price < 12;

Every row is tested against price < 12. Only the true ones survive.

Combining conditions: AND, OR, NOT

AND needs both sides true. OR needs at least one. NOT flips it. Use parentheses when you mix them. AND binds tighter than OR, and that surprises everyone at least once.

Example: Vegetarian AND under 12 dollars

SELECT name, price, is_vegetarian FROM pizzas WHERE is_vegetarian = 1 AND price < 12;

Change AND to OR and see how many more rows sneak in.

IN and BETWEEN: less typing

x IN (a, b, c) is the same as x = a OR x = b OR x = c. x BETWEEN 10 AND 13 is the same as x >= 10 AND x <= 13 (both ends included).

Example: Customers from two cities

SELECT name, city FROM customers WHERE city IN ('Austin', 'Miami');

Cleaner than a chain of ORs, and easy to extend.

LIKE: pattern matching

LIKE matches text patterns. % means "any number of characters", _ means "exactly one character". 'M%' = starts with M. '%Party' = ends with Party. '%ee%' = contains "ee".

Example: Pizzas whose name starts with P

SELECT name FROM pizzas WHERE name LIKE 'P%';

In SQLite LIKE is case-insensitive for ASCII letters, so 'p%' works too.

NULL is not a value

NULL means "unknown / missing". It is not zero and not an empty string. You cannot test it with =; you must use IS NULL or IS NOT NULL. Felix Wagner has no favorite topping yet. Let us go find him.

Example: Who has no favorite topping?

SELECT name FROM customers WHERE favorite_topping IS NULL;

Try WHERE favorite_topping = NULL instead. It returns nothing, because NULL never equals anything, not even NULL.

Practice exercises

1. Wild ones

   Show the name and price of all pizzas in the 'Wild' category.

2. Big spenders

   Show the name and loyalty_points of customers with more than 400 loyalty points.

3. Light and green

   Show the name and calories of pizzas that are vegetarian (is_vegetarian = 1) and have fewer than 900 calories.

4. The sweet spot

   Show the name and price of pizzas priced between 12 and 13.5 (inclusive). Use BETWEEN.

5. Somewhere in the name

   Show the name of every customer whose name contains the letters in anywhere (LIKE is case-insensitive, so Ingrid counts).

6. Undelivered, and not to Chicago

   From orders, show the id, customer_id and quantity of orders that were not delivered (delivered = 0) or have a quantity of 4 or more.

Open this page in a browser to run your SQL and get instant, auto-graded feedback.

Boss battle

Take the WHERE quiz: 6 timed questions.