Modeling Relations with Joins

An admin dashboard needs every user next to their most recent order. Two tables: users (id, email, created_at) and orders (id, user_id, total, created_at), related by the foreign key orders.user_id. The instinct — select users, loop, select each user's orders — is the N+1 pattern this topic kills at the end. One query can do it, and the only decision that matters is which join.

An INNER JOIN returns only rows with a match on both sides. Users with zero orders simply vanish from the result. Nothing errors, nothing warns — the dashboard just quietly shows 847 of 1,000 users, and you discover the gap when someone asks why the newest signup is missing.

A LEFT JOIN keeps every row of the left table, filling the right side's columns with NULL when there is no match. That NULL is not missing data — it is the answer to "does a match exist?".

$sql = 'SELECT u.id, u.email,
               o.id AS order_id, o.total, o.created_at AS last_order_at
        FROM users u
        LEFT JOIN orders o ON o.user_id = u.id
        ORDER BY u.id, o.created_at DESC';
$rows = $pdo->query($sql)->fetchAll();

The same skeleton answers three different questions depending on two small edits:

Test the NULL check on the right table's key column (o.id or o.user_id), not on a nullable-by-design column like o.total — a real order with a NULL total would be misclassified as "no order".

Two mechanical details interviewers probe. First, aliases: once you write FROM users u LEFT JOIN orders o, you must qualify every column that exists in both tables — id alone is ambiguous and MySQL rejects the query with Column 'id' in field list is ambiguous. Get in the habit of aliasing and qualifying; it also shortens queries enough that nested subqueries stay readable.

Second, the old comma syntax deserves a warning because you will meet it in legacy code:

-- Same join, two styles — MySQL optimizes them identically
SELECT u.email, o.total FROM users u, orders o WHERE o.user_id = u.id;
SELECT u.email, o.total FROM users u INNER JOIN orders o ON o.user_id = u.id;

The comma style states the relationship inside WHERE, mixed in with your filters. Remove or mistype one filter and the query still runs — it degrades into a cross join (every user × every order: 1,000 users × 50,000 orders = 50 million rows) with no error beyond a very slow query and an exhausted memory limit. JOIN ... ON makes the relationship structural; a WHERE clause can only ever remove rows, never accidentally create them. Cross joins are occasionally what you want — a sizes × colors product matrix, for example — but they should be deliberate.

One last property you must be able to state from memory, because everything in the next two chapters depends on it: a join multiplies rows, it does not nest them. A user with 3 orders appears in 3 result rows, each repeating the user's email. The dashboard that shows "users with their orders" has to either pick one order per user (window functions or PHP-side selection) or group the rows — and grouping by hand is where COUNT and SUM start lying.

The finance dashboard asks for "orders and revenue per user". The schema has three levels — users, orders (user_id, total), order_items (order_id, price) — and the first query anyone writes is the three-way join with aggregates slapped on top:

-- BROKEN: two one-to-many joins multiply rows before the aggregate runs
SELECT u.email, COUNT(*) AS orders, SUM(o.total) AS revenue
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY u.id, u.email;

Walk one concrete case through the row math. User 42 has one $50 order containing three items. The orders join produces one row; the order_items join triples it to three — one per item, each repeating the $50 total. COUNT(*) therefore says 3 orders, and SUM(o.total) says $150 for a single $50 order. Every order with N items is counted N times in both columns, and the report is wrong in direct proportion to average items per order. The join multiplied rows before GROUP BY saw them; aggregates can only count the rows they are given.

Three fixes, in order of how often you will reach for them:

1. Pre-aggregate in a derived table, then join the summary. Each level is collapsed to one row per parent before the joins meet, so nothing multiplies:

SELECT u.email, COALESCE(s.orders, 0) AS orders, COALESCE(s.revenue, 0) AS revenue
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS orders, SUM(total) AS revenue
    FROM orders GROUP BY user_id
) s ON s.user_id = u.id;

COALESCE replaces the NULLs the LEFT JOIN produced for users with no orders, so the dashboard shows 0, not blank.

2. Aggregate one level at a time in the outer query. If the report only needs order-level facts, aggregate the item detail away in a subquery per order, then sum that.

3. COUNT(DISTINCT ...) when you truly need one count across a multiplied join. COUNT(DISTINCT o.id) counts each order once no matter how many item rows it spawned. It fixes counts; it cannot fix SUM — there is no SUM(DISTINCT o.total) shortcut that works, because two different orders can legitimately have the same total and DISTINCT would wrongly collapse them. For sums, restructure the query (fix 1), never deduplicate values.

Two distinctions inside the aggregates themselves come up constantly:

• COUNT(*) versus COUNT(o.id): COUNT(*) counts result rows; COUNT(column) counts non-NULL values of that column. On a LEFT JOIN'd result, COUNT(*) counts the never-ordered users' placeholder rows while COUNT(o.id) counts only real orders — both are correct answers to different questions, which is why a report that mixes them up looks almost right.
• HAVING versus WHERE: WHERE filters rows before grouping and cannot see aggregates; HAVING filters groups after. "Users with more than 5 orders totaling over $500" is GROUP BY u.id HAVING COUNT(*) > 5 AND SUM(o.total) > 500. Putting the aggregate condition in WHERE is a syntax error, and putting a row condition in HAVING works but scans more than it should.

The habit that prevents all of this: after writing any join feeding an aggregate, count the rows by hand for one concrete parent with multiple children — if the number is bigger than the parent count, your SUM is already wrong. With the reporting query now trustworthy, the last thing that can sink it is how many times per page you run it.

A page renders 50 users with their orders. The developer follows the "obvious" structure — get the users, then get each user's orders:

$users = $pdo->query('SELECT id, email FROM users ORDER BY created_at DESC LIMIT 50')
    ->fetchAll();

foreach ($users as $user) {
    $stmt = $pdo->prepare('SELECT id, total FROM orders WHERE user_id = ?');
    $stmt->execute([$user['id']]);
    $user['orders'] = $stmt->fetchAll();   // one extra round trip per user
}

That is 1 query for the list plus N queries inside the loop — the N+1 query pattern. With 50 users the page issues 51 queries; with 500 it issues 501. Every individual query is fast — the slow query log stays clean — yet the page takes two seconds, because the cost is the round trips, serialized one after another. The signature is unmistakable in any profiler or MySQL general log: the same statement with a different bound parameter, over and over, and a query count that scales linearly with page size. This is also the shape most ORMs produce by default ("lazy loading"), which is why "what is the N+1 problem?" is one of the most-asked interview questions on data access.

The fix is one query per table, not one query per row. Two standard shapes:

// Shape A: one JOIN, then group the rows in PHP
$rows = $pdo->query(
    'SELECT u.id, u.email, o.id AS order_id, o.total
     FROM users u
     LEFT JOIN orders o ON o.user_id = u.id
     ORDER BY u.id'
)->fetchAll();

$byUser = [];
foreach ($rows as $row) {
    $byUser[$row['id']]['email'] = $row['email'];
    $byUser[$row['id']]['orders'][] = [
        'id'    => $row['order_id'],
        'total' => $row['total'],
    ];
}

The bucketing loop is the client-side half of the deal: the join returns the flat, multiplied shape from the first chapter, and PHP re-nests it — null order fields becoming an empty orders list for users who never ordered.

// Shape B: two queries, grouped the same way — constant at 2 regardless of page size
$orders = $pdo->prepare('SELECT id, user_id, total FROM orders WHERE user_id IN (...)');

Shape B matters because a JOIN is not always free. Joining two large one-to-many tables multiplies rows — 50 users × 200 orders each × 5 items is 50,000 rows shipped to PHP to render 50 users. When the child sets are big, fetch each table with its own query (the second one WHERE user_id IN (...) over the page's ids, built as one statement with a dynamically sized parameter list), then run the same bucketing loop. Two queries is still constant; N+1 is not.

Three supporting habits, each a common interview follow-up:

• Prepare outside the loop. If some logic genuinely requires a per-row query, $pdo->prepare() at least once before the loop and execute() inside — statement preparation is a round trip too. It softens the symptom; it does not cure N+1.
• Order in SQL, not in PHP. ORDER BY u.id in the query means the bucketing loop appends children in order; sorting after the fact is another O(N log N) surprise.
• Bound the page. All three shapes assume a LIMIT-ed page. "Fetch all users and all their orders into memory" is N+1's cousin — the unbounded fetchAll.

Diagnose by counting: enable the general log or your profiler on a staging page and look for the repeated-statement signature. The query count should be a small constant — 2 or 3 for this page — and if it moves when you move the page-size slider, you are back in N+1 territory. Everything on this page eventually reaches the browser as HTML, and any user-controlled string in those rows — an email, a display name — must be handled by the rules in Escaping Output to Prevent XSS.