Explain INNER, LEFT and FULL OUTER JOIN

A join combines rows from two tables based on a condition. The join type decides what happens to rows without a match.

• INNER JOIN returns only rows that match on both sides.
• LEFT JOIN returns all rows from the left table. When the right side has no match, its columns are NULL.
• FULL OUTER JOIN returns all rows from both tables, matched where possible and NULL-filled where not.

-- customers with their orders; customers with no orders show NULL
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- customers that have never ordered
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

A filter on the right table in the WHERE clause can turn a LEFT JOIN into an INNER JOIN, so put such conditions in ON. Some databases, such as MySQL, do not support FULL OUTER JOIN directly.