AND – Both Conditions Must Be True
AND returns a row only when all conditions it connects evaluate to TRUE. You can chain multiple ANDs in a single WHERE clause.
-- Both conditions must be true
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 80000;
-- Three conditions chained
SELECT * FROM products
WHERE category = 'Electronics'
AND price < 500
AND is_active = 1;
-- AND with date filtering
SELECT * FROM orders
WHERE status = 'shipped'
AND created_at >= '2026-01-01'
AND created_at < '2027-01-01';OR – At Least One Condition Must Be True
OR returns a row when at least one of the connected conditions is TRUE.
-- Either condition is sufficient
SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing';
-- OR with different columns
SELECT * FROM products
WHERE price < 10 OR stock = 0;
-- Combining multiple OR conditions
-- (but see the IN operator for a cleaner version of this)
SELECT * FROM users
WHERE country = 'US' OR country = 'CA' OR country = 'GB';NOT – Negate a Condition
NOT inverts the boolean result of a condition. It is commonly used with IN, LIKE, BETWEEN, and EXISTS.
-- NOT with a comparison
SELECT * FROM employees WHERE NOT department = 'HR';
-- Equivalent: WHERE department <> 'HR'
-- NOT with IN
SELECT * FROM products WHERE NOT category IN ('Clothing', 'Shoes');
-- Equivalent: WHERE category NOT IN ('Clothing', 'Shoes')
-- NOT with LIKE
SELECT * FROM users WHERE NOT email LIKE '%@test.com';
-- Equivalent: WHERE email NOT LIKE '%@test.com'
-- NOT with IS NULL
SELECT * FROM employees WHERE NOT manager_id IS NULL;
-- Equivalent: WHERE manager_id IS NOT NULLOperator Precedence – The Classic Bug
SQL evaluates logical operators in this order: NOT first, then AND, then OR. This is the same as most programming languages. The bug happens when developers forget that AND binds tighter than OR.
A very common mistake is writing a query that looks like it should filter one group OR another group, but the AND binds first and gives unexpected results.
-- INTENT: find Sales OR HR employees who earn over 50,000
-- BUG: AND binds first, so this is actually:
-- dept='Sales' OR (dept='HR' AND salary > 50000)
-- This returns ALL Sales employees regardless of salary!
SELECT * FROM employees
WHERE dept = 'Sales' OR dept = 'HR' AND salary > 50000;
-- HOW THE DATABASE READS IT (adding implicit parentheses):
SELECT * FROM employees
WHERE dept = 'Sales' OR (dept = 'HR' AND salary > 50000);
-- CORRECT: parenthesise the OR conditions explicitly
SELECT * FROM employees
WHERE (dept = 'Sales' OR dept = 'HR') AND salary > 50000;
-- Now: only employees in Sales OR HR who also earn over 50,000Even when you know the precedence rules, explicit parentheses make your intent clear to future readers (including yourself). Never rely on precedence when you have a mix of AND and OR in the same WHERE clause — always parenthesise the OR groups.
Truth Table – Including NULL (Three-Valued Logic)
SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison with NULL yields UNKNOWN. The logical operators handle UNKNOWN in sometimes counterintuitive ways.
| A | B | A AND B | A OR B |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE | TRUE |
| TRUE | UNKNOWN | UNKNOWN | TRUE |
| FALSE | TRUE | FALSE | TRUE |
| FALSE | FALSE | FALSE | FALSE |
| FALSE | UNKNOWN | FALSE | UNKNOWN |
| UNKNOWN | TRUE | UNKNOWN | TRUE |
| UNKNOWN | FALSE | FALSE | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
| A | NOT A |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| UNKNOWN | UNKNOWN |
-- NULL propagation example
-- Suppose bonus IS NULL for some employees:
SELECT * FROM employees WHERE salary > 50000 AND bonus > 1000;
-- Rows where bonus IS NULL: (TRUE AND UNKNOWN) = UNKNOWN → filtered out
-- To include employees with no bonus:
SELECT * FROM employees
WHERE salary > 50000 AND (bonus > 1000 OR bonus IS NULL);Summary
📋 Summary
- AND: both conditions must be TRUE for the row to be returned.
- OR: at least one condition must be TRUE.
- NOT: inverts the result. UNKNOWN stays UNKNOWN after NOT.
- Precedence: NOT > AND > OR. AND binds tighter than OR — this is the source of common bugs.
- Always parenthesise OR conditions when mixing AND and OR:
(A OR B) AND C. - SQL uses three-valued logic. Any comparison with NULL returns UNKNOWN, which is filtered out by WHERE just like FALSE.
FAQ
No — and this is a common source of confusion. In a WHERE clause, NOT column IS NULL and column IS NOT NULL are equivalent and both correctly filter out rows where the column is NULL. However, column NOT NULL is not valid SQL syntax in a WHERE clause — NOT NULL without IS is only used as a column constraint in CREATE TABLE. Always use IS NOT NULL in WHERE clauses.
Yes — in general, AND conditions are more selective and easier for the query optimizer to use indexes on. With OR, the optimizer must consider multiple code paths, which can sometimes result in a full table scan instead of an index seek — especially if the OR conditions involve different columns. Modern query planners (MySQL, PostgreSQL) handle many OR cases well, but complex ORs on non-indexed columns can be slower. When an OR combines checks on the same column (col = 'A' OR col = 'B'), consider using IN ('A', 'B') which often optimizes better.
Yes — logical operators work in any SQL clause that accepts a boolean expression: WHERE, HAVING, JOIN ... ON, CASE WHEN, CHECK constraints, and filtered indexes. The same precedence rules (NOT > AND > OR) and three-valued logic apply everywhere. For example: HAVING COUNT(*) > 5 AND AVG(salary) > 60000 — both conditions must be satisfied by the group.
This follows from the semantics of three-valued logic. With OR, if either side is TRUE, the result is TRUE regardless of the other side (even if it is UNKNOWN) — because true OR anything-at-all is always true. But FALSE OR UNKNOWN is UNKNOWN — we know the first is false, but we don't know if the second is true or false (it's unknown), so the result is unknown. Similarly, with AND: FALSE AND UNKNOWN is FALSE (because false AND anything is always false), but TRUE AND UNKNOWN is UNKNOWN.