SELECT & Filtering

The execution order SQL actually follows — not the order you write it in

Why

The single most common SQL mistake — using an alias defined in SELECT inside WHERE — comes from not knowing the logical execution order. SQL doesn't run top-to-bottom like Python. WHERE runs before SELECT, which means column aliases from SELECT don't exist yet when WHERE is evaluated. Knowing the order explains every "column does not exist" error.

Intuition

SQL's logical execution order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. You write SELECT first, but it runs near last. This explains why WHERE can't reference SELECT aliases, why HAVING filters after aggregation, and why ORDER BY can use SELECT aliases (it runs after SELECT).

Explanation

SELECT, WHERE, ORDER BY, LIMIT

-- Basic structure SELECT column1, column2, expression AS alias FROM table_name WHERE condition ORDER BY column1 DESC, column2 ASC LIMIT 10; -- Filtering with multiple conditions SELECT name, salary FROM employees WHERE department = 'Engineering' AND salary > 90000 AND hire_date >= '2022-01-01'; -- Pattern matching SELECT * FROM products WHERE name LIKE 'Pro%'; -- starts with 'Pro' SELECT * FROM products WHERE name ILIKE '%pro%'; -- case-insensitive (Postgres) -- IN and BETWEEN (readable shorthands) SELECT * FROM orders WHERE status IN ('pending', 'processing'); SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;

NULL handling

-- NULL is not a value — comparisons with NULL always return NULL (unknown) -- These do NOT work as expected: WHERE manager_id = NULL -- always false WHERE manager_id != NULL -- always false -- Correct NULL checks WHERE manager_id IS NULL WHERE manager_id IS NOT NULL -- COALESCE — first non-NULL value in the list SELECT COALESCE(phone, email, 'no contact') AS contact FROM users; -- NULLIF — return NULL when two expressions are equal (avoids divide-by-zero) SELECT revenue / NULLIF(sessions, 0) AS revenue_per_session FROM stats;

NULL propagates through arithmetic and comparisons — any expression involving NULL produces NULL. COALESCE is the standard way to substitute a default value for NULLs.

CASE expressions

-- CASE is an expression — usable anywhere a value is SELECT name, salary, CASE WHEN salary >= 150000 THEN 'senior' WHEN salary >= 100000 THEN 'mid' ELSE 'junior' END AS band FROM employees; -- Conditional aggregation — a CASE inside an aggregate SELECT department, COUNT(*) AS total, COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count FROM employees GROUP BY department;

Conditional aggregation (COUNT(CASE WHEN ...)) is a key pattern for pivot-style reports — counting or summing only rows that meet a condition, without a subquery.

← Previous Next →