Example tables

Assume customers(id, name) and orders(id, customer_id, total). The orders.customer_id column refers to customers.id.

INNER JOIN

SELECT c.name, o.total

FROM customers AS c

INNER JOIN orders AS o ON o.customer_id = c.id;

INNER JOIN returns rows where the join condition matches in both tables.

LEFT JOIN

SELECT c.name, o.total

FROM customers AS c

LEFT JOIN orders AS o ON o.customer_id = c.id;

LEFT JOIN keeps every customer. Customers without orders have NULL values in the order columns.

Find customers without orders

SELECT c.id, c.name

FROM customers AS c

LEFT JOIN orders AS o ON o.customer_id = c.id

WHERE o.id IS NULL;

This anti-join pattern finds records without a related row.

Performance notes

Index join columns when justified by query patterns. Use EXPLAIN, select only needed columns and consider the write and storage cost of every index.