Tips 8–16: Joining Data

Joins are where most slow queries live: two big tables multiplied together can produce enormous intermediate results. These 9 tips keep joins lean.

Join recap: a join combines rows from two tables where the join condition (ON …) is true. The engine physically does this with Nested Loops, Hash Match or Merge Join operators.

Tip 8 — Know your join types

Use INNER JOIN when possible — it is the most efficient, because unmatched rows are thrown away early and the optimizer is free to reorder the tables.

-- ❌ Bad: LEFT JOIN when you only want players that HAVE a team
SELECT p.name, t.team_name
FROM players AS p
LEFT JOIN teams AS t ON p.team = t.team;

-- ✅ Good
SELECT p.name, t.team_name
FROM players AS p
INNER JOIN teams AS t ON p.team = t.team;

A LEFT JOIN must keep every left row (and fill NULLs), which limits how the optimizer can rearrange the plan. Use it only when you really need the unmatched rows.

Tip 9 — Use explicit ANSI joins

Write INNER / LEFT JOIN … ON, not the old comma-style join with the condition in WHERE.

-- ❌ Bad: old style
SELECT p.name, t.team_name
FROM players p, teams t
WHERE p.team = t.team AND p.country = 'India';

-- ✅ Good: ANSI join
SELECT p.name, t.team_name
FROM players AS p
INNER JOIN teams AS t
    ON p.team = t.team
WHERE p.country = 'India';

Both give the same result and usually the same plan — but in comma style, forgetting the join condition silently creates a cross join (every player × every team = 42 rows). ANSI syntax separates join logic (ON) from filter logic (WHERE), so mistakes are visible.

Tip 10 — Index the ON columns

Columns used in the join condition should be indexed.

-- ❌ Bad: ON p.team = t.team with no index on players.team
-- ✅ Good
CREATE INDEX IX_team ON players (team);

With an index, a nested-loop join can seek the matching rows for each outer row instead of scanning the whole inner table each time. Primary keys are already indexed; foreign keys are not (see tip #25).

Tip 11 — Filter BEFORE joining

Reduce big tables first, so fewer rows have to be joined.

-- ❌ Bad: join everything, then WHERE
SELECT i.player, i.runs, t.city
FROM innings AS i
INNER JOIN teams AS t ON i.team = t.team
WHERE i.runs >= 70;

-- ✅ Good: filter in a subquery, then join the small result
SELECT x.player, x.runs, t.city
FROM (
    SELECT player, team, runs
    FROM innings
    WHERE runs >= 70
) AS x
INNER JOIN teams AS t ON x.team = t.team;

Note: modern optimizers often push a simple WHERE down automatically ("predicate pushdown"), so both may get the same plan. Writing the filter early makes your intent explicit and helps in complex queries where the optimizer can't do it — check the plan.

Tip 12 — Aggregate BEFORE joining

Summarise large tables first, then join the small summary.

-- ❌ Bad: join every innings row to teams, then GROUP BY
SELECT t.team_name, SUM(i.runs) AS total_runs
FROM innings AS i
INNER JOIN teams AS t ON i.team = t.team
GROUP BY t.team_name;

-- ✅ Good: 12 innings rows -> 4 team totals -> join 4 rows
SELECT t.team_name, x.total_runs
FROM (
    SELECT team, SUM(runs) AS total_runs
    FROM innings
    GROUP BY team
) AS x
INNER JOIN teams AS t ON x.team = t.team
ORDER BY x.total_runs DESC;

With 100 million ball-by-ball rows, joining 10 team totals instead of 100 million rows is a huge saving.

Tip 13 — No OR in join logic

OR in an ON condition usually prevents index seeks and forces slow nested loops. Replace it with two joins combined by UNION where possible.

Example: a search box where fans type a player's name or nickname.

-- ❌ Bad
SELECT s.term, p.name
FROM search_terms AS s
INNER JOIN player_profiles AS p
    ON s.term = p.name OR s.term = p.nickname;

-- ✅ Good: one join per condition, combined
SELECT s.term, p.name
FROM search_terms AS s
INNER JOIN player_profiles AS p ON s.term = p.name
UNION
SELECT s.term, p.name
FROM search_terms AS s
INNER JOIN player_profiles AS p ON s.term = p.nickname;

Each half can now seek its own index (name / nickname). Use UNION (not UNION ALL) here so a row matching both conditions isn't returned twice.

Tip 14 — Watch nested loops

Check the execution plan for slow Nested Loops joins over big inputs; use SQL hints only if really needed.

-- Last resort: force a hash join for this query only
SELECT p.name, i.runs
FROM players AS p
INNER JOIN innings AS i ON i.player = p.name
OPTION (HASH JOIN);

Hints override the optimizer forever, even after your data changes. Fix the cause first (missing index, stale statistics), and hint only when you've proven it helps.

Tip 15 — UNION ALL if duplicates are OK

UNION removes duplicates, which costs an extra sort/hash step. If duplicates are fine (or impossible), use the faster UNION ALL.

-- ❌ Bad (when duplicates don't matter)
SELECT name FROM batters
UNION
SELECT name FROM bowlers;

-- ✅ Good
SELECT name FROM batters
UNION ALL
SELECT name FROM bowlers;

UNION returns 8 names, UNION ALL returns 10 (Ravindra Jadeja and Hardik Pandya appear in both lists). The playground plan shows UNION USING TEMP B-TREE for UNION versus plain UNION ALL — that temp B-tree is the de-duplication cost.

Tip 16 — Duplicates not OK? UNION ALL + DISTINCT

If you need unique rows, the cheat sheet suggests UNION ALL wrapped in SELECT DISTINCT instead of UNION for better performance.

-- ❌
SELECT name FROM batters
UNION
SELECT name FROM bowlers;

-- ✅
SELECT DISTINCT name
FROM (
    SELECT name FROM batters
    UNION ALL
    SELECT name FROM bowlers
) AS x;

Both return the same 8 unique names. The win comes from doing one de-duplication at the end — most noticeable when you combine many SELECTs. Often the optimizer builds the same plan for both, so (as always) compare the plans.

Common mistakes

• ❌ Comma joins with a missing condition → accidental cross join. ✅ ANSI JOIN … ON.
• ❌ Putting a right-table filter of a LEFT JOIN in WHERE (turns it into an inner join). ✅ Put it in ON.
• ❌ Using UNION everywhere "to be safe". ✅ Use UNION ALL unless you truly need de-duplication.

Interview tip

Q: What's the difference between UNION and UNION ALL, performance-wise? A: UNION removes duplicates, so it must sort or hash the combined result — extra CPU, memory and possibly tempdb. UNION ALL simply appends the results and is faster. Use UNION ALL whenever duplicates are acceptable or impossible.