SQL fundamentals

How SQL joins two tables

A join stitches rows from two tables together on a matching column. The two you must know cold are INNER and LEFT.

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;   -- only customers who have orders

An INNER JOIN returns a row only where the ON condition matches on both sides. Swap in LEFT JOIN and every customer stays in the result, even those with no orders; the columns coming from orders are filled with NULL for them. RIGHT JOIN is the mirror image (keep every right-side row), and FULL JOIN keeps unmatched rows from both sides. In practice you will write INNER and LEFT ninety percent of the time.

One thing that surprises beginners: a customer with three orders produces three rows, not one. A join multiplies rows, it does not merge them.

Grouping rows and the WHERE vs HAVING split

GROUP BY collapses rows that share a value into one row per group, so you can count or sum each group.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'shipped'      -- filters individual rows first
GROUP BY customer_id
HAVING COUNT(*) > 5;          -- filters the groups after counting

WHERE runs before grouping and filters raw rows. HAVING runs after grouping and filters on the aggregate. You cannot put COUNT(*) > 5 in WHERE, because the count does not exist yet when WHERE runs. That ordering is the single most common junior mistake, so keep it straight: row filter goes in WHERE, group filter goes in HAVING.

Why NULL is not zero and not empty

NULL means "unknown", and it does not compare like a normal value.

wrong "WHERE bonus = NULL finds the rows with no bonus."

It finds nothing. Any comparison against NULL, even NULL = NULL, produces neither true nor false; the row is not returned. Use IS NULL and IS NOT NULL instead. The same idea shows up in counting: COUNT(*) counts every row, while COUNT(bonus) counts only rows where bonus is not NULL. SUM and AVG also skip NULLs, so an average is the total over the count of non-NULL values, never over the row count. Reach for COALESCE(bonus, 0) when you want a NULL to read as zero.

The LEFT JOIN that quietly becomes an INNER JOIN

You write a LEFT JOIN to keep every customer, then filter the joined table in WHERE, and the order-less customers disappear:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';   -- drops every customer with no order

The join does keep the unmatched customers, with NULL in o.status. Then WHERE evaluates NULL = 'shipped', which is UNKNOWN, and WHERE keeps only rows that are TRUE. The NULL rows fall out and your LEFT JOIN now behaves exactly like an INNER JOIN. When the predicate is about the optional side, it belongs in the ON clause, not WHERE:

LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'shipped'

The exception is WHERE o.id IS NULL, which is the standard way to find left rows with no match (an anti-join).

Subqueries, CTEs, and the NOT IN trap

A CTE (WITH name AS (...)) is a named subquery that sits above the main query. It reads top to bottom, can be referenced more than once, and can recurse. A plain subquery nested inside WHERE or FROM does the same job with worse readability when it repeats. Prefer a CTE when the logic has a name worth giving.

The trap that bites here is NOT IN over a subquery that can produce NULL:

wrong "WHERE id NOT IN (SELECT manager_id FROM staff) lists everyone who isn't a manager."

If any manager_id is NULL, the whole result is empty. NOT IN (1, NULL) expands to id <> 1 AND id <> NULL, and id <> NULL is UNKNOWN, so no row is ever TRUE. Use NOT EXISTS, which tests row by row and is immune to the NULL, or filter the NULLs out of the subquery.

UNION vs UNION ALL, and your first window function

UNION stacks two result sets and removes duplicates, which forces a sort. UNION ALL keeps every row and skips that work, so use it whenever you know the inputs are disjoint. Both require the two SELECTs to have the same column count and compatible types.

A window function computes across a set of rows without collapsing them, unlike GROUP BY:

SELECT name, dept,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept
FROM employees;

Every row survives and gains a per-department rank. That row-keeping behavior is what makes windows the tool for running totals, top-N-per-group, and period-over-period math.

The logical order a SELECT actually runs in

Text order lies. A query is written SELECT ... FROM ... WHERE, but it is processed FROM and JOIN, then WHERE, then GROUP BY, then aggregates, then HAVING, then window functions, then SELECT, then DISTINCT, then UNION/INTERSECT/EXCEPT, then ORDER BY, then LIMIT. Almost every "why can't I" question falls out of that list.

WHERE cannot reference a SELECT alias, because projection happens four steps later; the column does not exist yet. Window functions are illegal in WHERE, GROUP BY, and HAVING for the same reason, and legal only in SELECT and ORDER BY, the two phases that run after them. To filter on a ROW_NUMBER(), wrap it in a subquery or CTE and filter the outer query. DISTINCT runs after windows, so it cannot dedupe rows before a row number is assigned. ORDER BY, running last, can use SELECT aliases that WHERE could not.

Window frames and the default that surprises people

A window function has three parts: the function, PARTITION BY, and ORDER BY. Add an ORDER BY and you also get a frame, and the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, not ROWS.

wrong "A SUM(...) OVER (ORDER BY day) gives a per-row running total."

With RANGE, all rows that tie on the ORDER BY key share one frame end, so tied rows get the same cumulative value and the total jumps by the whole group at each distinct key. When you want a true row-by-row running total over duplicate keys, say ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly. The ranking family splits on ties too: ROW_NUMBER assigns distinct integers arbitrarily, RANK leaves gaps after a tie (1, 1, 3), DENSE_RANK does not (1, 1, 2).

Three-valued logic, all the way down

Every predicate returns TRUE, FALSE, or UNKNOWN, and different clauses treat UNKNOWN differently. WHERE, JOIN ON, and HAVING admit only TRUE rows. A CHECK constraint does the opposite: it rejects a row only when the predicate is FALSE, so UNKNOWN passes the constraint. A row that violates a CHECK on paper can slip in on a NULL.

Then the exception nobody expects: NULL = NULL is UNKNOWN, yet GROUP BY, DISTINCT, and UNION treat two NULLs as the same value and fold them into one group. The set operators use "not distinct" semantics, which contradicts equality on purpose so that grouping stays useful. Interviewers grade this topic on whether you volunteer the UNKNOWN branch before being asked, and whether you reach for filtered aggregates (COUNT(*) FILTER (WHERE ...) or SUM(CASE WHEN ... THEN 1 END)) instead of firing a separate query per condition.