SQL Joins — INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF Joins
Fast recall
Quick revision
Core answer
Whether you understand the NULL-handling behavior of each join type and can choose the correct one for a query requirement, not just recite definitions.
Using LEFT JOIN but adding a WHERE clause on the right table's column, which silently converts it to an INNER JOIN by filtering out NULL rows.
Explain how the query planner chooses between nested loop, hash join, and merge join algorithms, and why indexing join columns is critical for performance.
Interview-ready guide
Interview answer
Most people learn joins as "combining two tables" — but the more precise framing is: what happens to a row that finds no match on the other side? That one question explains the difference between every join type.
INNER JOIN — matched rows only
- Returns only rows where both sides have a match — so
orders INNER JOIN customers ON orders.customer_id = customers.idexcludes any order whosecustomer_iddoesn't exist in customers, which means orphaned rows are silently dropped from the result - This is the most common join and the default when you write just
JOINwithout a qualifier
LEFT JOIN — all from the left, matched or not
- Returns every row from the left table, with NULL values on the right side for rows that find no match — so
customers LEFT JOIN ordersreturns customers who have never placed an order, with null in every order column - The critical trap: adding
WHERE orders.status = 'ACTIVE'after a LEFT JOIN silently converts it to an INNER JOIN, because null values fail the WHERE filter — move status filtering into the ON clause to preserve unmatched left rows
CROSS JOIN and SELF JOIN
- CROSS JOIN produces the Cartesian product — every left row paired with every right row; 100 rows x 200 rows = 20,000 result rows; useful for generating combinations or test data, almost never accidental
- SELF JOIN joins a table to itself using two aliases, which is the standard way to query hierarchies —
employees e1 JOIN employees e2 ON e1.manager_id = e2.idfetches each employee alongside their manager's name
Always index the join column on both sides — the planner chooses between nested loop, hash join, and merge join based on table sizes and index availability; an unindexed foreign key forces a sequential scan on the larger table for every join.
SQL Joins — INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF Joins
Direct Answer
SQL joins combine rows from two or more tables on a related column. INNER JOIN returns only matching rows. LEFT JOIN returns all left-side rows with NULLs for non-matches on the right. RIGHT JOIN mirrors LEFT. FULL OUTER JOIN keeps unmatched rows from both sides. CROSS JOIN produces the Cartesian product. SELF JOIN joins a table to itself for hierarchies or row-to-row comparisons. The ON vs WHERE distinction controls whether outer joins preserve unmatched rows.
Common Mistakes & Pitfalls
Using LEFT JOIN but adding a WHERE clause on the right table's column, which silently converts it to an INNER JOIN by filtering out NULL rows.
Check your understanding
Answer these without looking back at the article.
- How does the query planner decide between nested loop, hash join, and merge join?
- What is the difference between putting a condition in ON vs WHERE for outer joins?
- How do you optimize a slow JOIN query on large tables?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.