Real questions rarely fit in one condition — hours, department, city and pay band all have opinions. AND, OR and NOT are the connectors that combine predicates into one filter, and they obey two rules that are easy to forget under deadline: AND binds tighter than OR, and a missing value stays hidden whatever you connect it with.
How does AND narrow a result down?
AND keeps a row only when every condition is true — each extra clause can only cost you rows. Café staff earning the band's floor pay comes to one person, because the roster's second barista misses the salary bar:
SELECT name, role, salary FROM staff WHERE department = 'Cafe' AND salary >= 30000;
name role salary
----------- --------- ------
Aisha Zahra Cafe Lead 32000
Mentally, AND is intersection. When a report returns nothing where you expected several rows, the usual cause is two clauses that never co-occur in the data — the intersection is simply empty. Re-run the borderline clauses individually before blaming the engine.
How does OR widen a result out?
OR keeps a row when any condition is true, so each added clause can only gain rows. Buying café together — the two departments invited to a supplier tasting, say — spans the roster:
SELECT name, department, city FROM staff WHERE department = 'Cafe' OR department = 'Buying';
name department city
----------- ---------- ----------
Priya Raman Buying London
Dana Kowalski Buying Manchester
Aisha Zahra Cafe Leicester
Joe Naylor Cafe Manchester
OR is union. Two practical notes: a row matches just once regardless of how many conditions it satisfies, and long OR ladders over the same column are a signal to switch spellings — lesson 7 shows IN doing the same job in one word. On this roster the café team is only two people, which makes widths and narrowings visible at a glance; on a real table, do not estimate them — count them, because row counts are the cheapest form of self-review available to anyone writing filters.
Which connector wins when AND and OR appear without parentheses?
Here is the rule with teeth: AND evaluates before OR, so every shared query needs parentheses. Leave the mixture undressed and the engine binds the London condition to Buying alone, so the result quietly becomes cafes anywhere, or buying staff in London — a union nobody asked for:
SELECT name, department, city, salary FROM staff
WHERE department = 'Cafe' OR department = 'Buying' AND city = 'London';
name department city salary
----------- ---------- ---------- ------
Priya Raman Buying London 61000
Aisha Zahra Cafe Leicester 32000
Joe Naylor Cafe Manchester 25500
Mathematically the engine executed Cafe OR (Buying AND city = 'London') — so café staff in other cities smuggled themselves into the result, which is exactly why well-reasoned reports keep failing review. Add the parentheses you meant and the query tightens to its honest reading: cafés or buying, London only:
SELECT name, department, city, salary FROM staff
WHERE (department = 'Cafe' OR department = 'Buying') AND city = 'London';
name department city salary
----------- ---------- ------ ------
Priya Raman Buying London 61000
My standing convention: in any query where AND and OR both appear, parentheses go in even where the precedence happens to be harmless. Future maintainers cannot re-derive intent from operator precedence they never memorised, and unlike some syntax, extra parentheses cost nothing at execution time.
What does NOT do — and what does it refuse to do with NULL?
NOT inverts a result: true becomes false and false becomes true. There is a third value, though, and it is the reason the roster's London filter misses the seasonal employee in both directions. Ask for everyone whose city is not London:
SELECT name, city FROM staff WHERE NOT city = 'London';
name city
------------- ----------
Dana Kowalski Manchester
Aisha Zahra Leicester
Tom Whitfield Leeds
Joe Naylor Manchester
Four rows returned, and Ruth — whose city is recorded as NULL — is absent again. In SQL's three-valued logic a comparison against NULL is unknown, and the filter can only pass rows whose condition is definitively true; unknown is not true, so the row disappears whether or not NOT is in play. Her city neither is nor isn't London, and the database refuses to pretend it knows. Lesson 8 shows the IS operators that bring unknowns back when you want them.
Condensed, the three-valued rule reads like this:
| Predicate value | Passes WHERE? | After NOT |
|---|---|---|
| true | yes | fails |
| false | no | passes |
| unknown (NULL involved) | no, always | still no |
Boolean shortcuts from other languages map onto SQL regularly; none survive contact with the unknown middle. Hang the table somewhere visible until the middle row feels automatic — it is the single most common source of stray missing rows in otherwise reviewed SQL.
Quick reference
- AND intersects — every clause must hold; each additional condition shrinks the result.
- OR unions — any clause suffices, and matching twice still yields one row.
- AND outranks OR; dress joint queries in parentheses even when optional.
- NOT flips true and false but leaves unknown unknown — NULLs stay excluded from not-filters too.
Drill: find the Manchester staff who are not in the cafe team (expect several), then everyone outside Sales whose salary is below 50000 — write it two ways, once with NOT and once without, and check both return the identical row set. Finally, predict the row count for department = 'Cafe' OR city = 'London' AND salary < 30000 before you run it; then justify whatever you get in one sentence.