-- Basic comparison operators

SELECT * FROM employees WHERE salary > 80000;

SELECT * FROM employees WHERE department = 'Engineering';

SELECT * FROM employees WHERE hire_date >= '2020-01-01';

SELECT * FROM employees WHERE status != 'terminated';

SELECT * FROM employees WHERE status <> 'terminated'; -- equivalent

-- Combining conditions

SELECT * FROM employees

WHERE department = 'Engineering'

AND salary > 90000

AND status = 'active';

-- OR with explicit parentheses (critical)

SELECT * FROM employees

WHERE (department = 'Sales' OR department = 'Marketing')

AND status = 'active';

-- NULL checks — only correct syntax

SELECT * FROM employees WHERE manager_id IS NULL;

SELECT * FROM employees WHERE manager_id IS NOT NULL;

-- IN replaces OR chains

SELECT * FROM orders

WHERE status IN ('pending', 'processing', 'on_hold');

-- BETWEEN (inclusive on both ends)

SELECT * FROM orders

WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Sargable date range (preferred over BETWEEN for datetime columns)

SELECT * FROM orders

WHERE order_date >= '2024-01-01'

AND order_date < '2025-01-01';

-- EXISTS with correlated subquery

SELECT * FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o

WHERE o.customer_id = c.id

AND o.total > 500

);

WHERE runs in step 3 of the logical processing order — after FROM/JOIN but before GROUP BY, HAVING, SELECT, and ORDER BY

SELECT column aliases are not available in WHERE because WHERE executes before SELECT

NULL uses three-valued logic (TRUE / FALSE / UNKNOWN). Any comparison with NULL yields UNKNOWN, which WHERE treats as FALSE

IS NULL and IS NOT NULL are the only correct operators for testing NULL. = NULL always produces UNKNOWN (zero rows)

AND has higher precedence than OR. Always use explicit parentheses when mixing AND and OR in the same expression