SQL Essentials
From SELECT to window functions: the 10 concepts that let you query, join, aggregate, and optimize any relational database confidently.
What is SQL?
A declarative language for querying relational databases. You describe WHAT you want; the engine figures out HOW to get it.
SELECT basics — filter, sort, limit
The bread and butter: SELECT columns FROM table WHERE conditions ORDER BY sort LIMIT n. Learn this well; everything else builds on it.
Joins — INNER, LEFT, RIGHT, FULL
Combine rows across tables using keys. Pick the right join type or you'll silently lose or duplicate data.
GROUP BY & aggregates
Roll up rows into buckets and summarize: COUNT, SUM, AVG, MIN, MAX. The foundation of every report.
Subqueries & CTEs
Nest one query inside another, or pull it out as a named CTE (WITH clause) for readability. The tool that turns 200-line queries into 20-line ones.
Indexes & query performance
An index is a secondary data structure that turns O(N) table scans into O(log N) lookups. Know when to add one — and when NOT to.
Transactions & isolation levels
ACID, what it guarantees, and how isolation levels (Read Committed, Repeatable Read, Serializable) trade consistency for concurrency.
Window functions — ranking, running totals, LAG/LEAD
Aggregate without collapsing rows. Ranks, running totals, period-over-period comparisons — the superpower of modern SQL.
NULL handling — three-valued logic and gotchas
NULL means 'unknown' in SQL — not 'empty' and not 'zero'. Three-valued logic (TRUE, FALSE, UNKNOWN) quietly breaks naive queries.
Query optimization — anti-patterns to avoid
The top 10 mistakes that turn fast queries slow: SELECT *, function on indexed column, N+1, implicit casts, deep pagination.