Joins are how relational databases earn the “relational” name — combining rows from multiple tables based on a related column. Picking the wrong join type is a classic source of silently missing (or duplicated) data.
-- INNER JOIN: only rows with a match in both tables SELECT o.id, c.name FROM orders o INNER JOIN customers c ON o.customer_id = c.id; -- LEFT JOIN: all rows from orders, matched customer data or NULL SELECT o.id, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.id; -- RIGHT JOIN: all rows from customers, matched orders or NULL SELECT o.id, c.name FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id; -- Finding orphaned rows: customers who never ordered SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;
MySQL doesn’t support FULL OUTER JOIN directly — the common workaround is a LEFT JOIN UNION RIGHT JOIN, or increasingly, just a LEFT JOIN plus a separate query for the opposite direction if you truly need both.