SQL Semantics & Query Architecture
Mastering the declarative paradigm, the logical order of execution, set theory, and the danger of NULL.
The Declarative Paradigm
Modern programming languages (Python, Java, Go) are imperative. You write explicit, step-by-step instructions on how to achieve a result (e.g., “initialize an array, loop over these objects, filter by this property, and return the new array”).
SQL is declarative. You describe exactly what data you want, and you leave the how entirely up to the database’s Query Optimizer. The Optimizer translates your SQL string into a physical execution plan, deciding which indexes to use, how much memory to allocate, and in what order to join the tables.
Writing highly performant SQL requires understanding how the database interprets your declarations.
The Logical Order of Execution
The most common source of SQL bugs—such as referencing an alias that “doesn’t exist” or incorrectly filtering aggregated data—stems from a misunderstanding of how SQL is parsed.
A query is written starting with SELECT, but the database engine processes the clauses in a completely different order:
Because SELECT (Step 5) happens after WHERE (Step 2) and GROUP BY (Step 3), you cannot use an alias defined in the SELECT clause inside your WHERE or GROUP BY clauses.
Furthermore, this order dictates the difference between WHERE and HAVING:
WHEREfilters individual rows before they are grouped.HAVINGfilters aggregated data after the grouping has occurred.
Set Theory: JOINs as Venn Diagrams
Relational databases combine data across tables using set theory. A JOIN is not a loop; it is a mathematical intersection of data sets based on a predicate (the ON clause).
INNER JOIN: Returns only the overlapping subset. If a User has no Orders, the User is entirely dropped from the result set.LEFT JOIN: Returns the entire left set. If a User has no Orders, the User is returned, and all Order columns for that row are populated withNULL.FULL OUTER JOIN: Returns everything from both sets, injectingNULLwherever a relationship does not exist.
JOIN operations are incredibly expensive because they often require moving massive amounts of data into memory. When designing analytical systems, engineers often intentionally denormalize data to avoid runtime JOINs, trading storage space for read performance.The Semantic Trap of NULL
In SQL, NULL does not mean “zero,” “empty string,” or “false.” NULL strictly means “Unknown.”
Because SQL operates on Three-Valued Logic (True, False, Unknown), comparing anything to NULL yields NULL.
1 = 1yieldsTRUE.1 = 0yieldsFALSE.1 = NULLyieldsNULL.NULL = NULLyieldsNULL. (Is an unknown value equal to another unknown value? We don’t know).
The Missing Data Bug: Imagine a query to find all users not in the marketing department:
SELECT * FROM users WHERE department_id != 5;
If a user was just hired and their department_id is currently NULL, this query will drop them. The database evaluates NULL != 5 as NULL (Unknown). Since NULL is not TRUE, the row is filtered out.
You must explicitly handle unknowns using IS NULL or COALESCE():
SELECT * FROM users WHERE department_id != 5 OR department_id IS NULL;
Test Your Understanding
Q:An engineer writes the following query to find the total revenue per user, but only for users who have spent more than $500: `SELECT user_id, SUM(amount) as total_spent FROM orders WHERE total_spent > 500 GROUP BY user_id;` The query crashes with an error. Why? Reveal ▾
WHERE clause (Step 2) before it evaluates the SELECT clause (Step 5). Therefore, the alias total_spent does not exist yet.
Additionally, you cannot use WHERE to filter aggregated data (SUM(amount)). The correct query must use HAVING:
SELECT user_id, SUM(amount) as total_spent FROM orders GROUP BY user_id HAVING SUM(amount) > 500;