In a shopping app, you tick the Electronics filter and set a maximum price of $1,000. Out of thousands of items, only a few are still shown. The other items are not gone from the shop: they just do not meet your conditions.
In SQL, a filter like the one in the shopping app is written with WHERE, followed by a condition. The database checks every row one by one. A row that meets the condition passes, and only the rows that pass go into the query's result. The table itself does not change: only the result is filtered.
WHERE and the comparison operators
The products table that the exercise uses:
| id | name | price | category |
|---|---|---|---|
| 1 | Laptop | 1200 | electronics |
| 2 | Mouse | 15 | electronics |
| 3 | Notebook | 4 | stationery |
| 4 | Pen | 1 | stationery |
| 5 | Monitor | 350 | electronics |
WHERE goes after FROM. The query below reads: fetch the name and price columns from the products table, where price is less than 1000.
SELECT name, price
FROM products
WHERE price < 1000;
The result is 4 rows: Mouse, Notebook, Pen, Monitor. Laptop does not pass, because its price is 1200.
The < sign is a comparison operator: a sign that compares two values. This is the list:
| Operator | Means | Example |
|---|---|---|
= |
equal to | category = 'electronics' |
!= or <> |
not equal to | category != 'electronics' |
> / < |
greater / less than | price > 100 |
>= / <= |
greater or equal / less or equal | price <= 4 |
- Text that you compare goes inside single quotes:
'electronics'. - Numbers are written without quotes and without thousands separators:
1000, not1,000. - In SQLite,
=compares text exactly, including upper and lower case. - To find an empty cell (NULL), use
IS NULL, not= NULL. For example, if Mouse has an empty category,WHERE category IS NULLfinds Mouse, andWHERE category IS NOT NULLfinds the rows whose category is filled in.
AND, OR and NOT
You can join two conditions with AND (both must be true) or OR (one true condition is enough). Before you look at the table below, guess: of the 5 products in the products table, how many pass when the condition is WHERE category = 'electronics' OR price < 1000?
SQLite checks every condition on every row, and writes true as 1 and false as 0. The AND and OR columns are the result of joining both conditions:
| name | category = 'electronics' |
price < 1000 |
AND | OR |
|---|---|---|---|---|
| Laptop | 1 | 0 | 0 | 1 |
| Mouse | 1 | 1 | 1 | 1 |
| Notebook | 0 | 1 | 0 | 1 |
| Pen | 0 | 1 | 0 | 1 |
| Monitor | 1 | 1 | 1 | 1 |
With AND, only 2 rows pass:
SELECT name, price
FROM products
WHERE category = 'electronics' AND price < 1000;
| name | price |
|---|---|
| Mouse | 15 |
| Monitor | 350 |
With OR, all five rows pass, because every product meets at least one condition. Laptop passes because of its category, Notebook and Pen because of their price.
NOT reverses the result of a condition (true becomes false, false becomes true). WHERE NOT category = 'electronics' gives Notebook and Pen, the same as category != 'electronics'.
LIKE, IN and BETWEEN
LIKE compares text with a pattern. In a pattern, % stands for any characters, any number of them, even zero. _ stands for exactly one character:
SELECT name FROM products WHERE name LIKE 'M%'; -- Mouse, Monitor
SELECT name FROM products WHERE name LIKE '%o%'; -- Laptop, Mouse, Notebook, Monitor
'M%' means text that starts with M. '%o%' means text with an o anywhere in it. In SQLite and MySQL, LIKE treats upper and lower case as the same for A to Z. In PostgreSQL, LIKE treats them as different, and the version that treats them as the same is called ILIKE.
IN means the value equals one of the items in the list inside the parentheses:
SELECT name FROM products WHERE name IN ('Mouse', 'Pen', 'Fan'); -- Mouse, Pen
That query is the same as name = 'Mouse' OR name = 'Pen' OR name = 'Fan', only shorter. There is no Fan in the table, so the result is only Mouse and Pen.
BETWEEN means the value is inside a range, both ends included (here 4 and 350). In this query, AND is part of BETWEEN, not a join of two conditions:
SELECT name FROM products WHERE price BETWEEN 4 AND 350; -- Mouse, Notebook, Monitor
⚠️ Why is the result an error, empty, or too big?
- Text without quotes.
WHERE category = electronicsis answered withno such column: electronics. Without quotes, SQLite readselectronicsas a column name. - Thousands separators.
price < 1,000is answered withnear ",": syntax error. Write1000. - Upper and lower case.
category = 'Electronics'gives 0 rows in SQLite (and PostgreSQL), because the stored text iselectronics, in lower case. MySQL with its default settings treats upper and lower case as the same, so in MySQL the result is 3 rows. Write the text exactly as it is stored. = NULL.WHERE category = NULLgives 0 rows with no error, even when some categories are empty. NULL means the value is unknown, so no comparison with NULL is ever true. That is also whycategory != 'electronics'skips rows whose category is NULL. WriteIS NULL.- AND and OR without parentheses. AND is calculated before OR, like multiplication before addition. So
category = 'electronics' AND price < 1000 OR category = 'stationery'gives 4 rows: Mouse and Monitor from the AND part, plus Notebook and Pen from the OR part. Use parentheses to make your meaning clear.
TL;DR: WHERE condition goes after FROM, and only the rows that meet the condition go into the result. A condition uses a comparison operator (a sign that compares two values): =, != or <> (not equal to), >, <, >=, <=. Text goes inside single quotes, numbers have no quotes and no thousands separators. Two conditions are joined with AND (both must be true) or OR (one true condition is enough), and NOT reverses the result of a condition. AND is calculated before OR. LIKE compares text with a pattern: % stands for any characters, any number of them, _ for exactly one character. IN (...) is true when the column's value equals one of the items in the list. BETWEEN a AND b is true for values from a to b, with a and b included.
⚡ Filter rows with WHERE
Write:
SELECT name, price
FROM products
WHERE price < 1000;
Meaning: WHERE is followed by a condition, here price < 1000 (the price is less than 1000). A row that meets the condition passes, and only the rows that pass go into the result. Of the 5 products in the products table, 4 pass: Mouse, Notebook, Pen, Monitor. Laptop does not pass, because its price is 1200. Numbers are written without thousands separators, text inside single quotes.
⚡ AND or OR
Write:
SELECT name, price
FROM products
WHERE category = 'electronics' AND price < 1000;
Guess first: Laptop, Mouse and Monitor are in the electronics category, Notebook and Pen are not. If AND is replaced with OR, how many of the 5 products pass?
All five. Every product meets at least one condition: Laptop because of its category, Notebook and Pen because of their price.
Meaning: AND joins two conditions, and both must be true. With AND, only Mouse and Monitor pass. With OR, one true condition is enough.
⚡ Patterns, lists, ranges
Write:
SELECT name FROM products WHERE name LIKE 'M%';
Meaning: LIKE compares text with a pattern. In a pattern, % stands for any characters, any number of them. So 'M%' means text that starts with M: Mouse and Monitor. IN (...) is true when the column's value equals one of the items in the list inside the parentheses. BETWEEN a AND b is true for values from a to b, with a and b included. The AND inside BETWEEN does not join two conditions.