SQL fundamentals

Article.

Joins, grouping, subqueries and normalization are asked at every level. The senior version of each answer is about the cases where SQL does something other than what the query appears to say: NULLs, outer joins that quietly become inner joins, and aggregates that double-count.

Why does WHERE id NOT IN (subquery) return nothing at all? The answer is three-valued logic, and it explains most of the traps below. Outputs are from PostgreSQL 18.4, on 50,000 users and 200,000 orders.

NULL and three-valued logic

A comparison with NULL is neither true nor false; it is NULL (unknown). WHERE, ON and HAVING keep a row only when the condition is true, so unknown drops it just as false does.

SELECT NULL = NULL,                      -- NULL, not true
       NULL <> 1,                        -- NULL
       NULL IS DISTINCT FROM NULL,       -- false: the NULL-safe comparison
       1 NOT IN (2, NULL);               -- NULL: 1 <> 2 AND 1 <> NULL

The rules that follow from it:

• Test with IS NULL, never = NULL, which matches nothing.
• IS [NOT] DISTINCT FROM treats two NULLs as equal; use it when comparing nullable columns, for example in change detection.
• A UNIQUE constraint allows many NULLs by default. PostgreSQL 15+ has UNIQUE NULLS NOT DISTINCT for the other behaviour.
• CHECK passes on NULL. A check constraint rejects only false, so CHECK (price > 0) accepts a NULL price; add NOT NULL.

JOINs

SELECT u.name, COUNT(o.id) AS order_count   -- COUNT(o.id), not COUNT(*)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;                              -- users with no orders get 0

COUNT(*) there would give 1 for a user with no orders, because the outer join produced one row of NULLs for them.

An accidental cartesian product rarely comes from writing CROSS JOIN. It comes from the old comma syntax, FROM a, b WHERE ..., with a join condition missing from the WHERE; JOIN without ON is a syntax error, which is one reason to prefer explicit joins.

Self-join

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

One level of a hierarchy. For arbitrary depth, a recursive CTE (CTEs and recursive queries).

WHERE vs ON in an outer join

-- predicate in ON: every user kept; only orders >= 100 attached
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.total >= 100;

-- predicate in WHERE: users with no such order disappear
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.total >= 100;

In the second query o.total is NULL for unmatched users, NULL >= 100 is unknown, and the row is dropped. The planner knows this and says so:

Hash Join                         <- not "Hash Left Join"
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders o
        Filter: (total >= '100'::numeric)

The LEFT JOIN became an INNER JOIN. Filters on the optional side belong in ON; a WHERE o.user_id IS NULL is the one right-side filter that keeps the outer join meaningful (it is an anti-join, below).

GROUP BY and aggregates

SELECT user_id, COUNT(*) AS n, SUM(amount)
FROM transactions
WHERE created_at >= '2026-01-01'      -- rows, before grouping
GROUP BY user_id
HAVING COUNT(*) >= 10;                -- groups, after aggregation

What may appear in SELECT

Every selected column must be grouped, aggregated, or functionally dependent on a grouped primary key. PostgreSQL accepts this:

SELECT u.id, u.name, COUNT(o.id)       -- u.name not in GROUP BY
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;                          -- u.id is the primary key

MySQL enforces the same rule with ONLY_FULL_GROUP_BY, on by default since 5.7; before that it returned an arbitrary value from the group. SQLite still does.

HAVING vs WHERE

WHERE filters rows before grouping, HAVING filters groups after it, so an aggregate can only be filtered in HAVING. A non-aggregate condition put in HAVING is not slower in PostgreSQL: the planner moves it into the scan.

-- ... GROUP BY country HAVING country = 'DE'
GroupAggregate
  ->  Seq Scan on users
        Filter: (country = 'DE'::text)

Put it in WHERE anyway; that is where a reader looks for it.

Aggregates and NULLs

• COUNT(*) counts rows; COUNT(col) skips NULLs: 200,000 against 199,800 on the orders table, where 200 orders have no user_id.
• SUM, AVG, MIN, MAX ignore NULLs, so AVG(rating) is the average of the rated rows only.
• SUM of no rows is NULL, not 0. Wrap it in COALESCE(SUM(x), 0) when the result feeds arithmetic or an API.
• FILTER (WHERE ...) computes several conditional aggregates in one pass: COUNT(*) FILTER (WHERE status = 'paid').

The fan-out trap

Join two one-to-many tables to the same parent and aggregate, and every row is multiplied by the other side’s matches:

-- user 1: payments 100 + 50, refunds 10 + 20 + 5
SELECT p.user_id, SUM(p.amount) AS paid, SUM(r.amount) AS refunded
FROM payments p JOIN refunds r USING (user_id)
GROUP BY p.user_id;
-- paid 450, refunded 70: each payment counted 3 times, each refund twice

The true totals are 150 and 35. Aggregate each child before joining (a subquery or CTE per table), then join the one-row-per-user results. A report that is “a bit high” for some customers is usually this.

Subqueries, semi-joins and anti-joins

-- scalar: one value per outer row
SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id)
FROM users u;

-- semi-join: users with at least one order
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- anti-join: users with no orders
SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id
WHERE o.user_id IS NULL;

IN vs EXISTS

The old advice, “EXISTS stops at the first match, IN materializes the list”, describes engines of twenty years ago. PostgreSQL turns both into the same semi-join; IN above planned as a hash join against the distinct user_ids. Pick whichever reads better.

NOT IN vs NOT EXISTS

Here they differ, and not in speed first but in the answer. 200 orders have a NULL user_id:

id NOT IN (a, b, NULL) means id <> a AND id <> b AND id <> NULL, and the last term is unknown for every id, so no row is ever true. Because of that, the planner cannot turn NOT IN into an anti-join; it runs a subplan per row:

NOT EXISTS                        NOT IN
Hash Right Anti Join              Seq Scan on users
  Hash Cond: (o.user_id = u.id)     Filter: (NOT (ANY (id = (hashed SubPlan 1).col1)))

When the subquery’s result does not fit in work_mem, the hashed subplan becomes a plain one that rescans the list for every outer row. With work_mem = 64kB and 5,000 users, NOT IN took 1.5 s and NOT EXISTS 53 ms, and the gap grows with the square of the sizes.

Use NOT EXISTS, or LEFT JOIN ... WHERE o.user_id IS NULL, which plans the same anti-join. Test the join column: with WHERE o.id IS NULL the answer was the same but the planner did not prove it, and ran a left join plus a filter.

Normalization

Normal forms remove the redundancy that lets the same fact disagree with itself. Most OLTP schemas aim for 3NF.

• 1NF: atomic values. No comma-separated lists in a column; a user_tags(user_id, tag) table instead of users.tags = 'python,sql'.
• 2NF: no partial dependency. With a key of (order_id, product_id), product_name depends on product_id alone and belongs in products.
• 3NF: no transitive dependency. users.zip_city depends on zip_code, not on the user; it belongs in a zips table.

The one-line version: every non-key column depends on the key, the whole key, and nothing but the key.

-- violates 2NF: product_name repeats per order line
CREATE TABLE order_items (
    order_id     int,
    product_id   int,
    product_name text,
    quantity     int,
    PRIMARY KEY (order_id, product_id)
);

Arrays and jsonb columns bend 1NF on purpose. They fit data that is read and written as a whole (a settings blob, a list of labels shown together); once you filter, join or enforce constraints on the elements, it wants to be a table.

When to denormalize

Denormalize when a measured read path is too slow, not before: reporting tables (star schema), cached aggregates such as users.order_count, a copy of a field to avoid a join on a hot path. Every copy needs a sync plan, a trigger, the application, or a materialized view refreshed on a schedule, and an accepted answer to “what if they disagree?”

Some copies are not denormalization at all. order_items.unit_price looks like a duplicate of products.price, but it records the price at the time of the order, a different fact; normalizing it away corrupts history.

Where it breaks in production

NOT IN after a column becomes nullable

A NOT IN (SELECT ...) report works for years, then a migration makes the column nullable, one NULL arrives, and the report returns zero rows with no error. Ban NOT IN with a subquery in review; NOT IN with a literal list of constants is fine.

A LEFT JOIN that lost its rows

A dashboard of “all customers and their recent orders” adds WHERE o.created_at > now() - interval '30 days' and silently drops every customer without a recent order. The plan shows Hash Join where Hash Left Join was expected; move the filter into ON.

Inflated totals

Revenue joined with refunds, or orders joined with shipments, then summed: totals are multiplied for exactly the customers with several of both. Tests with one row per child never catch it. Aggregate before joining.

OFFSET pagination

ORDER BY id OFFSET 190000 LIMIT 20 read and discarded 190,000 index entries: 38 ms. The keyset form, WHERE id > 190000 ORDER BY id LIMIT 20, took 0.4 ms and stays flat at any depth. Keyset also does not skip or repeat rows when inserts land on an earlier page. Its cost: no jumping to page 500, and the sort key must be unique (ORDER BY created_at, id).

SELECT * in application code

It ships columns nobody reads, prevents index-only scans, and changes shape when a column is added: an ORM-free mapper reading by position breaks, and a view defined with * keeps the old column list.

The trade not taken

Most of these traps come from SQL’s choice of NULL as one marker for both “unknown” and “not applicable”. The alternative, avoiding NULLs with NOT NULL and explicit defaults or separate tables, is good design wherever a real default exists, and it makes NOT IN and outer-join filters safe.

It cannot remove NULLs altogether: an outer join creates them in every query, whatever the schema says. So the working rule is both: NOT NULL by default in the schema, and queries written as if NULL can still appear.

Related

• How a query is processed - the logical order of clauses
• Window functions - window functions
• CTEs and recursive queries - CTEs and hierarchies
• Relationships and Keys in PostgreSQL - keys and relationships
• How a B-tree index works - what makes keyset pagination fast
• Reading a query plan - reading the plans above