Databases & SQL · Class 12 · Querying with SELECT

MySQLSELECT⏱️ 8 min read

AND, OR & NOT

One condition answers “who is in grade 12?”. Real questions are fussier: in grade 12 and from Gangtok, or scoring below 70 or above 90. Two conditions joined by one word, and the word changes everything.

1AND — both must be true

MySQL command line client

mysql> select id, name, grade, city from students where grade = 12 and city = 'Gangtok';

+------+--------+-------+---------+

+------+--------+-------+---------+

| 1187 | Anjali | 12 | Gangtok |

| 2035 | Ravi | 12 | Gangtok |

+------+--------+-------+---------+

2 rows in set (0.00 sec)

Four students are in grade 12 and two live in Gangtok. Only the students who satisfy both come back — so and always gives you the same number of rows or fewer than either condition alone. Each extra and narrows the result.

2OR — either one will do

MySQL command line client

mysql> select id, name, percent from students where percent < 70 or percent > 90;

+------+--------+---------+

| id | name | percent |

+------+--------+---------+

| 1099 | Veena | 90.5 |

| 1187 | Anjali | 91 |

| 1250 | Sohan | 66 |

| 1402 | Lhamu | 95.2 |

| 3033 | Tom | 64.25 |

| 3612 | Nima | 59.4 |

+------+--------+---------+

6 rows in set (0.00 sec)

The very low scorers and the very high ones, in one resultset. A row qualifies if either side is true, so or always gives the same number of rows or more than either condition alone. Each extra or widens the result.

Which word, and which way it moves

and narrows — every extra condition is another hurdle. or widens — every extra condition is another way in. If a query returns too many rows, you probably wanted and; too few, and you probably wanted or.

3Say the column name every time

In English you say “percent below 70 or above 90”. In SQL each side of the or must be a complete condition:

correct

where percent < 70 or percent > 90

wrong

where percent < 70 or > 90

The second is a syntax error. > 90 on its own does not say what is greater than 90.

4NOT — reverse a condition

not goes in front of a condition and flips it:

MySQL command line client

mysql> select id, name, city from students where not city = 'Gangtok';

+------+--------+----------+

| id | name | city |

+------+--------+----------+

| 1099 | Veena | Singtam |

| 1402 | Lhamu | Namchi |

| 2044 | Karma | Singtam |

| 2199 | Diana | Kolkata |

| 2500 | Bhim | Singtam |

| 3033 | Tom | Kolkata |

| 3120 | Pema | Namchi |

| 3301 | Farhan | Siliguri |

+------+--------+----------+

8 rows in set (0.00 sec)

For a simple comparison this is the same as using <>, and MySQL agrees — the query below returns the identical eight rows:

MySQL command line client

mysql> select id, name, city from students where city <> 'Gangtok';

+------+--------+----------+

| id | name | city |

+------+--------+----------+

| 1099 | Veena | Singtam |

| 1402 | Lhamu | Namchi |

| 2044 | Karma | Singtam |

| 2199 | Diana | Kolkata |

| 2500 | Bhim | Singtam |

| 3033 | Tom | Kolkata |

| 3120 | Pema | Namchi |

| 3301 | Farhan | Siliguri |

+------+--------+----------+

8 rows in set (0.00 sec)

not earns its keep in front of the operators that have no symbol to negate — not between, not in and not like, all coming up in the next lessons.

5The trap: NOT does not rescue NULLs

Count those last two resultsets. Eight rows — but the table has twelve students and only two live in Gangtok. Twelve minus two is ten, not eight. Where did the other two go?

Sohan and Nima have NULL in city. Asking is your city Gangtok? about an unknown city gives NULL, and not NULL is still NULL — never true. So they fail both the condition and its opposite.

A condition and its negation do not add up to the whole table

This is the most surprising consequence of NULL in the whole chapter. With missing values in the column, where city = 'Gangtok' (2 rows) and where city <> 'Gangtok' (8 rows) leave 2 rows in neither. To include them you must say so explicitly:
where city <> 'Gangtok' or city is null

6When you mix AND with OR

and is evaluated before or, exactly as × is evaluated before + in arithmetic. So this:

where grade = 12 or grade = 11 and city = 'Singtam'

means grade 12, or (grade 11 from Singtam) — which is almost certainly not what was intended. Brackets make it say what you mean:

where (grade = 12 or grade = 11) and city = 'Singtam'

Just use brackets

Nobody loses marks for brackets that were not strictly needed, and plenty of people lose them for leaving brackets out. Whenever and and or appear in the same where, bracket the part you want done first.

The three logical operators

Operator	Row is kept when	Effect on the row count
and	Both conditions are true	Same or fewer — it narrows
or	At least one is true	Same or more — it widens
not	The condition is false	Reverses — but NULL rows stay out

Quick Check

Which returns students in grade 12 who are also from Singtam?

Quick Check

Why does where city <> 'Gangtok' return 8 rows when 12 students exist and only 2 are from Gangtok?

Quick Check

What does where a = 1 or a = 2 and b = 3 mean?

← Previous

🎯 The WHERE Clause

BETWEEN & IN 📐