SQL Query Explainer

SQL executes clauses in this logical order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. This is different from the order you write them. Understanding execution order explains why you can't use SELECT aliases in WHERE (WHERE runs before SELECT), but you can use them in ORDER BY (which runs after SELECT).

WHERE filters individual rows BEFORE aggregation — it cannot use aggregate functions. HAVING filters groups AFTER aggregation — it can reference aggregate functions like SUM(), COUNT(). Rule: if the condition uses an aggregate function, it must be in HAVING. If not, prefer WHERE (better performance, runs earlier).

INNER JOIN returns only rows where the ON condition matches in BOTH tables. LEFT JOIN returns all rows from the left table plus matching rows from the right (NULLs for no match). RIGHT JOIN is the mirror of LEFT JOIN. FULL OUTER JOIN returns all rows from both tables, with NULLs where no match exists. CROSS JOIN produces every combination of rows (Cartesian product).

A CTE (Common Table Expression) creates a named temporary result set within a query. Use CTEs to: (1) Break complex queries into readable steps. (2) Reference the same subquery multiple times without duplication. (3) Write recursive queries (e.g., tree traversal). CTEs in PostgreSQL 12+ are inlined by default (treated as subqueries), which allows the optimizer to push WHERE filters through them.

Key optimizations: (1) Add indexes on columns used in WHERE, JOIN, and ORDER BY. (2) Avoid functions on indexed columns in WHERE (prevents index use). (3) Use EXPLAIN ANALYZE to see the query plan. (4) Replace SELECT * with specific columns. (5) Add covering indexes for frequently queried column sets. (6) Avoid N+1 queries — use JOINs or batch loading instead. (7) For large tables, partition by date or other high-cardinality columns.

UNION combines results from two SELECT statements and removes duplicates (expensive — requires sorting/hashing). UNION ALL combines results without removing duplicates (faster — no deduplication). Use UNION ALL unless you specifically need to remove duplicates. Both require the same number of columns with compatible types in each SELECT.