SQL
SQL as the user-facing surface — DDL, DML, SELECT, joins, aggregates, subqueries, CTEs, window functions, EXPLAIN.
SQL is the API of the relational world. The syntax is verbose, the semantics have edge cases (three-valued NULL logic, the GROUP BY contract), and the planner can hide a lot of cost from you — but it's also a language with thirty years of optimiser work behind it. Learn to read EXPLAIN plans and you can outperform almost any application-level alternative.
The topics that come up in interviews most often: joins (especially LEFT JOIN), GROUP BY + HAVING, subqueries vs CTEs vs window functions, and the cost model of indexes. Know these and the rest is dialect detail.
Key concepts
- SELECT is read; INSERT / UPDATE / DELETE / MERGE are writes; DDL is the schema
- NULL is not equal to itself — three-valued logic infects every predicate
- JOIN ON vs WHERE behaves differently for OUTER joins
- GROUP BY restricts SELECT to grouped columns + aggregates
- Window functions don't collapse rows; GROUP BY does
Reference template
// Reading an EXPLAIN plan
1. What's the top operator? (sequential scan, index scan, hash join, merge join?)
2. What's the row estimate? (and is it close to actual?)
3. Where's the time spent? (the slow node is rarely the top one)
4. Are statistics fresh? (ANALYZE recently?)
5. Would an index help? (and which kind — B-tree, hash, GiST, GIN?)Adapt to your problem; the structure is the load-bearing part.
Common pitfalls
- Writing
WHERE col = NULLinstead ofWHERE col IS NULL - Using
LEFT JOINand then aWHEREon the right table's columns — it silently becomes an INNER JOIN - Reaching for a correlated subquery when a JOIN or window function would be faster
- SELECT * in production code — schema changes will eventually break consumers
Related topics
Items (8)
- SQL DDL — CREATE, ALTER, DROP
Defining the schema. Tables, columns, constraints, indexes — and the ALTER traps on a live system.
Building Block Foundational - SQL DML — INSERT, UPDATE, DELETE, MERGE
Mutating data. RETURNING, UPSERT / ON CONFLICT, the things to know about row-locking on writes.
Building Block Foundational - SELECT and the WHERE Clause
The shape of every read. Predicate logic, NULL handling, the difference between AND/OR and short-circuit eval.
Building Block Foundational - JOIN — INNER, LEFT, RIGHT, FULL, CROSS
Connecting tables. When LEFT JOIN is the right tool, what FULL OUTER is for, and how CROSS JOIN should be feared.
Building Block Foundational - Aggregates, GROUP BY, HAVING
COUNT / SUM / AVG / MIN / MAX, the GROUP BY contract, why HAVING is not WHERE.
Building Block Foundational - Subqueries and CTEs
Correlated vs non-correlated subqueries, the readability case for CTEs, recursive CTEs for trees.
Building Block Intermediate - Window Functions
OVER, PARTITION BY, ORDER BY, frame clauses. Running totals, ranks, lag/lead — the part of SQL most engineers underuse.
Building Block Intermediate - Query Execution and EXPLAIN Plans
How the planner reads a query, the cost-based optimizer, EXPLAIN/EXPLAIN ANALYZE, the four operators you'll see most.
Concept Intermediate