SQL JOIN reference

A SQL JOIN combines rows from two or more tables based on a related column. Understanding which type of join to use is one of the most important skills in SQL - and the source of many bugs when the wrong type is chosen.

JOIN Type	Returns	When no match in right table…
INNER JOIN	Only rows where the join condition is true in both tables.	The left row is excluded entirely.
LEFT JOIN (LEFT OUTER JOIN)	All rows from the left table; matching rows from the right.	Right-table columns are NULL for that row.
RIGHT JOIN (RIGHT OUTER JOIN)	All rows from the right table; matching rows from the left.	Left-table columns are NULL for that row.
FULL OUTER JOIN	All rows from both tables; NULLs fill where there is no match.	Non-matching rows from both sides are included with NULLs.
CROSS JOIN	Cartesian product - every row in left paired with every row in right.	N/A (no condition; all combinations returned).

Worked examples

Given two tables: orders(order_id, customer_id, amount) and customers(customer_id, name).

INNER JOIN - orders that have a matching customer:

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

LEFT JOIN - all orders, even those with no customer record:

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

FULL OUTER JOIN - all orders and all customers, matched where possible:

SELECT o.order_id, c.name, o.amount
FROM orders o
FULL OUTER JOIN customers c ON o.customer_id = c.customer_id;

Choosing the right join

Use INNER JOIN when you only want rows where a relationship exists in both tables. This is the most common join type.
Use LEFT JOIN when you want all rows from the primary (left) table regardless of whether a match exists in the secondary table. The most frequent alternative to INNER JOIN. Example: “show all customers, and their orders if they have any.”
Use FULL OUTER JOIN when you need to identify unmatched rows in either table. Useful for data reconciliation.
The #1 mistake: using INNER JOIN when you needed LEFT JOIN. This silently drops rows where no match exists - resulting in undercounted aggregates or missing records that are hard to notice.

Venn diagram mental model

The classic Venn diagram visualization maps each join type to areas of two overlapping circles:

INNER JOIN = the intersection (only overlapping rows)
LEFT JOIN = entire left circle (all left rows + overlapping right rows)
RIGHT JOIN = entire right circle (overlapping left rows + all right rows)
FULL OUTER JOIN = the union (all rows from both circles)

This mental model is helpful for selecting the right join type quickly. Note that the Venn diagram is a simplification - it works for understanding inclusion/exclusion but doesn't capture CROSS JOINs or SELF JOINs.

CROSS JOIN and SELF JOIN

A CROSS JOIN produces the Cartesian product: every row in table A paired with every row in table B. A 100-row table crossed with a 200-row table produces 20,000 rows. CROSS JOINs are useful for generating combinations (e.g., all color/size combinations for products) but are dangerous when used accidentally on large tables.

A SELF JOIN joins a table to itself using an alias. This is used to compare rows within the same table - for example, to find employees and their managers in an employees table where each row has a manager_id column that references another row in the same table.

Performance implications

INNER JOIN is generally the fastest - the database can use index lookups and discard non-matching rows early.
OUTER JOINs are slightly more expensive because they must track which rows had no match and fill in NULLs.
FULL OUTER JOIN is the most expensive join type - it requires a complete pass through both tables and cannot be short-circuited.
NULL join columns: if your join condition includes a nullable column, NULL != NULL in SQL - rows where the join key is NULL will never match. Use IS NOT DISTINCT FROM (PostgreSQL) or COALESCE if you need to match NULLs.