SQL Joins Explained: INNER, LEFT, RIGHT, FULL (With Examples)

The Setup: Two Tables

All examples in this guide use the same two tables. A users table and an orders table. Not every user has placed an order, and (in a flawed data scenario) some orders may reference deleted users.

-- users table
| id | name    |
|----|---------|
|  1 | Alice   |
|  2 | Bob     |
|  3 | Carol   |
|  4 | Dave    |

-- orders table
| id | user_id | amount |
|----|---------|--------|
|  1 |       1 | 99.00  |
|  2 |       1 | 49.00  |
|  3 |       2 | 29.00  |
|  4 |       9 | 15.00  |  -- user_id 9 does not exist

User 3 (Carol) and user 4 (Dave) have no orders. Order 4 references user_id = 9, which does not exist. This deliberately messy data illustrates how each join type handles missing matches.

INNER JOIN: Only Matching Rows

An INNER JOIN returns only rows where the join condition is true in both tables. Non-matching rows from either side are discarded.

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Result:
| name  | amount |
|-------|--------|
| Alice |  99.00 |
| Alice |  49.00 |
| Bob   |  29.00 |

Note what is missing: Carol and Dave (no orders), and the orphaned order for user_id = 9. INNER JOIN is the most restrictive - it only shows you the intersection of both datasets.

When to use it: When you only want records with a match on both sides. This is the most common join type. "Show me all orders with customer information" is an INNER JOIN.

LEFT JOIN: All Left Rows, Matching Right

A LEFT JOIN (also written LEFT OUTER JOIN) returns all rows from the left table plus any matching rows from the right table. Where there is no match on the right, columns from the right table are NULL.

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Result:
| name  | amount |
|-------|--------|
| Alice |  99.00 |
| Alice |  49.00 |
| Bob   |  29.00 |
| Carol |   NULL |  -- no orders
| Dave  |   NULL |  -- no orders

When to use it: When you want all records from the left table regardless of whether there is a match. "Show me all users and their orders (if any)" is a LEFT JOIN. This is the second most common join type.

A powerful variant: filtering on a NULL right-side column after a LEFT JOIN gives you rows that have no match. This is the standard pattern for "find records with no related records":

-- Find users who have never placed an order
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- Result:
| name  |
|-------|
| Carol |
| Dave  |

RIGHT JOIN: All Right Rows, Matching Left

A RIGHT JOIN is the mirror image of LEFT JOIN: it returns all rows from the right table plus matching rows from the left. Where there is no match on the left, left-table columns are NULL.

SELECT u.name, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Result:
| name  | amount |
|-------|--------|
| Alice |  99.00 |
| Alice |  49.00 |
| Bob   |  29.00 |
|  NULL |  15.00 |  -- order with no matching user

When to use it: In practice, RIGHT JOIN is rarely written directly. Most developers rewrite it as a LEFT JOIN by swapping the table order (which is semantically equivalent and easier to read). The query above is equivalent to:

SELECT u.name, o.amount
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;

A good rule of thumb: always use LEFT JOIN, never RIGHT JOIN. Swap the table order instead. Your queries will be much easier to read and reason about.

FULL OUTER JOIN: All Rows from Both Tables

A FULL OUTER JOIN returns all rows from both tables. Where there is no match, the missing side's columns are NULL. Think of it as LEFT JOIN + RIGHT JOIN combined.

SELECT u.name, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- Result:
| name  | amount |
|-------|--------|
| Alice |  99.00 |
| Alice |  49.00 |
| Bob   |  29.00 |
| Carol |   NULL |
| Dave  |   NULL |
|  NULL |  15.00 |

Note: MySQL does not support FULL OUTER JOIN natively. Emulate it with UNION:

-- MySQL: emulate FULL OUTER JOIN
SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.amount FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

When to use it: Data reconciliation, finding mismatches between two datasets, auditing referential integrity violations.

CROSS JOIN: The Cartesian Product

A CROSS JOIN returns every possible combination of rows from the two tables. If the left table has M rows and the right table has N rows, the result has M × N rows. There is no ON condition.

-- Generate all combinations of colors and sizes
SELECT colors.name, sizes.label
FROM colors
CROSS JOIN sizes;

-- If colors has 3 rows and sizes has 4 rows: 12 result rows

When to use it: Generating all combinations for reports, test data generation, or populating a matrix. Rarely used in application queries. Be careful: a CROSS JOIN of two large tables will produce a massive result set and can crash your database.

SELF JOIN: A Table Joined to Itself

A SELF JOIN is not a separate keyword - it is just joining a table to itself using a table alias. It is used for hierarchical data (employees and managers, categories and subcategories) or comparing rows within the same table.

-- employees table: id, name, manager_id (references employees.id)
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Result:
| employee | manager  |
|----------|----------|
| Alice    | NULL     |  -- Alice is the CEO, no manager
| Bob      | Alice    |
| Carol    | Alice    |
| Dave     | Bob      |

JOIN Syntax Quick Reference

Joining Multiple Tables

Real queries often join three or more tables. Chain JOIN clauses sequentially. The result of each join feeds into the next:

SELECT
  u.name         AS customer,
  o.id           AS order_id,
  p.name         AS product,
  oi.quantity,
  oi.unit_price
FROM users u
INNER JOIN orders o       ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p     ON oi.product_id = p.id
WHERE o.created_at >= '2026-01-01'
ORDER BY o.id, p.name;

Use table aliases (u, o, oi, p) consistently. They make queries readable and are required when both tables have a column with the same name.

Performance: Indexes and Join Order

Joins can be slow without proper indexing. Key rules:

• Index foreign key columns. Every column used in a ON clause should have an index. In the examples above, orders.user_id should be indexed.
• Index columns used in WHERE after a join. If you filter on o.created_at, that column needs an index too.
• Avoid functions on join columns. ON YEAR(u.created_at) = 2026 prevents index use. Rewrite to use a range condition on the bare column.
• The query optimizer determines join order. Most modern databases (PostgreSQL, MySQL 8, SQL Server) use a cost-based optimizer that determines the most efficient join order. You rarely need to specify it manually, but EXPLAIN ANALYZE shows you what the planner chose.

-- Check the query execution plan
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

FAQ

What is the difference between INNER JOIN and WHERE with multiple tables?

In older SQL syntax (pre-SQL-92), joins were written as: SELECT * FROM users, orders WHERE users.id = orders.user_id. This is functionally equivalent to an INNER JOIN. The explicit JOIN ... ON syntax introduced in SQL-92 is strongly preferred because it separates join conditions from filter conditions, is easier to read, and is less likely to accidentally produce a cartesian product if you forget a WHERE clause.

When should I use LEFT JOIN vs INNER JOIN?

Use INNER JOIN when you only want rows that have a match in both tables and non-matching rows are irrelevant. Use LEFT JOIN when you need all rows from the left table regardless of whether a match exists on the right. A common mistake is using INNER JOIN and wondering why some records disappeared - the answer is almost always that there was no matching row in the joined table.

Does the order of tables matter in a JOIN?

For INNER JOIN and FULL OUTER JOIN, the result is the same regardless of table order (they are commutative). For LEFT JOIN and RIGHT JOIN, table order matters: the "left" or "right" designation determines which table's unmatched rows are preserved. Swapping the table order in a LEFT JOIN produces the same result as a RIGHT JOIN with the original order.

What causes a JOIN to produce duplicate rows?

Duplicates appear when the join key is not unique in the joined table. If one user has three orders and you join users to orders, each user row is repeated for each of their orders. This is expected and correct behavior. If you see unexpected duplicates, check whether your join condition is too loose (joining on a non-unique column), whether you have duplicates in the source data, or whether you have multiple join conditions that all produce matches.

How do I join tables without a common key?

You can join on any condition, not just equality. Range joins (ON a.start_date <= b.date AND b.date <= a.end_date), inequality joins, and multi-column joins are all valid SQL. Range joins are common for time-series data and pricing rules. Be aware that non-equality joins cannot use standard B-tree indexes as efficiently and can be slower on large datasets.

Format and clean up complex SQL queries before running them in production: Use our free SQL Formatter here →