SQL JOINs Explained: INNER, LEFT, RIGHT, and FULL with Real Examples

The example tables

All examples use two tables: orders and customers. Notice that customer cus_4 exists in the customers table but has no orders, and orderord_5 references customer cus_99 who does not exist in the customers table (a foreign key violation that sometimes happens in real databases).

-- customers
id      name
------  -------
cus_1   Alice
cus_2   Bob
cus_3   Carol
cus_4   Dan        -- no orders

-- orders
id      customer_id   amount
------  -----------   ------
ord_1   cus_1         120
ord_2   cus_1         85
ord_3   cus_2         200
ord_4   cus_3         45
ord_5   cus_99        60    -- orphaned row (customer does not exist)

INNER JOIN: only rows with a match on both sides

INNER JOIN returns rows where the join condition is satisfied in both tables. Rows with no match on either side are excluded. In the example, Dan (no orders) and the orphaned order (unknown customer) both disappear from the result.

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

-- Result
order_id  customer  amount
--------  --------  ------
ord_1     Alice     120
ord_2     Alice     85
ord_3     Bob       200
ord_4     Carol     45
-- ord_5 excluded (cus_99 not in customers)
-- Dan excluded (no orders)

INNER JOIN is the correct choice when you need data from both tables and rows without a match are genuinely irrelevant to the query. "Show me all orders with the customer name" is an INNER JOIN — an order without a known customer is not useful output.

LEFT JOIN: all rows from the left table, NULLs where no match

LEFT JOIN (also written LEFT OUTER JOIN) returns all rows from the left table regardless of whether a match exists in the right table. Rows with no match get NULL for all columns from the right table. The orphaned order appears with NULLs for customer columns.

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

-- Result
order_id  customer  amount
--------  --------  ------
ord_1     Alice     120
ord_2     Alice     85
ord_3     Bob       200
ord_4     Carol     45
ord_5     NULL      60    -- orphaned order appears with NULL customer
-- Dan still excluded (he is in the right table, not the left)

LEFT JOIN is the most commonly used JOIN after INNER. It is the correct choice when the left table contains the entities you always want to see, and the right table contains optional related data. "Show me all orders, and if we know the customer name, include it" is a LEFT JOIN.

A useful pattern: to find rows in the left table that have no match in the right table (anti-join), add WHERE right_table.id IS NULL to a LEFT JOIN. This is typically more efficient than a NOT EXISTS subquery on large tables.

-- Find orphaned orders (orders with no matching customer)
SELECT o.id, o.amount
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

-- Result: ord_5 (60)

RIGHT JOIN: all rows from the right table

RIGHT JOIN is the mirror of LEFT JOIN — it returns all rows from the right table, with NULLs for left table columns where there is no match. Dan (customer with no orders) now appears with NULLs for order columns.

SELECT
  o.id         AS order_id,
  c.name       AS customer,
  o.amount
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;

-- Result
order_id  customer  amount
--------  --------  ------
ord_1     Alice     120
ord_2     Alice     85
ord_3     Bob       200
ord_4     Carol     45
NULL      Dan       NULL   -- Dan has no orders
-- ord_5 excluded (cus_99 not in customers)

RIGHT JOIN is rarely used in practice because the same result is always achievable by swapping the tables in a LEFT JOIN. Most style guides recommend always using LEFT JOIN and swapping the table order rather than mixing LEFT and RIGHT JOINs in the same codebase, since it is easier to read when you always know that "the preserved side" is the left.

FULL OUTER JOIN: all rows from both tables

FULL OUTER JOIN returns all rows from both tables. Rows with no match on either side appear with NULLs for the other table's columns. Both Dan and the orphaned order appear.

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

-- Result
order_id  customer  amount
--------  --------  ------
ord_1     Alice     120
ord_2     Alice     85
ord_3     Bob       200
ord_4     Carol     45
ord_5     NULL      60     -- orphaned order
NULL      Dan       NULL   -- customer with no orders

FULL OUTER JOIN is useful for data reconciliation — comparing two tables to find rows that exist in one but not the other, or finding discrepancies between a source and a destination after a data migration. It is less common in application queries.

Note: MySQL does not support FULL OUTER JOIN natively. To get the same result in MySQL, combine a LEFT JOIN and a RIGHT JOIN with UNION.

Common JOIN mistakes and how to fix them

Row multiplication from one-to-many joins. If you join orders to order_items, and each order has multiple items, each order row is duplicated for every matching item. Aggregates applied to the joined result without grouping will sum or count duplicated values. Always check the cardinality of the join relationship before writing aggregates.

Filtering in WHERE instead of ON for LEFT JOINs. If you add a filter on the right table's columns in the WHERE clause of a LEFT JOIN, you convert it to an effective INNER JOIN — rows that matched only with NULLs are then eliminated by the WHERE filter. Put conditions on the right table in the ON clause, not WHERE, to preserve the LEFT JOIN behavior.

-- Wrong: this silently becomes an INNER JOIN
SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'IT';   -- eliminates NULL rows

-- Right: filter on the right table goes in ON
SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c
  ON o.customer_id = c.id
  AND c.country = 'IT';   -- NULL rows are preserved

Joining on nullable columns. If the join column can be NULL in either table, rows where the column is NULL will never match — because NULL = NULL evaluates to NULL (not TRUE) in SQL. This can cause silent data loss. Use IS NOT DISTINCT FROM(PostgreSQL/SQL standard) or COALESCE if you need NULL-safe equality.

Missing indexes on join columns. A JOIN on an unindexed column performs a full table scan on every join. For large tables, this is the most common cause of slow queries. Every foreign key should have an index, and every column that appears in a JOIN condition should be indexed if the table has more than a few thousand rows.