SQL Joins Explained: Inner, Left, Right, and Full Outer

A SQL join combines rows from two or more tables based on a related column between them, letting a single query pull together data that’s been split across separate tables — a common outcome of database normalization, which deliberately spreads related data across multiple tables to avoid duplication. The join types differ in what happens to rows on either side that don’t have a match.

Setting up the example

Consider two simple tables. customers has one row per customer:

id	name
1	Ava
2	Ben
3	Cleo

orders has one row per order, referencing a customer by customer_id:

id	customer_id	total
101	1	40
102	1	15
103	2	80

Notice Cleo has no orders, and no order references a customer 4. That mismatch is exactly what the different join types handle differently.

Inner join: only matching rows

An inner join returns only rows where the join condition matches on both sides:

SELECT customers.name, orders.total
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

This returns Ava (twice, once per order) and Ben — Cleo is excluded entirely, since she has no matching row in orders. Inner join is the default when people just write JOIN without a qualifier, and it’s the right choice whenever you only care about rows that exist on both sides.

Left join: keep everything on the left

A left join (or LEFT OUTER JOIN) returns every row from the left table, matched with rows from the right table where possible, and NULL for the right-hand columns where there’s no match:

SELECT customers.name, orders.total
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

This returns Ava twice, Ben once, and Cleo once with NULL for total. Left joins are the workhorse for “show me all X, along with any related Y” queries — every customer, whether or not they’ve ordered anything, which is exactly the case an inner join would silently drop.

Right join: keep everything on the right

A right join is the mirror image: every row from the right table is kept, with NULL on the left where there’s no match. In practice, right joins are rare in real codebases, because any right join can be rewritten as a left join by swapping the table order — most style guides prefer sticking to left joins consistently rather than mixing left and right joins in the same codebase, purely for readability.

Full outer join: keep everything from both sides

A full outer join returns every row from both tables, with NULL filled in on whichever side lacks a match:

SELECT customers.name, orders.total
FROM customers
FULL OUTER JOIN orders ON customers.id = orders.customer_id;

This would return Ava twice, Ben once, Cleo once with a NULL total, and — if an order existed with no matching customer — that order too, with a NULL name. Full outer joins are useful for finding mismatches on either side of a relationship, such as auditing for orphaned records, but they’re less common in everyday application queries than inner and left joins.

Comparison table

Join type	Unmatched left rows	Unmatched right rows	Typical use
Inner	Dropped	Dropped	Only rows that exist on both sides
Left	Kept, NULL on right	Dropped	All of table A, plus related B if it exists
Right	Dropped	Kept, NULL on left	Rarely used; equivalent to a left join with tables swapped
Full outer	Kept, NULL on right	Kept, NULL on left	Auditing mismatches on either side

Joins and performance

A join’s performance depends heavily on whether the join column is indexed — see database indexing for how an index turns a join from a full table scan into a fast lookup. Joining large tables without an index on the join column is one of the most common causes of slow queries in production databases, and it gets worse as either table grows, since the database has no shortcut to match rows without scanning. In PostgreSQL, EXPLAIN ANALYZE shows exactly which join strategy the planner picked and where the time went.

Joins are also where the N+1 query problem usually gets fixed — instead of issuing one query per row in a loop, a single joined query (or a batched fetch) retrieves everything at once.

Joins vs other ways of combining data

A join combines columns from related tables side by side, based on a matching key. That’s different from a UNION, which stacks rows from two queries with the same columns on top of each other rather than joining them side by side. It’s also different from a materialized view, which can precompute the result of a complex join once and store it, so repeated queries don’t have to redo the join every time. And joins operate on a snapshot consistent with whatever isolation level and transaction the query runs under — a join spanning two tables being written to concurrently can see different consistency guarantees depending on that isolation level.

If you need aggregated results across joined rows — running totals, rankings — window functions operate on the joined result set without collapsing rows the way GROUP BY does.

The takeaway

Inner joins keep only matching rows from both tables; left joins keep everything from the left table and fill in NULL where the right side doesn’t match; right joins do the mirror image and are rarely used in practice; full outer joins keep everything from both sides. Reach for an inner join by default, a left join whenever you need “all of A, plus related B if it exists,” and a full outer join only when auditing for mismatches on either side of a relationship. Whichever you use, make sure the join column is indexed — an unindexed join is one of the fastest ways to make a database slow.