SQL Roadmap/Joins & Multi-Table Queries

Join Types: INNER, LEFT/RIGHT, FULL, Self-Joins

The mechanical shapes a join can take — matched-only, matched-plus-unmatched-on-one-side, matched-plus-unmatched-on-both-sides — and the one variant (self-join) that looks unfamiliar the first time you see it but is really just "join a table to itself."

!!!2/5Theory: 25m4 problems

The mental model: start from CROSS JOIN, then filter

Every join type is easiest to understand as "start with every possible pairing of rows from both tables, then keep only the pairings (and sometimes the leftovers) that matter." The join type decides which leftovers survive.

SELECT o.order_id, c.customer_name FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id;

INNER JOIN: only matched rows survive

An INNER JOIN keeps a row only if it has a match on both sides of the ON condition. If an order references a customer_id that doesn't exist in customers (orphaned data), that order silently disappears from the result — no error, no warning. This is the join type people reach for by default, but "silently disappears" is exactly why it's the wrong choice the moment the question is about missing data.

LEFT JOIN / RIGHT JOIN: keep one side unconditionally

SELECT c.customer_name, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id;

LEFT JOIN keeps every row from the left table, whether or not it has a match on the right — unmatched rows get NULL in every column that came from the right table. This is the workhorse for "find rows in A with no counterpart in B" questions: run the LEFT JOIN, then filter WHERE <right_table>.<any_column> IS NULL to keep only the rows that had no match at all (this is the "customers who never ordered" pattern, and it's covered in more depth as an anti-join in the next subtopic). RIGHT JOIN is the mirror image and is rarely used in practice — any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order, and most style guides (and interviewers) prefer that you do, purely for readability.

FULL OUTER JOIN: keep everything from both sides

Keeps every row from both tables, filling NULL on whichever side didn't have a match. Not supported natively by MySQL (as of the versions typically used in interview environments) — the standard workaround is LEFT JOIN ... UNION ... RIGHT JOIN or LEFT JOIN ... UNION ... (SELECT ... WHERE left_key IS NULL), which is worth knowing exists even if you never need to write it live, because "how would you do this on an engine that lacks FULL OUTER JOIN" is a real follow-up question.

Self-joins: joining a table to itself

Nothing new mechanically — it's the exact same JOIN syntax, just with the same table referenced twice under different aliases so you can compare two rows from the same table to each other:

-- Employees earning more than their own manager SELECT e.name AS employee FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary;

The alias (e and m above) is mandatory — without it, the query can't tell which occurrence of employees a given column reference means. Self-joins are the standard tool for hierarchical/reflexive relationships within one table: managers-and-reports, "find pairs of rows where X," or comparing a row to the row before/after it when ordered by some column (though window functions, covered later, are usually a cleaner tool for that specific comparison — see Running Totals & Frame Clauses).

Why interviewers care

Join-type choice is one of the fastest ways to tell whether a candidate is pattern-matching syntax or actually reasoning about the data. "Find customers who never placed an order" and "find customers and their orders" look like nearly the same English sentence but require an INNER JOIN vs. a LEFT JOIN (plus a NULL filter) respectively — an interviewer who hears you say "join" without immediately specifying which kind, given a "missing data" question, will usually push on it.

Pitfalls and interview gotchas

  • Defaulting to INNER JOIN when the question is about missing/unmatched data. The single most common join-type mistake in interviews.
  • Putting a filter on the right-hand table's column in WHERE instead of the ON clause of a LEFT JOIN. LEFT JOIN orders o ON ... WHERE o.status = 'shipped' silently turns the LEFT JOIN back into something INNER-JOIN-like for that condition, because unmatched rows have o.status = NULL, and NULL = 'shipped' is UNKNOWN, which WHERE discards. If you want to keep unmatched left rows and filter the right side, the filter belongs in the ON clause instead: LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'shipped'.
  • Forgetting to alias a self-join, or aliasing both sides identically by accident.
  • Assuming RIGHT JOIN is exotic or different from LEFT JOIN. It's the same operation with table order swapped — know it exists, but default to LEFT JOIN for readability.

Where this goes next

Set Operations & Anti-Joins (next) formalizes the "missing data" pattern hinted at above (LEFT JOIN + IS NULL) alongside its alternatives (NOT IN, NOT EXISTS) — and covers exactly why one of those alternatives is dangerous on real-world nullable data.

Further Resources (Optional)

Practice Problems

Interview relevancy:!!!Critical — must-have!!Important — highly recommended!Good to have~Niche — rarely asked

Optional Practice (Extra Reps)

For once you've cleared the main set above and want more reps on this pattern. These don't count toward the roadmap's progress stats — solve them purely for your own benefit.