Inner, left, right and full outer joins in SQL

1INNER JOIN: only the rows that matchRecommended

INNER JOIN returns one row for every pair of rows where the ON condition is true. A customer with two orders appears twice, a customer with no orders doesn't appear, and an order whose customer is missing doesn't appear either. Writing JOIN without a type means INNER JOIN in every major database; the keyword INNER is only there for readability.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);

INSERT INTO customers VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Cleo');
-- Cleo has no orders; order 104 points at customer 9, who doesn't exist
INSERT INTO orders VALUES (101, 1, 40), (102, 1, 25), (103, 2, 60), (104, 9, 15);

-- Only the rows that match on both sides
SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
ORDER BY o.id;

-- JOIN on its own means INNER JOIN: same rows
SELECT count(*) AS rows_join
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id;

Three rows come back: Ana's orders 101 and 102 and Ben's order 103. Cleo and order 104 are both missing, because neither has a partner on the other side. The bare JOIN counts the same 3 rows. Inner join is the right default when the data has to exist on both sides, such as an order line with its product; the result is also the same whichever table you write first.

2LEFT JOIN: every row from the first table

LEFT JOIN returns everything an inner join returns, plus each row of the left table (the one before the keyword) that matched nothing. The columns from the right table are NULL on those extra rows. LEFT OUTER JOIN is the same join written longhand. It is the join to reach for when the first table is the list you care about, for example every customer with their order count, including the ones at zero.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);

INSERT INTO customers VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Cleo');
-- Cleo has no orders; order 104 points at customer 9, who doesn't exist
INSERT INTO orders VALUES (101, 1, 40), (102, 1, 25), (103, 2, 60), (104, 9, 15);

-- Every customer, with their orders when they have any
SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

-- LEFT OUTER JOIN is the same join; OUTER is optional
SELECT c.name, count(o.id) AS orders, coalesce(sum(o.total), 0) AS spent
FROM customers AS c
LEFT OUTER JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY c.id;

The first query adds a fourth row, Cleo | NULL | NULL, to the inner join's three. Order 104 is still missing: a left join keeps unmatched rows from the left table only. In the summary, count(o.id) gives Cleo 0 because count(column) skips NULLs, whereas count(*) would count her one padded row as 1. sum of nothing is NULL, so coalesce(..., 0) turns her total into 0.

3RIGHT JOIN: every row from the second table

RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image: it keeps every row of the table after the keyword. Any right join can be rewritten as a left join by swapping the two tables, and most people do that, since queries read more easily when the main table comes first. SQLite has supported RIGHT and FULL joins since version 3.39 (2022).

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);

INSERT INTO customers VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Cleo');
-- Cleo has no orders; order 104 points at customer 9, who doesn't exist
INSERT INTO orders VALUES (101, 1, 40), (102, 1, 25), (103, 2, 60), (104, 9, 15);

-- Every order, with its customer when one exists
SELECT o.id AS order_id, o.total, c.name
FROM customers AS c
RIGHT JOIN orders AS o ON o.customer_id = c.id
ORDER BY o.id;

-- The same rows as a LEFT JOIN with the tables swapped
SELECT o.id AS order_id, o.total, c.name
FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;

Both queries print the same four rows. This time the orphan order 104 is kept with NULL for the name, and Cleo is gone, because she is on the side that isn't preserved. A row like 104 is usually a data problem; a foreign key (in SQLite, with PRAGMA foreign_keys = ON) would have refused to insert it.

4FULL OUTER JOIN: every row from both tables

FULL OUTER JOIN (or FULL JOIN) combines the two previous results: all the matches, the left rows with no partner and the right rows with no partner. It is the join for comparing two lists, such as reconciling a bank export against your own records. The second query below builds the same rows without FULL JOIN, which is how you write it in MySQL: the left join's rows, plus the right join's rows that found no customer.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);

INSERT INTO customers VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Cleo');
-- Cleo has no orders; order 104 points at customer 9, who doesn't exist
INSERT INTO orders VALUES (101, 1, 40), (102, 1, 25), (103, 2, 60), (104, 9, 15);

-- Every customer and every order, matched where possible
SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
FULL OUTER JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id IS NULL, c.id, o.id;

-- Without FULL JOIN (MySQL): the LEFT JOIN rows, plus the
-- RIGHT JOIN rows that found no customer
SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
UNION ALL
SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
RIGHT JOIN orders AS o ON o.customer_id = c.id
WHERE c.id IS NULL
ORDER BY order_id;

The full join returns five rows: the three matches, Cleo | NULL | NULL and NULL | 104 | 15. The emulation returns the same five; only the order differs, because a UNION can be sorted only by its output columns (sorting by name IS NULL there fails with 1st ORDER BY term does not match any column in the result set) and SQLite sorts NULL first. The WHERE c.id IS NULL on the second half matters: it keeps the matched rows from being listed twice. Shortening it to a plain UNION without that filter also works on this data, but UNION removes duplicate rows, so two genuinely identical rows would collapse into one. See UNION vs UNION ALL.

5ON vs WHERE: how a filter turns a LEFT JOIN into an inner join

For an inner join it makes no difference whether a condition sits in ON or WHERE. For an outer join it does. A condition in ON decides which rows count as a match, and the preserved table keeps all its rows anyway. A condition in WHERE runs after the join, on the padded rows too, and a comparison with NULL is never true, so those rows are filtered out.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);

INSERT INTO customers VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Cleo');
-- Cleo has no orders; order 104 points at customer 9, who doesn't exist
INSERT INTO orders VALUES (101, 1, 40), (102, 1, 25), (103, 2, 60), (104, 9, 15);

-- Condition in ON: every customer stays, only big orders join
SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.total >= 30
ORDER BY c.id;

-- Condition in WHERE: rows with NULL totals are filtered out,
-- so the LEFT JOIN acts like an INNER JOIN
SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.total >= 30
ORDER BY c.id;

With the condition in ON, order 102 (25) no longer matches, but Cleo is still listed with NULLs. With the same condition in WHERE, Cleo's row has total = NULL, NULL >= 30 isn't true, and she disappears: the result is what an inner join would return. Rule of thumb: conditions on the optional table go in ON; conditions on the preserved table, or a deliberate WHERE o.id IS NULL to keep only the unmatched rows, go in WHERE. That last pattern has its own page: find rows with no match.

6Which join do you need?

The row counts are from the customers and orders tables above: 3 matches, 1 customer with no orders, 1 order with no customer.

7Joins in PostgreSQL, MySQL and SQL Server

The join keywords are standard SQL, so the queries above run unchanged on PostgreSQL and SQL Server (Oracle supports the same keywords too; very old Oracle code uses the (+) operator instead). The exception is MySQL, which has no FULL JOIN. Worse, it doesn't always reject it: FROM customers FULL JOIN orders reads FULL as an alias for customers, warns Using FULL as unquoted identifier is deprecated and then fails with Unknown column 'customers.name'. With aliases (customers AS c FULL OUTER JOIN) it is a plain syntax error.