SQL Joins Table
| Join type | Returns | When the other side has no match |
|---|---|---|
| INNER JOIN | Only matching rows from both tables | Row is excluded entirely |
| LEFT JOIN | All rows from the left table, matched rows from the right | Right columns are NULL |
| RIGHT JOIN | All rows from the right table, matched rows from the left | Left columns are NULL |
| FULL OUTER JOIN | All rows from both tables | Missing side is NULL |
| CROSS JOIN | Every row × every row (Cartesian product) | No join condition - every combination |
SQL joins combine rows from two tables based on a condition - and the five join types determine what happens when there is no match. INNER JOIN keeps only matches, LEFT JOIN preserves the entire left table, RIGHT JOIN preserves the right, FULL OUTER JOIN keeps everything, and CROSS JOIN produces every combination.
The interview question is always LEFT JOIN with IS NULL - which finds rows in one table that have no match in the other. The production question is why your CROSS JOIN is returning 10 million rows.
How to use
- Filter by join type or keyword - inner, outer, cross - to see the one you need.
- Read the third column: what happens to rows that have no match determines the join type.
- Use LEFT JOIN + IS NULL to find unmatched rows - the most common real-world join pattern.
Frequently asked questions
What is the difference between LEFT JOIN and LEFT OUTER JOIN?
Nothing - OUTER is optional in the syntax. LEFT JOIN and LEFT OUTER JOIN are identical. The OUTER keyword exists for readability and symmetry with FULL OUTER JOIN, but every database engine treats them the same. Most style guides drop the OUTER for brevity.
Can I JOIN more than two tables?
Yes - joins chain left to right. A JOIN B JOIN C first joins A and B, then joins the result with C. Each join produces an intermediate virtual table that the next join operates on. The order matters for LEFT/RIGHT joins because it determines which table’s rows are preserved at each step.
What is the difference between ON and WHERE in a LEFT JOIN?
Conditions in ON filter which rows match during the join, but NULL rows from the left table are still preserved. Conditions in WHERE filter the final result after the join - so putting a right-table condition in WHERE turns a LEFT JOIN into an INNER JOIN by eliminating the NULL rows. This is the most common SQL logic bug.
When would I use a CROSS JOIN?
CROSS JOIN produces every combination - useful for generating all possible pairs (products × regions for a sales matrix), creating date spines (every date × every store), or building test data. With a WHERE clause it behaves like an INNER JOIN with a complex condition.