SQL JOIN Types Explained: INNER, LEFT, RIGHT, and FULL

A JOIN combines rows from two tables based on a related column. INNER JOIN returns only rows with matches in both tables. LEFT JOIN returns all rows from the left table plus matching rows from the right (NULL where there's no match). RIGHT JOIN does the reverse. FULL OUTER JOIN returns all rows from both tables, matched where possible.

Common causes

• Relational databases normalize data across multiple tables, so retrieving a complete picture (e.g. orders with customer names) requires combining rows from related tables

How to fix it

• Use INNER JOIN when you only want rows that definitely have a match in both tables, like orders that have a valid customer
• Use LEFT JOIN when you want every row from the primary table even if there's no related row, like all customers whether or not they've placed an order
• MySQL doesn't support FULL OUTER JOIN directly — emulate it with a LEFT JOIN UNION a RIGHT JOIN (or UNION of LEFT JOIN and a NOT EXISTS RIGHT-only query)

Example

SELECT customers.name, orders.total
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id;

FAQ

What happens to unmatched rows in a LEFT JOIN?

They're still included in the result, with NULL values in every column that comes from the right-hand table.