AND, OR, NOT and Operator Precedence

Concept

Logical operators join conditions into one expression. There are three, and they have a strict precedence order that does not match how English sentences are read.

Syntax

WHERE condition1 AND condition2
WHERE condition1 OR  condition2
WHERE NOT condition
WHERE (condition1 OR condition2) AND condition3

Example

-- What people usually mean:
--   employees in department 10 or 20, earning over 80000
SELECT first_name, dept_id, salary
FROM   employees
WHERE  (dept_id = 10 OR dept_id = 20)
  AND  salary > 80000;

-- What they often write, which means something else entirely
SELECT first_name, dept_id, salary
FROM   employees
WHERE  dept_id = 10 OR dept_id = 20 AND salary > 80000;

Explanation

Because AND binds tighter than OR, the second query is read as dept_id = 10 OR (dept_id = 20 AND salary > 80000). Every employee in department 10 is returned regardless of salary. The query does not error - it quietly answers a different question, which is far worse.

NOT and NULL

-- These two are NOT equivalent when status can be NULL
SELECT * FROM employees WHERE NOT (status = 'active');
SELECT * FROM employees WHERE status <> 'active' OR status IS NULL;

If status is NULL, status = 'active' is UNKNOWN, and NOT UNKNOWN is still UNKNOWN - so the row is filtered out. NOT cannot rescue a NULL. Only IS NULL can.

Short circuit is not guaranteed

-- Do not rely on left to right evaluation for safety
SELECT * FROM orders
WHERE  total <> 0 AND (1000 / total) > 5;   -- may still divide by zero

Unlike most programming languages, SQL does not promise to evaluate AND operands in order. The optimiser may reorder conditions freely. Use CASE or NULLIF when an operation must be guarded.

Important rules

• Precedence is NOT, then AND, then OR. Parentheses override it.
• NOT UNKNOWN is UNKNOWN, so NOT never converts a NULL row into a match.
• OR across different columns often prevents efficient index use; the engine may switch to a full scan or an index merge.
• WHERE a = 1 OR a = 2 OR a = 3 is exactly WHERE a IN (1, 2, 3), and the latter is easier to read.

Common mistakes

• Mixing AND and OR without parentheses - by far the most common logic bug in SQL.
• Expecting NOT IN to work when the list can contain NULL; it returns no rows at all. See the NULL notes.
• Assuming a guard condition placed first will protect a later division.
• Writing WHERE NOT status = 'active' and losing every NULL row silently.

Best practices

• Parenthesise every mixed AND/OR expression, even when the default precedence is already correct. The next reader should not have to remember the table.
• Put each condition on its own line and align the operators; complex filters become reviewable.
• Replace long OR chains on one column with IN.
• Handle NULL explicitly rather than hoping NOT covers it.

Practice

1. Write a filter for active employees in Engineering or Finance hired after 2020.
2. Explain what WHERE NOT (dept_id = 10 AND salary > 80000) returns for an employee with dept_id = NULL.
3. Rewrite WHERE status = 'pending' OR status = 'shipped' OR status = 'packed' more concisely.