SQL Joins Explained: INNER, LEFT, RIGHT, FULL, CROSS

SQL joins combine rows from two or more tables based on a related column. They are one of the most powerful features of relational databases — and one of the most commonly misunderstood.

This guide covers every join type with diagrams, real examples in PostgreSQL/MySQL/SQLite syntax, and common mistakes to avoid.

Quick Reference

Join Type	Returns
`INNER JOIN`	Only rows that match in both tables
`LEFT JOIN`	All rows from left table + matching rows from right (NULLs for no match)
`RIGHT JOIN`	All rows from right table + matching rows from left (NULLs for no match)
`FULL OUTER JOIN`	All rows from both tables (NULLs where there is no match)
`CROSS JOIN`	Every combination of rows (Cartesian product)
`SELF JOIN`	A table joined with itself

Setup: Sample Tables

These two tables are used in every example below.

-- orders table
CREATE TABLE orders (
  id         INT PRIMARY KEY,
  customer_id INT,
  amount     DECIMAL(10,2)
);

INSERT INTO orders VALUES
  (1, 1, 100.00),
  (2, 2,  50.00),
  (3, 3,  75.00),
  (4, 5, 200.00);   -- customer 5 has no matching customer row

-- customers table
CREATE TABLE customers (
  id   INT PRIMARY KEY,
  name TEXT
);

INSERT INTO customers VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Carol'),
  (4, 'Dan');        -- Dan has no orders

INNER JOIN

Returns only rows where the join condition matches in both tables. Non-matching rows from either table are excluded.

orders:       customers:          INNER JOIN result:
+----+----+  +----+-------+      +----+-------+--------+
| id | cid|  | id | name  |  →   | id | name  | amount |
+----+----+  +----+-------+      +----+-------+--------+
|  1 |  1 |  |  1 | Alice |      |  1 | Alice | 100.00 |
|  2 |  2 |  |  2 | Bob   |      |  2 | Bob   |  50.00 |
|  3 |  3 |  |  3 | Carol |      |  3 | Carol |  75.00 |
|  4 |  5 |  |  4 | Dan   |      (order 4 excluded — no cust 5)
                               (Dan excluded — no orders)

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

 order_id | customer_name | amount
----------+---------------+--------
        1 | Alice         | 100.00
        2 | Bob           |  50.00
        3 | Carol         |  75.00

JOIN without a keyword defaults to INNER JOIN in all major databases.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table. Rows from the right table are included if they match; otherwise the right-side columns are NULL.

Use LEFT JOIN when you want every row from the "main" table regardless of whether a match exists.

LEFT JOIN result:
+----+-------+--------+
| id | name  | amount |
+----+-------+--------+
|  1 | Alice | 100.00 |
|  2 | Bob   |  50.00 |
|  3 | Carol |  75.00 |
|  4 | Dan   | NULL   |  ← Dan included, no matching order
(order 4 excluded — no customer 5)

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

Find customers with NO orders

Filter for NULL on the right side to get unmatched rows:

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
-- returns: Dan

RIGHT JOIN (RIGHT OUTER JOIN)

Mirror image of LEFT JOIN. Returns all rows from the right table, with NULL for unmatched left-side columns.

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

 name  | order_id | amount
-------+----------+--------
 Alice |        1 | 100.00
 Bob   |        2 |  50.00
 Carol |        3 |  75.00
 NULL  |        4 | 200.00  ← order 4 included, no customer 5

Tip: RIGHT JOIN is rarely used in practice. You can always rewrite it as a LEFT JOIN by swapping the table order — which is usually clearer.

-- Equivalent to the RIGHT JOIN above:
SELECT c.name, o.id AS order_id, o.amount
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id;

FULL OUTER JOIN (FULL JOIN)

Returns all rows from both tables. Where there is no match, NULL fills in the missing side.

FULL OUTER JOIN result:
 name  | order_id | amount
-------+----------+--------
 Alice |        1 | 100.00
 Bob   |        2 |  50.00
 Carol |        3 |  75.00
 Dan   | NULL     | NULL    ← no order
 NULL  |        4 | 200.00  ← no customer

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

MySQL note: MySQL does not support FULL OUTER JOIN. Emulate it with a UNION of LEFT JOIN and RIGHT JOIN:

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

UNION

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

CROSS JOIN

Returns the Cartesian product: every row from the left table paired with every row from the right table.

3 customers × 3 sizes = 9 combinations

SELECT c.name, s.label AS shirt_size
FROM customers c
CROSS JOIN (VALUES ('S'), ('M'), ('L')) AS s(label);

 name  | shirt_size
-------+-----------
 Alice | S
 Alice | M
 Alice | L
 Bob   | S
 ...

Use cases:

Generating test data
Building a calendar (days × hours)
Pricing matrices (products × regions)

SELF JOIN

A table joined to itself. Useful for hierarchical or comparative data stored in a single table.

-- employees table with manager_id referencing the same table
CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       TEXT,
  manager_id INT REFERENCES employees(id)
);

-- Find each employee and their manager's name:
SELECT
  e.name         AS employee,
  m.name         AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Joining More Than Two Tables

Chain multiple joins in sequence:

SELECT
  o.id            AS order_id,
  c.name          AS customer,
  p.name          AS product,
  oi.quantity,
  oi.unit_price
FROM orders o
INNER JOIN customers  c  ON c.id = o.customer_id
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products   p  ON p.id = oi.product_id
WHERE o.created_at >= '2024-01-01';

Performance Tips

1. Index the join column on both sides

-- Both sides should be indexed:
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- customers.id is already the primary key (indexed automatically)

Unindexed joins cause full table scans, which are slow on large tables.

2. Filter early

Push WHERE conditions as close to the data source as possible so the join operates on fewer rows:

-- Better: filter before joining
SELECT c.name, o.amount
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.amount > 100;               -- filter applied before sort/group

-- Avoid filtering after joining millions of rows unnecessarily

3. Use EXPLAIN / EXPLAIN ANALYZE

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

Look for Seq Scan on large tables — that's a sign an index is missing.

4. Be careful with DISTINCT after joins

If you get duplicate rows after a join, resist the urge to slap on DISTINCT. Instead, diagnose why the join produces duplicates:

Is there a one-to-many relationship you didn't account for?
Do you need GROUP BY instead?
Should you use a subquery or CTE?

Join vs Subquery vs EXISTS

All three can express "rows that have a related record":

-- JOIN: fetch data from both tables
SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

-- Subquery: check for existence without needing order columns
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- EXISTS: same as above, often more efficient on large datasets
SELECT name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Rule of thumb:

Use JOIN when you need columns from both tables.
Use EXISTS / IN when you only need rows from one table and are just checking for a match.

Common Mistakes

Mistake	Problem	Fix
`WHERE` instead of `ON` for join condition	Turns `LEFT JOIN` into `INNER JOIN` because `NULL` rows are filtered out	Move the condition to `ON`
Joining on a non-indexed column	Full table scan on every join	Add an index on the join column
Missing join condition	Produces unintentional `CROSS JOIN` (huge result set)	Always specify `ON`
Filtering right-side columns in `WHERE` after `LEFT JOIN`	Removes NULLs, converting to INNER JOIN	Filter in `ON` clause or use `IS NULL` intentionally
Ambiguous column names	Query error when both tables have a column with the same name	Always alias tables and qualify column names (`o.id`, `c.id`)
Joining on columns with different data types	Implicit cast kills index usage	Ensure join columns have the same type

FAQ

What is the difference between INNER JOIN and JOIN?
They are identical. JOIN is shorthand for INNER JOIN in all major SQL databases (PostgreSQL, MySQL, SQLite, SQL Server, Oracle).

When should I use LEFT JOIN instead of INNER JOIN?
Use LEFT JOIN when you want to keep rows from the left table even if there's no matching row in the right table. If you only want rows that exist in both tables, use INNER JOIN.

Can I join on multiple columns?
Yes. Add conditions with AND:

JOIN orders o ON o.customer_id = c.id AND o.region = c.region

What is the difference between ON and WHERE in a join?
ON specifies the join condition and is evaluated during the join. WHERE filters the result after the join. For INNER JOIN they produce the same result, but for LEFT/RIGHT/FULL JOINs, moving a filter from ON to WHERE can change semantics (it excludes NULL rows).

Why do I get duplicate rows after joining?
Usually because of a one-to-many relationship: one row in the left table matches multiple rows in the right table. Use GROUP BY and aggregate functions, or a subquery, to collapse duplicates intentionally.

Does FULL OUTER JOIN work in MySQL?
No. MySQL doesn't support FULL OUTER JOIN. Use LEFT JOIN UNION RIGHT JOIN as shown in the FULL OUTER JOIN section above.

What is a non-equi join?
A join where the condition uses something other than = — for example >=, <, or BETWEEN. Common for date ranges:

-- Find the price tier that was active when an order was placed:
FROM orders o
JOIN price_tiers pt ON o.created_at BETWEEN pt.valid_from AND pt.valid_to