WHERE: telling true rows from false ones

Last lesson you filtered rows with WHERE. Now we look properly at what a condition can say, because filtering is where most wrong answers are born. The canteen keeps a second table, orders, with one row per order: what was bought, for how much, and whether it is paid. One row has paid = NULL: the canteen does not know yet.

orders
+----+--------+-------+----------+
| id | item   | price | paid     |
+----+--------+-------+----------+
| 1  | soup   | 2.50  | yes      |
| 2  | salad  | 2.80  | no       |
| 3  | juice  | 1.90  | yes      |
| 4  | juice  | 1.90  | NULL     |
| 5  | soup   | 2.50  | yes      |
+----+--------+-------+----------+The orders table: one row per order, and one unknown value in paid.

A comparison like price < 2.00 or paid = 'yes' does not describe the table — it is a question you ask each row separately. Every row answers yes, no, or I cannot say. WHERE keeps exactly the yes rows. That is why the same query can return a different number of rows tomorrow: the question is stable, the data is not.

Read both queries over the orders table and predict the rows before looking at the answer. AND narrows: one condition is about payment, the other about price, and both must hold. OR widens: a row survives if at least one side is true, so price alone saves the cheap rows.

WHERE paid = 'yes' AND price < 2.00
rows kept: juice (1.90, yes)

WHERE paid = 'yes' OR price < 2.00
rows kept: soup, juice, juice, soupTwo conditions over the same table: rows kept by AND, then by OR.

AND binds tighter than OR, exactly like multiplication before addition in arithmetic. If you mean something else, put parentheses around it. WHERE a = 1 OR b = 2 AND c = 3 means a = 1 OR (b = 2 AND c = 3), and people get this wrong constantly — the parentheses cost nothing.

Equality demands the whole value to match. LIKE matches a pattern instead: % stands for any text of any length, including nothing. So dish LIKE 'veggie%' catches everything that starts with veggie, and '%soup%' catches everything with soup anywhere inside.

menu
+----+---------------+
| id | dish          |
+----+---------------+
| 1  | veggie soup   |
| 2  | chicken soup  |
| 3  | veggie salad  |
+----+---------------+

WHERE dish LIKE 'veggie%'    matches: veggie soup, veggie salad
WHERE dish LIKE '%soup%'     matches: veggie soup, chicken soupThe menu table and two LIKE patterns over the dish column.

Now the strangest rule in SQL. The row with paid = NULL disappears from every comparison query. Not from paid = 'no' and not from paid = 'yes' — from both. To understand why, look at what NULL actually means.

WHERE paid = 'yes'
rows kept: soup (1), juice (3), soup (5)
row 4: paid is NULL, dropped

NULL = NULL  is  not true, it is unknownWhy paid = 'yes' drops the NULL row, and why NULL = NULL is not true.

NULL is not a value, it is the absence of one: the canteen does not know yet whether that order was paid. Asking is an unknown thing equal to yes is like asking is my tomorrow headache equal to yes — the honest answer is not no, it is unknown. SQL calls that unknown, and WHERE keeps only true, so the row is dropped.