SQL JOIN Tutorial with Examples: INNER, LEFT, RIGHT, and FULL OUTER

Overview

SQL JOINs combine rows from two or more tables based on a related column. This tutorial uses a small sample dataset to demonstrate every major join type, with expected output for each query.

Sample Tables

We will use two tables: customers and orders.

-- customers
+----+----------+
| id | name     |
+----+----------+
| 1  | Alice    |
| 2  | Bob      |
| 3  | Charlie  |
+----+----------+

-- orders
+----+-------------+--------+
| id | customer_id | amount |
+----+-------------+--------+
| 1  | 1           | 100    |
| 2  | 1           | 200    |
| 3  | 2           | 150    |
+----+-------------+--------+

INNER JOIN

Returns only rows where the join condition matches in both tables.

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

Result: Alice/100, Alice/200, Bob/150. Charlie has no orders, so he is excluded.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matching rows from the right table. Non-matching right rows produce NULL.

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

Result: Alice/100, Alice/200, Bob/150, Charlie/NULL.

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror of LEFT JOIN: all rows from the right table, plus matches from the left.

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

Result: Alice/100, Alice/200, Bob/150. Same as INNER JOIN here because every order has a matching customer.

FULL OUTER JOIN

Returns all rows from both tables, filling with NULL where there is no match.

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

Result: Alice/100, Alice/200, Bob/150, Charlie/NULL. If there were an order with no matching customer, it would also appear.

Join Type Comparison

CROSS JOIN

Produces the Cartesian product of both tables. Every row in the left table is paired with every row in the right table.

SELECT c.name, o.id
FROM customers c
CROSS JOIN orders o;

With 3 customers and 3 orders, this returns 9 rows. Use this sparingly; it can explode quickly on large tables.

Joining More Than Two Tables

SELECT c.name, o.amount, p.product_name
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN products p ON o.product_id = p.id;

Common Mistakes

• Forgetting the ON clause — produces a cross join unintentionally.
• Using WHERE instead of ON for join conditions — semantically different for outer joins.
• Ambiguous column names — always qualify columns with table aliases when joining.
• NULL comparison — NULL = NULL evaluates to unknown. Use IS NULL.