SQL Joins Explained — INNER, LEFT, RIGHT, FULL OUTER JOIN [2026]

Q: What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only the rows where there is a match in BOTH tables. If a row in the left table has no matching row in the right table, it is excluded. LEFT JOIN (LEFT OUTER JOIN) returns ALL rows from the left table, plus matched rows from the right table. Where there is no match in the right table, NULL values are returned for the right table columns. Use LEFT JOIN when you want all records from the primary table regardless of whether they have related records.

Q: Why would a LEFT JOIN return NULL values?

NULL appears in LEFT JOIN results for right-table columns when there is no matching row. For example: SELECT customers.name, orders.id FROM customers LEFT JOIN orders ON customers.id = orders.customer_id — customers who have never placed an order will appear with NULL in the orders.id column. This is the key use case for LEFT JOIN: finding records that DO NOT have a match (WHERE orders.id IS NULL gives you customers with no orders).

Q: What is FULL OUTER JOIN and how is it different from UNION?

FULL OUTER JOIN returns all rows from BOTH tables, with NULLs where there is no match on either side. It combines LEFT JOIN and RIGHT JOIN. UNION combines the result sets of two SELECT queries by stacking rows (not columns). FULL OUTER JOIN joins side-by-side on a key; UNION stacks results vertically. MySQL does not support FULL OUTER JOIN natively — simulate it with LEFT JOIN UNION RIGHT JOIN.

Q: Do JOINs hurt query performance?

JOINs can be slow when joining large tables without indexes on the join columns. Always index the columns in ON conditions and foreign key columns. The query planner chooses a join algorithm (Nested Loop, Hash Join, Merge Join) based on table size and available indexes. Multiple JOINs compound: a query joining 5 large tables without indexes can be extremely slow. Use EXPLAIN to see the join strategy and look for sequential scans that should be index scans.

Q: What is a CROSS JOIN used for?

A CROSS JOIN returns the Cartesian product — every combination of rows from both tables. If table A has 100 rows and table B has 50 rows, the result has 5,000 rows. Use cases: generating all possible combinations (e.g. all colour/size combinations for a product), populating a calendar table by crossing dates with hours, or generating test data. CROSS JOIN has no ON condition. Be careful with large tables — a CROSS JOIN on two million-row tables produces a trillion rows.

Q: When should I use a JOIN vs a subquery?

JOINs are generally faster than correlated subqueries because the query planner can optimise JOIN execution. Use JOINs when combining columns from multiple tables in SELECT results. Use subqueries when filtering based on aggregate or existence: WHERE EXISTS (subquery) or WHERE id IN (subquery). For checking existence, EXISTS is often faster than IN for large subqueries. Modern query planners often rewrite subqueries as JOINs anyway — but explicit JOINs are clearer and more predictable.

Sample Data Used Throughout This Guide

INNER JOIN

INNER JOIN returns rows where there is a match in BOTH tables. Non-matching rows from either table are excluded.

Customers: [Alice] [Bob] [Charlie] [Diana] Orders: [101] [102] [103] [104] INNER JOIN: Only overlapping rows — Diana excluded, order 104 excluded

INNER JOIN SELECT c.name, o.id AS order_id, o.amount FROM customers c INNER JOIN orders o ON c.id = o.customer_id; -- Result: name | order_id | amount ------+----------+------- Alice | 101 | 500 Alice | 102 | 300 Bob | 103 | 700 -- Charlie: no orders → excluded -- Diana: no orders → excluded -- Order 104: customer_id 5 not in customers → excluded

INNER JOIN is the Default

Writing just "JOIN" in SQL means INNER JOIN. They are identical. Most developers write "JOIN" for brevity; "INNER JOIN" is more explicit and readable in complex queries.

LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN returns ALL rows from the left table, plus matched rows from the right table. Where there is no match, right table columns are NULL.

Customers: [Alice] [Bob] [Charlie] [Diana] Orders: [101] [102] [103] [104] LEFT JOIN: ALL customers + matched orders; Diana gets NULL order columns

RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN returns ALL rows from the right table, plus matched rows from the left table. Where there is no match, left table columns are NULL. Right JOIN is the mirror of LEFT JOIN.

RIGHT JOIN SELECT c.name, o.id AS order_id, o.amount FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id; -- Result: name | order_id | amount ------+----------+------- Alice | 101 | 500 Alice | 102 | 300 Bob | 103 | 700 NULL | 104 | 200 ← order 104 has no matching customer — NULL for name

Prefer LEFT JOIN over RIGHT JOIN

RIGHT JOIN is the least commonly used JOIN. Most SQL developers prefer to rewrite RIGHT JOINs as LEFT JOINs by swapping the table order — it is easier to read and reason about when the "primary" table is always on the left.

FULL OUTER JOIN

FULL OUTER JOIN returns ALL rows from BOTH tables. Where there is no match on either side, NULLs fill the missing columns.

CROSS JOIN

CROSS JOIN returns the Cartesian product — every combination of rows from both tables. No ON condition.

CROSS JOIN — generate combinations -- All colour × size combinations for a product SELECT colours.name, sizes.label FROM colours CROSS JOIN sizes; -- colours: Red, Blue, Green (3 rows) -- sizes: S, M, L, XL (4 rows) -- Result: 12 rows (all 3×4 combinations) -- Practical use: populate a time-series reporting table SELECT d.date, h.hour FROM dates d CROSS JOIN hours h;

SELF JOIN

A SELF JOIN joins a table to itself. Used for hierarchical or comparative data within the same table.

SELF JOIN — employee/manager hierarchy -- employees table has manager_id pointing to another employee row SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id; -- employee | manager -- Alice | Bob (Alice reports to Bob) -- Bob | NULL (Bob has no manager — CEO) -- Charlie | Alice

JOIN Types Summary

JOIN Type	Returns	NULLs for	Common Use Case
INNER JOIN	Matching rows only	None	Get related data from both tables
LEFT JOIN	All left + matched right	Unmatched right rows	All records + optional related data
RIGHT JOIN	All right + matched left	Unmatched left rows	Rarely used — rewrite as LEFT JOIN
FULL OUTER JOIN	All rows from both	Unmatched from either side	Find unmatched records on either side
CROSS JOIN	Cartesian product	None	Generate all combinations
SELF JOIN	Table joined to itself	Depends on JOIN type	Hierarchies, comparisons within one table

Always Index Your JOIN Columns

The most common performance problem with JOINs is missing indexes on the ON condition columns. Always index foreign key columns and any column used in a JOIN condition. Run EXPLAIN before and after adding indexes to verify the query plan changes from Seq Scan to Index Scan.

How We Research and Update This Guide

We test the underlying formula or workflow, compare outputs with reliable references, and revise examples whenever the page content changes.

The workflow or formula is tested directly in the tool and compared against independent reference examples.
Examples are kept practical so readers can verify the result without hidden assumptions.
Pages are revised whenever the interface, calculation flow, or surrounding guidance materially changes.

Frequently Asked Questions — SQL JOINs

What is the difference between INNER JOIN and LEFT JOIN?

Why would a LEFT JOIN return NULL values?

What is FULL OUTER JOIN and how is it different from UNION?

Do JOINs hurt query performance?

What is a CROSS JOIN used for?

When should I use a JOIN vs a subquery?