A WHERE condition is a test that SQL applies to one row at a time. Only rows where the test is true are kept.
This lesson belongs to SQL reasoning in a fictional database. If SELECT and WHERE are brand new, start with writing simple SQL queries.
How do you predict the surviving rows?
Write a column beside the table and put a tick or a cross for each row. Then list the rows with a tick. Never decide from the first row only.
Here is an original school canteen table called Menu.
| ItemID | ItemName | Price | Category | Stock |
|---|---|---|---|---|
| 1 | Nasi lemak | 3.50 | Rice | 12 |
| 2 | Mee goreng | 4.00 | Noodle | 0 |
| 3 | Teh tarik | 2.00 | Drink | 30 |
| 4 | Roti canai | 1.50 | Bread | 25 |
| 5 | Kuih lapis | 1.00 | Snack | NULL |
| 6 | Milo ais | 2.50 | Drink | 18 |
Worked example: two conditions joined by AND
Take WHERE Price <= 2.50 AND Category = ‘Drink’. Test both parts on every row.
| Item | Price <= 2.50 | Category = ‘Drink’ | Kept? |
|---|---|---|---|
| Nasi lemak | no | no | no |
| Mee goreng | no | no | no |
| Teh tarik | yes | yes | yes |
| Roti canai | yes | no | no |
| Kuih lapis | yes | no | no |
| Milo ais | yes | yes | yes |
Only rows with two yes answers survive, so the result is Teh tarik and Milo ais: 2 rows.
The mistake: reading AND and OR from left to right
Consider WHERE Category = ‘Drink’ OR Category = ‘Bread’ AND Price < 2.
A student reads it as (Drink OR Bread) AND Price < 2. That gives only Roti canai, because Teh tarik costs 2.00 and Milo ais costs 2.50. The answer is 1 row.
SQL works out AND first, so the condition means Drink OR (Bread AND Price < 2). Teh tarik and Milo ais are kept because they are drinks. Roti canai is kept because it is bread under RM2. The correct answer is 3 rows.
The fix is to add brackets yourself, so the meaning is on the page and not hidden in a rule you must remember.
What happens to a NULL value?
Take WHERE Stock > 10. Nasi lemak (12), Teh tarik (30), Roti canai (25) and Milo ais (18) are kept. Mee goreng (0) fails.
Kuih lapis has NULL stock. A comparison with a missing value is neither true nor false, so the row is left out. The result has 4 rows, and the stock of Kuih lapis is not treated as zero.
To find items with no stock figure on purpose, write WHERE Stock IS NULL. That returns Kuih lapis only.
A quick boundary check
BETWEEN includes both ends. Price BETWEEN 1.50 AND 3.50 keeps Roti canai at 1.50 and Nasi lemak at 3.50. Test the boundary rows first, because they are where predictions go wrong.
Check yourself
Using the Menu table, how many rows does this return, and which ones?
SELECT ItemName FROM Menu WHERE Price BETWEEN 1.50 AND 3.50 AND Stock < 20
Answer
Price test: Nasi lemak (3.50), Teh tarik (2.00), Roti canai (1.50) and Milo ais (2.50) pass. Mee goreng (4.00) and Kuih lapis (1.00) fail.
Stock test on those four: Nasi lemak (12) passes, Teh tarik (30) fails, Roti canai (25) fails, Milo ais (18) passes.
The result is 2 rows: Nasi lemak and Milo ais.
What to study next
Filtering rows is the first step before grouping. Continue with distinguishing row filtering from group filtering, then test yourself on the practice set.
To check your own tables, try the SQL reasoning sandbox. If you want a teacher to go through your predictions, see online one-to-one Computer Science tuition.