SQL fundamentals

The basics every backend dev should answer cold: joins, normalization, GROUP BY/HAVING, subqueries vs JOINs.

JOINs

The four standard joins, by what rows they include:

-- INNER JOIN — rows that match in both
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- LEFT JOIN — all left rows; right side NULL when no match
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
-- users with zero orders show count = 0 (not omitted)

-- RIGHT JOIN — all right rows; symmetric to LEFT
-- (rare in practice — flip the table order and use LEFT instead)

-- FULL OUTER JOIN — all rows from both sides
SELECT u.name, o.id
FROM users u
FULL JOIN orders o ON o.user_id = u.id;

CROSS JOIN is rarely intentional; usually it’s a missing ON clause turned into a bug.

Self-join

Joining a table to itself — e.g., employee → manager:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Useful for hierarchies (1 level). For arbitrary depth, recursive CTE — see 12_window_functions_cte.md.

Filtering: WHERE vs ON for outer joins

Subtle: a predicate on the outer side moves rows in/out of the result. In WHERE, it filters after the join. In ON, it filters before.

-- LEFT JOIN, predicate in ON: keeps users with no orders, ignores orders < 100
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.total >= 100;

-- LEFT JOIN, predicate in WHERE: drops users with no orders entirely (NULL fails >=)
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.total >= 100;

The second silently turns the LEFT JOIN into an INNER JOIN. Common bug.

GROUP BY and aggregates

SELECT user_id, COUNT(*), SUM(amount)
FROM transactions
GROUP BY user_id;

Every non-aggregated column in SELECT must appear in GROUP BY (Postgres enforces; MySQL historically did not).

HAVING vs WHERE

• WHERE filters rows before aggregation.
• HAVING filters groups after aggregation.

SELECT user_id, COUNT(*) AS n
FROM transactions
WHERE created_at >= '2026-01-01'    -- per-row filter, before grouping
GROUP BY user_id
HAVING COUNT(*) >= 10;               -- group filter, after aggregation

Use WHERE for things you can — it’s faster (less to aggregate). Use HAVING only when the predicate is on an aggregate.

Aggregate functions

COUNT(*), COUNT(col) (skips NULLs), COUNT(DISTINCT col), SUM, AVG, MIN, MAX, string_agg(col, ','), array_agg(col), bool_or, bool_and. PG-specific: percentile_cont, mode.

Subqueries

Three flavors: scalar, IN/EXISTS, correlated.

-- Scalar subquery — returns one value
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS n_orders
FROM users u;

-- IN subquery — list of values
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- EXISTS — boolean, fast (stops at first match)
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id AND total > 1000);

-- Correlated — references outer query
SELECT name FROM users u
WHERE 5 < (SELECT COUNT(*) FROM orders WHERE user_id = u.id);

IN vs EXISTS

For “does X have any Y?” — both work, but:

• EXISTS short-circuits on first match — usually faster.
• IN materializes the subquery first.
• For NULLs, IN is treacherous: WHERE id NOT IN (subquery returning NULLs) is always false. Use NOT EXISTS instead.

-- Bug: if any user_id is NULL, returns no rows at all
SELECT * FROM orders WHERE user_id NOT IN (SELECT id FROM users);

-- Safe
SELECT * FROM orders o WHERE NOT EXISTS (
    SELECT 1 FROM users u WHERE u.id = o.user_id
);

Subquery vs JOIN

For most “filter X by attribute of Y” queries, a JOIN and a subquery produce equivalent plans (the optimizer rewrites between them). Pick whichever is more readable. Use JOIN when you also need columns from the other table; use EXISTS when you only need to check existence.

Normalization

The classical normal forms — most apps target 3NF, sometimes denormalize for read performance.

1NF — atomic values

Every column holds a single value, not a list.

-- Bad
CREATE TABLE users (id INT, tags TEXT);     -- "python,backend,sql" stored as csv

-- Good — separate table
CREATE TABLE user_tags (user_id INT, tag TEXT);

(PG: arrays and JSONB technically break 1NF; use them when querying-as-a-set is rare.)

2NF — no partial dependency on a composite key

If your PK is (order_id, product_id), every non-key column must depend on the whole key, not just part.

-- Violates 2NF: product_name depends on product_id alone
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name TEXT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- Fix: move product_name to products table
CREATE TABLE products (id INT PRIMARY KEY, name TEXT);
CREATE TABLE order_items (order_id INT, product_id INT REFERENCES products, quantity INT, PRIMARY KEY (order_id, product_id));

3NF — no transitive dependency

Non-key columns must depend on the key, the whole key, and nothing but the key.

-- Violates 3NF: zip_city depends on zip_code, which depends on user_id transitively
CREATE TABLE users (id INT PRIMARY KEY, zip_code TEXT, zip_city TEXT);

-- Fix: zip_city moves to a zips table
CREATE TABLE zips (code TEXT PRIMARY KEY, city TEXT);
CREATE TABLE users (id INT PRIMARY KEY, zip_code TEXT REFERENCES zips);

When to denormalize

Aim for 3NF first. Denormalize when:

• Read-heavy and the JOIN cost is measured-not-guessed too high.
• Reporting / analytics tables (star schema, snowflake).
• Caching aggregates (e.g., users.order_count updated by trigger).

Denormalization always trades write complexity for read speed. Have a plan to keep the duplicates in sync (triggers, app-level, or accept eventual divergence and reconcile).

SELECT *… and other anti-patterns

• SELECT * in production code: brittle to schema changes, ships unused columns over the wire, breaks index-only scans. Spell out columns.
• ORDER BY without LIMIT on large tables: full sort. Add an index that matches the sort, or paginate.
• OFFSET for pagination on large tables: scans OFFSET rows just to skip them. Use keyset pagination (WHERE id > last_seen_id ORDER BY id LIMIT N).
• COUNT(*) for “are there any?”: use EXISTS.

Interview angle

• Q: “What’s the difference between LEFT JOIN and INNER JOIN?” — left keeps non-matching left rows with NULLs.
• Q: “WHERE vs HAVING?” — WHERE before grouping, HAVING after.
• Follow-up: “What’s wrong with WHERE x NOT IN (subquery) if the subquery has NULLs?” — always returns no rows; use NOT EXISTS.
• Follow-up: “What’s 3NF?” — non-key columns depend on the key, whole key, nothing but the key.

See 02_indexes.md, 05_relations_keys.md, 12_window_functions_cte.md.