SQL Execution Order Unlocks All Clauses

Execution Order Powers Clause Behavior

SQL queries execute in this fixed sequence: 1. FROM & JOIN (views/CTEs expanded), 2. ON, 3. OUTER JOIN, 4. WHERE (indexes speed filtering), 5. GROUP BY, 6. Aggregates, 7. HAVING, 8. SELECT (aliases created), 9. DISTINCT, 10. ORDER BY (uses aliases/indexes), 11. LIMIT/OFFSET. Query planner checks indexes first.

You write SELECT → FROM → JOIN → ON → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT, but execution flips it. This resolves three pitfalls:

1. No SELECT aliases in WHERE: Aliases form at step 8, post-WHERE. Fix: repeat expression in WHERE or use CTE.

   -- Fails
   SELECT salary * 12 AS annual_salary FROM employees WHERE annual_salary > 50000;
   

2. WHERE vs HAVING: WHERE filters rows pre-grouping (step 4); HAVING filters groups post-aggregation (step 7). Aggregates like COUNT(*) unavailable in WHERE.

   -- Fails: COUNT in WHERE
   SELECT department, COUNT(*) FROM employees WHERE COUNT(*) > 5 GROUP BY department;
   -- Works: COUNT in HAVING
   SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;
   

3. ORDER BY uses aliases: Runs after SELECT (step 10).

   SELECT salary * 12 AS annual_salary FROM employees ORDER BY annual_salary DESC;  -- Works
   

Indexes optimize WHERE (step 4) and ORDER BY (step 10), skipping full scans/sorts. Use EXPLAIN to verify: EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';.

Joins: Match Rows Precisely by Type

INNER JOIN returns matches only (employees INNER JOIN departments ON employees.department_id = departments.id—excludes orphans).

LEFT JOIN keeps all left rows, NULLs right mismatches (LEFT JOIN—all employees, NULL departments if unmatched).

RIGHT JOIN keeps all right rows (rare; rewrite as LEFT by swapping tables).

FULL OUTER JOIN keeps all rows from both, NULLs mismatches.

CROSS JOIN creates Cartesian product (every combo: 5 employees × 3 departments = 15 rows; avoid on large tables).

SELF JOIN links table to itself (employees e JOIN employees m ON e.manager_id = m.id—employee-manager hierarchy).

ANTI JOIN (no keyword): Use NOT EXISTS for left rows without right matches (handles NULLs); avoid NOT IN if subquery has NULLs (returns zero rows).

-- Safe ANTI
SELECT * FROM employees e WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.id);

ON filters during join (steps 1-3); WHERE filters post-join (step 4). For LEFT JOIN, WHERE on right columns turns it into INNER by dropping NULLs.

Objects, Patterns, and Functions Organize Logic

Objects (DDL-managed, persistent): Tables store data; views/CTEs expand in FROM (step 1); materialized views store results; indexes (pre-execution planner); sequences auto-increment IDs; schemas namespace; procedures/UDFs/triggers automate logic; constraints enforce rules (PRIMARY/FOREIGN KEY, UNIQUE, NOT NULL, CHECK).

Patterns (run full execution order, feed FROM):

• Subquery: Inline (WHERE salary > (SELECT AVG(salary) FROM employees)).
• CTE: Named, reusable (WITH dept_avg AS (SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department) SELECT * FROM dept_avg WHERE avg_sal > 70000—prefer over subqueries for readability).
• Recursive CTE: Hierarchies (WITH RECURSIVE org_tree AS (SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN org_tree o ON e.manager_id = o.id)—anchor starts, recursive expands).
• Derived table: Inline FROM subquery (less readable than CTE).

Functions (execute post-WHERE/GROUP BY/HAVING, pre-ORDER BY/LIMIT):

• Aggregates collapse groups (SUM/COUNT/AVG/MIN/MAX; need GROUP BY; no WHERE).
• Window functions keep rows (ROW_NUMBER()/RANK()/DENSE_RANK()/LAG()/LEAD() OVER (PARTITION BY dept ORDER BY salary DESC)—PARTITION groups without collapse).
• Scalar: Row-level (UPPER(), ROUND(), COALESCE(), CAST()).
• Table-valued: Return tables (FROM clause).

Operators and Pitfalls for Robust Queries

CASE: If-then-else (CASE WHEN salary > 100000 THEN 'Senior' END—use in SELECT/aggregates/ORDER BY; e.g., COUNT(CASE WHEN salary > 100000 THEN 1 END) pivots counts).

Filtering: =/<>/>/AND/OR/NOT/IN (shorthand ORs)/EXISTS (subquery rows?)/BETWEEN (inclusive range)/LIKE '%pat%' (% any chars, _ one char; ILIKE case-insensitive)/IS NULL (dedicated; = NULL fails)/NOT IN (NULLs break it).

Statements: DML (SELECT/INSERT/UPDATE/DELETE), DDL (CREATE/ALTER/DROP), DCL (GRANT/REVOKE), TCL (COMMIT/ROLLBACK).

Pitfalls:

• DISTINCT modifies SELECT (post-step 8).
• Views expand in FROM; indexes plan pre-execution.
• CTEs > subqueries for reuse/readability.
• Aggregates collapse (use HAVING); windows preserve (OVER()).
• String concat: || (standard), + (SQL Server), CONCAT() (universal).