AND, OR, NOT

Learning Focus

Master AND, OR, and NOT to build complex, precise WHERE conditions in DuckDB.

Logical Operators

Operator	Description
AND	Both conditions must be true
OR	At least one condition must be true
NOT	Inverts the condition

Truth Table

A	B	A AND B	A OR B	NOT A
true	true	true	true	false
true	false	false	true	false
false	false	false	false	true
NULL	true	NULL	true	NULL
NULL	false	false	NULL	NULL

Examples

-- AND: both must be true
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 85000;

-- OR: either must be true
SELECT * FROM employees
WHERE department = 'HR' OR active = false;

-- NOT: invert
SELECT * FROM employees
WHERE NOT department = 'HR';
-- equivalent to: WHERE department != 'HR'

-- Combine all three
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Finance')
  AND salary > 75000
  AND NOT active = false;

Operator Precedence

NOT > AND > OR — use parentheses for clarity:

-- Ambiguous (AND binds before OR)
SELECT * FROM employees
WHERE department = 'HR' OR department = 'Finance' AND salary > 70000;

-- Explicit with parentheses (recommended)
SELECT * FROM employees
WHERE (department = 'HR' OR department = 'Finance') AND salary > 70000;

Common Pitfalls

Pitfall	Consequence	Prevention
Missing parentheses with mixed AND/OR	Wrong rows filtered	Always group OR conditions with ()
NOT IN with NULLs in list	Returns empty result	Use NOT EXISTS or filter NULLs from the list
Redundant conditions	Slower queries	Remove overlapping conditions

Quick Reference

WHERE cond1 AND cond2
WHERE cond1 OR cond2
WHERE NOT cond
WHERE (cond1 OR cond2) AND cond3

What's Next

• Next: IS NULL and COALESCE - Handle missing values in conditions.
• Module Overview - Return to this module index.