What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only matching rows from both tables; LEFT JOIN returns all rows from the left table plus any matches from the right.

SQLPostgreSQL

Overview

JOINs combine rows from two tables based on a related column. The type of JOIN determines what happens when a row in one table has no matching row in the other.

Key Points

•INNER JOIN: only rows with a match in BOTH tables appear in the result.
•LEFT JOIN (LEFT OUTER JOIN): all rows from the left table, with NULL for right-side columns when there is no match.
•RIGHT JOIN: mirror of LEFT JOIN all rows from the right table.
•FULL OUTER JOIN: all rows from both tables, NULLs where there is no match.
•Use LEFT JOIN when you want to keep all records from the main table even if the related data doesn't exist.

Examples

INNER JOIN: only matched rows

sql

SELECT orders.id, customers.name
FROM   orders
INNER JOIN customers ON orders.customer_id = customers.id;
-- Only returns orders that have a matching customer
-- Orders with a NULL or missing customer_id are excluded

LEFT JOIN: keep all orders, even without a customer

sql

SELECT orders.id, customers.name
FROM   orders
LEFT JOIN customers ON orders.customer_id = customers.id;
-- Returns ALL orders
-- customer.name is NULL for orders with no matching customer

Find records with no match (anti-join pattern)

sql

-- Find orders with no matching customer
SELECT orders.id
FROM   orders
LEFT JOIN customers ON orders.customer_id = customers.id
WHERE  customers.id IS NULL;

The WHERE IS NULL filter on the right table's column is the classic anti-join pattern.

Pro Tip

When in doubt, draw a Venn diagram: INNER = intersection; LEFT = left circle; FULL OUTER = both circles. The most common JOINs in production code are INNER JOIN and LEFT JOIN.