SQL Join Types Compared: Inner, Left, Right, Full

1. The core principle: what a join actually does

A join combines rows from two or more tables based on a condition, usually an equality comparison between a foreign key and a primary key. Conceptually, the database first forms the cartesian product of all possible row combinations and then filters out with the ON condition which combinations actually stay in the result set. In practice, the query planner optimizes this process with hash joins, merge joins or nested loop joins, but the logical result stays the same regardless of the chosen execution plan.

The decisive difference between the four join types is not in the intersection itself, but in how rows without a matching counterpart are handled. An inner join drops these rows entirely, a left join or right join keeps them from one of the two tables and fills the missing columns with NULL, a full join keeps them from both tables at once. Anyone who internalizes this one difference can derive each of the four join types from the definition instead of memorizing them.

For the following examples we use two simple tables: customers with customer data and orders with orders, linked via customers.id = orders.customer_id. Not every customer has an order, not every order is necessarily tied to an existing customer, which makes the differences between the join types visible.

2. Inner Join: only the intersection

The inner join is the most restrictive of the four join types and at the same time the most commonly used. It returns only row combinations where the join condition is satisfied on both tables. A customer without an order does not appear in the result set, an order with an invalid customer reference does not either. That makes the inner join the right choice when only complete, referentially consistent records are relevant, for example an invoice list where every row necessarily needs a customer and an order.

Syntactically, INNER JOIN and the short JOIN without a prefix are identical, both produce an inner join. Some teams still consistently use the spelled-out INNER JOIN, because it makes it immediately visible in code review that filtering is happening on purpose here, while a plain JOIN can easily be confused with an accidentally forgotten prefix.

-- Sample tables
-- customers: id, name
-- orders: id, customer_id, amount

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

-- customers table
-- id | name
-- 1  | Anna Berger
-- 2  | Tom Keller
-- 3  | Lisa Wolf        <- has no orders

-- orders table
-- id | customer_id | amount
-- 10 | 1           | 120.00
-- 11 | 1           | 45.00
-- 12 | 2           | 300.00
-- 13 | 99          | 15.00   <- orphaned, no matching customer

-- Result: only rows where both sides match
-- name         | order_id | amount
-- Anna Berger  | 10       | 120.00
-- Anna Berger  | 11       | 45.00
-- Tom Keller   | 12       | 300.00
-- Lisa Wolf and order 13 are both missing from the result

3. Left Join: keep every row of the left table

The left join, spelled out LEFT OUTER JOIN, keeps every row of the left, first named table, regardless of whether a matching row exists in the right table. If no match is found, all columns of the right table are filled with NULL. This is the join type of choice for the typical question "show me all X, even if they have no related Y", for example "all customers, including those without an order" or "all products, including those without a review".

A common use case for the left join is finding missing associations: you join left to right and then filter with WHERE right_table.id IS NULL to exactly the rows for which no matching counterpart exists. This pattern finds, for instance, customers with zero orders, without needing a separate subquery with NOT IN.

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

-- Result: every customer appears, even without orders
-- name         | order_id | amount
-- Anna Berger  | 10       | 120.00
-- Anna Berger  | 11       | 45.00
-- Lisa Wolf    | NULL     | NULL
-- Tom Keller   | 12       | 300.00

-- Common pattern: find customers with zero orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

-- name
-- Lisa Wolf

4. Right Join: the mirrored variant

The right join, spelled out RIGHT OUTER JOIN, behaves exactly as the mirror image of the left join: it keeps every row of the right table and fills missing values of the left table with NULL. Every RIGHT JOIN can be fully rewritten as a LEFT JOIN by swapping the table order, which is why many style guides deliberately avoid RIGHT JOIN and use only LEFT JOIN, to keep readability consistent and avoid switching between two mental models.

Still, the right join shows up regularly in generated SQL, in ORM output or in migration scripts, and anyone who does not read it as an inverted left join loses time while debugging. Important: PostgreSQL, MySQL and SQL Server all support RIGHT JOIN equally, it is not a proprietary extension, even though it is written less often in practice than its left-hand counterpart.

-- Right join: keep every row from orders, even orphaned ones
SELECT c.name, o.id AS order_id, o.customer_id
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id
ORDER BY o.id;

-- name         | order_id | customer_id
-- Anna Berger  | 10       | 1
-- Anna Berger  | 11       | 1
-- Tom Keller   | 12       | 2
-- NULL         | 13       | 99   <- orphaned order, kept because it's on the right

-- Equivalent rewrite using LEFT JOIN, swapped table order
SELECT c.name, o.id AS order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;
-- produces the identical result set

5. Full Join: nothing gets lost

The full join, spelled out FULL OUTER JOIN, combines the behavior of left and right join: it keeps rows from both tables, regardless of whether a counterpart exists, and fills the respective missing side with NULL. The result contains both customers without an order and orders without a valid customer in the same query, which makes it the right tool for data quality checks where both directions of the inconsistency should be visible at once.

An important practical note: MySQL does not natively support FULL OUTER JOIN up to and including version 8.0, while PostgreSQL, SQL Server and Oracle offer it directly. In MySQL, the full join is emulated through a combination of LEFT JOIN, RIGHT JOIN and UNION. This vendor-specific gap is one of the cases where standard SQL knowledge alone is not enough and you need to know the target database before using a full join in production code.

-- Full join: PostgreSQL, SQL Server, Oracle
SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

-- name         | order_id
-- Anna Berger  | 10
-- Anna Berger  | 11
-- Tom Keller   | 12
-- Lisa Wolf    | NULL     <- customer without any order
-- NULL         | 13       <- order without a valid customer

-- MySQL emulation, no native FULL OUTER JOIN before 8.0
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.name, o.id AS order_id
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

6. The cartesian product: the classic join mistake

The most common mistake with any join is a missing or incomplete ON condition. Without a join condition, for example with SELECT * FROM a, b or CROSS JOIN, the database combines every row of a with every row of b. With two tables of a thousand rows each, that produces a million result rows, a cartesian product that is almost never intended in a real application and produces reports with multiplied, wrong totals.

A subtler but equally common case arises with multiple join conditions when one of them is forgotten. If, for example, orders is joined to order_items only via customer_id instead of additionally via order_id, countless incorrect combinations arise for every customer with multiple orders and multiple line items, without an obvious error occurring, the query simply runs through and returns plausible-looking but wrong numbers.

-- WRONG: missing join condition creates a cartesian product
SELECT c.name, o.amount
FROM customers c, orders o;
-- every customer combined with every order: 3 customers x 4 orders = 12 rows

-- WRONG: incomplete join condition on multiple keys
SELECT o.id, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON o.customer_id = oi.customer_id;
-- missing order_id condition duplicates rows across unrelated orders

-- RIGHT: explicit and complete join condition
SELECT o.id, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON o.id = oi.order_id;

-- Defensive check: row count sanity check after any join
SELECT COUNT(*) AS row_count FROM orders;          -- baseline
SELECT COUNT(*) AS after_join
FROM orders o JOIN order_items oi ON o.id = oi.order_id;
-- after_join should be >= row_count, never a wild multiple

7. Multiple joins and join order

Real-world queries rarely join only two tables. With three or more tables, the order of the JOIN clauses does not decide logical correctness, but it does affect readability and sometimes performance if the optimizer does not rearrange the order itself. A proven convention is to start with the "central" table, for example orders, and join step by step from there through customers, order_items and products, so the query stays traceable from top to bottom.

With multiple consecutive LEFT JOIN clauses, special care is needed: every additional left join onto a table with several matching rows multiplies the previous result set. A customer with three orders, joined with a table with two addresses, produces six rows instead of the expected three, unless the address join condition is additionally constrained. Such multiplication effects are a milder form of the same cartesian product from section six and often only becomes noticeable when totals in a report suddenly appear too high.

Aggregations after multiple joins must account for this multiplication. A SUM(o.amount) after a join with order_items counts the order total per line item instead of per order, unless it is deduplicated beforehand or the sum is formed at the correct level of aggregation. It often helps to pre-aggregate the amount in a subquery before the join, instead of summing it after the join.

8. Handling NULL values in join results correctly

Every outer join, whether left, right or full, produces NULL values in the columns of the table without a counterpart. These NULL values must be explicitly handled in further processing, otherwise comparisons behave unexpectedly. WHERE o.amount > 100 after a left join automatically filters out rows with NULL in amount, because any comparison with NULL in SQL evaluates to UNKNOWN instead of TRUE, which accidentally removes exactly the rows the left join was supposed to keep.

Functions like COALESCE(o.amount, 0) replace NULL with a defined default value before aggregations or comparisons take place, and IS NULL respectively IS NOT NULL are the only correct operators to explicitly test for NULL. Anyone who writes = NULL instead usually gets an empty result set without an error message, a pitfall that is frequently overlooked especially after a left join.

-- WRONG: filtering after a LEFT JOIN accidentally turns it into an INNER JOIN
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 100;
-- Lisa Wolf disappears again, NULL > 100 evaluates to UNKNOWN

-- RIGHT: move the condition into the join, or handle NULL explicitly
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.amount > 100;

-- RIGHT: default missing amounts to zero for aggregation
SELECT c.name, COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

9. Join types in direct comparison

The table below summarizes which rows each join type keeps in the result set when no matching counterpart exists. This overview does not replace a deep understanding of the individual cases, but it is a quick reference for choosing the right join type in a concrete query.

In practice, INNER JOIN and LEFT JOIN dominate everyday use by far, while RIGHT JOIN is rarely written manually and FULL JOIN stays limited to data quality and migration scenarios. Anyone who deliberately decides for every join whether unmatched rows should be kept or not avoids most of the error sources described above from the start.

10. Summary

The four join types inner, left, right and full differ only in how they handle rows without a counterpart. The inner join drops them on both sides, the left join keeps them on the left, the right join keeps them on the right, the full join keeps them on both sides. Anyone who internalizes this rule does not need to memorize the four variants but can derive them from the base logic and deliberately choose the right join type for each concrete question.

The biggest practical risks are not in the choice of join type itself, but in missing or incomplete join conditions that produce cartesian products, and in incorrect NULL handling after outer joins that accidentally turns a left join back into an inner join. Anyone who checks the row count after every join and deliberately formulates WHERE conditions on outer join columns avoids most of the mistakes that lead to wrong reports in practice.

11. FAQ: SQL Join Types Compared