AND, OR, NOT

A WHERE clause can test more than one thing at once. Join conditions with:

• AND — both must be true
• OR — at least one must be true
• NOT — flips a condition

SELECT name FROM student WHERE form = '11A' AND score > 88;

This returns 11A students who also scored above 88.

When you mix AND and OR, use brackets to make the meaning clear, just like in maths:

SELECT name FROM student
WHERE form = '11B' AND (score < 70 OR score > 90);

AND is checked before OR, so without the brackets SQL would read this as (form = '11B' AND score < 70) OR score > 90, and Sara (95, form 11A) would get in too. The brackets make SQL check the score test as one unit.

Question. Show the name of each student who is in 11B, or in 11A, and who scored more than 90.

Without brackets AND goes first, so this means "in 11B, or (in 11A and over 90)":

SELECT name FROM student
WHERE form = '11B' OR form = '11A' AND score > 90;

name
Jamal
Sara
Tom

Jamal (72) and Tom (64) are wrong answers: they are in 11B but did not score over 90.

With brackets the form test is one unit, and the score test applies to everyone:

SELECT name FROM student
WHERE (form = '11B' OR form = '11A') AND score > 90;

name
Sara

• Repeat the column in each test. WHERE form = '11A' OR '11B' does not mean "11A or 11B". Write form = '11A' OR form = '11B'.
• Mixing AND and OR without brackets. AND is checked first, which is often not what you meant. Add brackets.
• NOT goes in front of the test it reverses: WHERE NOT form = '11A'.