Combining Conditions — AND, OR, NOT

One condition is rarely enough. AND, OR and NOT combine them.

AND — every condition must hold

SQL
SELECT first_name, salary, dept_id
FROM employees
WHERE dept_id = 1 AND salary > 80000
ORDER BY salary DESC;

Three rows: engineers earning over 80,000. Each extra AND can only ever reduce the result.

OR — any condition may hold

SQL
SELECT first_name, dept_id
FROM employees
WHERE dept_id = 2 OR dept_id = 3
ORDER BY dept_id, first_name;

Each extra OR can only ever increase the result. (This particular query has a tidier form, IN, which is the next lesson.)

NOT — invert a condition

SQL
SELECT name, category, unit_price
FROM products
WHERE NOT category = 'Hardware'
ORDER BY unit_price;

NOT category = 'Hardware' and category <> 'Hardware' mean the same thing. NOT earns its place in front of IN, LIKE, BETWEEN and EXISTS, where there is no single negated operator to reach for.

Precedence: AND binds tighter than OR

This is the rule that silently changes answers. AND is evaluated before OR, exactly as × is evaluated before +.

SQL
SELECT first_name, dept_id, salary
FROM employees
WHERE dept_id = 1 OR dept_id = 2 AND salary > 90000
ORDER BY dept_id, salary DESC;

Read what the database read: dept_id = 1 OR (dept_id = 2 AND salary > 90000). So it returns everyone in department 1 regardless of salary, plus only the high earners in department 2. Almost certainly not what a person writing that line intended.

The intended query, with brackets:

SQL
SELECT first_name, dept_id, salary
FROM employees
WHERE (dept_id = 1 OR dept_id = 2) AND salary > 90000
ORDER BY salary DESC;

A different, smaller answer. Both queries are valid, neither errors, and the only thing separating a correct report from a wrong one is a pair of brackets.

The rule to work by: whenever AND and OR appear in the same WHERE, bracket it. Even when the default precedence happens to be what you want, the brackets tell the next reader you meant it.

The full precedence order

From tightest to loosest:

comparison operators (=, <, >, LIKE, IN, BETWEEN)
NOT
AND
OR

Negating a compound condition

NOT (A AND B) is NOT A OR NOT B — the AND becomes an OR. This is De Morgan's law, and getting it backwards is a classic bug:

SQL
SELECT first_name, dept_id, salary
FROM employees
WHERE NOT (dept_id = 1 AND salary > 80000)
ORDER BY emp_id;

Read it as "not (an engineer earning over 80k)" — which includes low-paid engineers and everyone outside engineering. Note who is still missing: the employee with a NULL salary, and the one with a NULL department. NOT of unknown is still unknown, so negation does not rescue NULLs — it never does.

Common mistakes

Mixing AND and OR without brackets — the most expensive typo in this course.
Assuming NOT includes NULLs — it does not.
WHERE dept_id = 1 OR 2 — 2 on its own is not a condition. Some databases reject it; MySQL treats it as truthy and returns every row.

Interview question

What does WHERE a = 1 OR a = 2 AND b > 10 return?

Everything with a = 1, plus rows where a = 2 and b > 10. AND binds tighter. The follow-up — "how would you write what the author probably meant?" — is the actual test.

Check yourself

Bracket WHERE x = 1 OR x = 2 AND y = 3 to make the OR apply first.
Rewrite NOT (a > 5 OR b < 2) without the outer NOT.
Why does adding an AND never grow a result set?