SQL AND, OR, and NOT

The AND, OR, and NOT operators are the building blocks of every non-trivial SQL WHERE clause. They let you combine two or more conditions, require any one of several conditions to be true, or reverse a condition entirely. Almost every real-world query — “active customers in the UK”, “orders placed this year but not refunded” — depends on chaining these logical operators correctly. Getting their precedence and behavior with NULL wrong is one of the most common sources of silently incorrect query results, so this lesson covers not just the syntax but exactly how the database engine evaluates these expressions.

Overview: How AND, OR, and NOT Work

SQL’s WHERE clause filters rows by evaluating a boolean expression once per row. AND, OR, and NOT are logical operators that combine simpler comparisons (like salary > 90000 or country = 'UK') into a single boolean expression:

• AND — the combined expression is true only if every condition joined by AND is true. If any one is false, the row is excluded.
• OR — the combined expression is true if at least one condition joined by OR is true. Only if every condition is false is the row excluded.
• NOT — a unary operator that reverses the truth value of the condition that follows it: NOT TRUE becomes FALSE, and NOT FALSE becomes TRUE.

Internally, the query engine evaluates the WHERE expression row by row (logically, right after the row has been assembled from FROM/JOIN), reducing it down to TRUE, FALSE, or — critically — NULL (unknown). Only rows where the final result is TRUE are kept; both FALSE and NULL rows are dropped. That third possible outcome, NULL, is what causes most of the surprises with NOT, and we’ll dig into it below.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2;

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2;

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Operator	Meaning	Row kept when…
AND	Logical conjunction	ALL joined conditions are true
OR	Logical disjunction	AT LEAST ONE joined condition is true
NOT	Logical negation	The following condition evaluates to false

You can chain any number of conditions with AND and OR, and you can freely mix them — but as soon as you mix AND and OR in the same clause, parentheses become essential (see Under the Hood below).

Examples

1. Using AND

Sample employees table: 6 rows across Engineering, Sales, and Marketing.

SELECT name, department, salary
FROM employees
WHERE department = 'Engineering' AND salary > 90000;

Result:

name	department	salary
Alice	Engineering	95000
Eve	Engineering	102000

Both conditions must hold for a row to appear. Carol is also in Engineering but earns 88000, which fails the salary > 90000 test, so she is excluded even though her department matches.

2. Using OR

SELECT name, department
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

Result:

name	department
Bob	Sales
Dave	Marketing
Frank	Sales

Only one of the two conditions needs to be true per row, so anyone in either department is returned.

3. Using NOT

SELECT name, department, salary
FROM employees
WHERE NOT (salary > 80000);

Result:

name	department	salary
Bob	Sales	62000
Dave	Marketing	54000
Frank	Sales	71000

NOT (salary > 80000) keeps every row where the inner condition is false — here, everyone earning 80000 or less. This is logically equivalent to salary <= 80000, and in practice <= or <> is often clearer than wrapping a comparison in NOT, but NOT becomes essential once conditions get more complex (e.g. negating an entire multi-part expression, or negating IN, LIKE, or EXISTS).

4. Combining AND and OR with parentheses

SELECT name, department, salary, hire_date
FROM employees
WHERE department = 'Engineering'
  AND (salary > 90000 OR hire_date < '2020-01-01');

Result:

name	department	salary	hire_date
Alice	Engineering	95000	2021-03-15
Eve	Engineering	102000	2018-05-30

This finds Engineering staff who are either high earners or long-tenured (hired before 2020). Carol is Engineering but neither earns over 90000 nor was hired before 2020, so she’s filtered out. The parentheses around the OR are what make this “Engineering AND (high salary OR long tenure)” instead of something else entirely — see the precedence trap in Common Mistakes.

How It Works Step by Step (Query Processing Order)

Logically, a query executes in this order, and AND/OR/NOT only come into play in the WHERE (and HAVING) step:

1. FROM — identify the source table(s) and perform any joins, producing a working set of rows.
2. WHERE — evaluate the boolean expression (built from AND, OR, NOT, and comparisons) against each row; keep only rows that evaluate to TRUE.
3. GROUP BY — bucket the remaining rows, if grouping is used.
4. HAVING — filter groups, which can also use AND/OR/NOT against aggregated values.
5. SELECT — compute the output columns for the surviving rows.
6. ORDER BY / LIMIT — sort and cap the final result.

Within a single WHERE expression, the query planner (or, for a simple table scan, the row-by-row evaluator) works inside-out: it first resolves the innermost comparisons (salary > 90000, hire_date < '2020-01-01') to TRUE/FALSE/NULL, then applies NOT to any negated sub-expression, then combines with AND, and finally with OR — unless parentheses override that order. If an indexed column is involved, the optimizer may use the index to satisfy an AND-ed equality condition first (narrowing the row set cheaply) before evaluating the remaining, more expensive conditions, but the logical result is identical regardless of which physical order the engine chooses.

Under the Hood: Three-Valued Logic and NULL

SQL doesn’t use ordinary two-valued (true/false) logic — it uses three-valued logic, because any comparison involving NULL produces NULL (unknown), not TRUE or FALSE. A row is only returned when the final expression evaluates to TRUE; both FALSE and NULL are treated as “don’t return this row.”

A	B	A AND B	A OR B	NOT A
TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	NULL	NULL	TRUE	FALSE
FALSE	FALSE	FALSE	FALSE	TRUE
FALSE	NULL	FALSE	NULL	TRUE
NULL	NULL	NULL	NULL	NULL

Notice the asymmetry: FALSE AND NULL is FALSE (one false condition is enough to kill an AND), but TRUE OR NULL is TRUE (one true condition is enough to satisfy an OR). This matters most when NOT meets a NULL: NOT NULL is NULL, not TRUE. That single fact is responsible for the classic NOT IN pitfall covered next.

On operator precedence: when NOT, AND, and OR appear together without parentheses, SQL evaluates NOT first, then AND, then OR — the same left-to-right, tightest-binds-first rule as most programming languages. This means a OR b AND c is parsed as a OR (b AND c), not (a OR b) AND c. Relying on this default order is a frequent source of bugs (see below) — always use explicit parentheses when mixing AND and OR so the query reads unambiguously and does what you intend.

Common Mistakes

Mistake 1: Forgetting to repeat the column name with OR

-- Wrong: intends "Engineering or Sales"
SELECT name, department
FROM employees
WHERE department = 'Engineering' OR 'Sales';

This runs without error, which makes it dangerous. 'Sales' on its own isn’t a comparison — it’s just a string literal, and in a boolean context a non-numeric string is treated as falsy (0). So the clause silently collapses to just department = 'Engineering', and every Sales row is missing from the result with no warning. Always write out the full comparison on both sides of OR:

-- Correct
SELECT name, department
FROM employees
WHERE department = 'Engineering' OR department = 'Sales';

Mistake 2: Mixing AND and OR without parentheses

-- Wrong: intends "(Sales or Marketing) employees earning over 65000"
SELECT name, department, salary
FROM employees
WHERE department = 'Sales' OR department = 'Marketing' AND salary > 65000;

Because AND binds tighter than OR, this is actually evaluated as department = 'Sales' OR (department = 'Marketing' AND salary > 65000). Every Sales row is returned regardless of salary — including Bob, who earns only 62000 and should have been filtered out. Fix it by grouping the department check explicitly:

-- Correct
SELECT name, department, salary
FROM employees
WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 65000;

Mistake 3: NOT IN with a NULL in the list

-- Wrong: returns zero rows, even though Dave (Marketing) looks like it should match
SELECT name, department
FROM employees
WHERE department NOT IN (SELECT department FROM managed_departments);

If the subquery’s result set contains even one NULL (for example, a manager row with no assigned department), NOT IN silently returns an empty result set for every single row of the outer query — not an error, just nothing. This happens because x NOT IN (a, b, NULL) expands to NOT (x = a OR x = b OR x = NULL), and x = NULL always evaluates to NULL, which poisons the whole expression to NULL for every row that doesn’t already match a or b. Always exclude NULLs from the subquery, or use NOT EXISTS instead, which doesn’t have this trap:

-- Correct
SELECT name, department
FROM employees
WHERE department NOT IN (
  SELECT department FROM managed_departments WHERE department IS NOT NULL
);

Best Practices

• Use parentheses any time AND and OR appear in the same WHERE clause, even when you’re confident about precedence — it removes all ambiguity for the next reader (including future you).
• Prefer <=, <>, or != over wrapping a simple comparison in NOT (...) when a direct operator exists; reserve NOT for negating compound expressions, IN, LIKE, EXISTS, or BETWEEN.
• Never use NOT IN against a subquery or column that might contain NULL — filter the NULLs out first or switch to NOT EXISTS.
• Repeat the full comparison on each side of OR (col = 'x' OR col = 'y') rather than assuming a bare value will be understood as a comparison.
• For long lists of OR-ed equality checks on the same column, use IN (...) instead — it’s shorter, clearer, and the optimizer handles it just as well.
• When a query returns unexpectedly few or zero rows, suspect NULL handling in AND/OR/NOT logic before assuming the data itself is missing.

Practice Exercises

• Exercise 1: Using the employees table from this lesson, write a query that returns employees who are in Engineering and earn more than 85000.
• Exercise 2: Write a query that returns employees who are in Sales or were hired before 2020-01-01, making sure your parentheses (if any) reflect exactly what you intend.
• Exercise 3: Write a query using NOT that returns every employee who is not in Marketing. Then rewrite it using <> instead of NOT and confirm both return the same rows.

Summary

• AND requires every joined condition to be true; OR requires at least one to be true; NOT reverses a condition’s truth value.
• Without parentheses, NOT is evaluated first, then AND, then OR — mixing AND and OR without parentheses is a common source of subtly wrong results.
• SQL uses three-valued logic: any expression touching NULL can evaluate to NULL (unknown), and NULL rows are excluded from WHERE results just like FALSE rows.
• NOT IN against a list containing NULL silently returns zero rows — prefer NOT EXISTS or filter out NULLs explicitly.
• Always write full comparisons on both sides of OR, and use parentheses generously to make intent unambiguous.