SQL
The declarative language of relational databases: SELECT describes the result, not the plan.
Level: beginner
What is it?
SQL lets you say what you want (a relation) rather than how to get it. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and JOIN are the everyday core. The optimizer is allowed to reorder and pick indexes as long as the result matches the spec.
Why does it matter?
It is the lingua franca of data. Application code that fights SQL (row-by-row) loses; code that describes sets wins.
Mental model
A pipeline of relational operators: scan, filter, project, join, aggregate, sort. You write a description; EXPLAIN shows the pipeline they chose.
How it works
- Logical processing order is not the written order: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
- NULL is not a value; it poisons most comparisons. IS NULL exists for a reason.
- Joins are predicates, not arrows in an ORM. Know inner vs left.
- Indexes are not hints you sprinkle after the query is slow — they are data structures the planner can choose.
Code example: Filter, join, aggregate
SELECT u.country, COUNT(*) AS n, SUM(o.amount) AS revenue
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.amount > 0
GROUP BY u.country
HAVING COUNT(*) >= 2
ORDER BY revenue DESC;Real-world usage
- OLTP apps
- Analytics
- Migrations and admin
Common mistakes
- SELECT *
- Joining in the application loop
- WHERE col = NULL
Interview questions
WHERE vs HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation.
What does an inner join drop?
Rows that do not match the join predicate on the other side.