MySQL Joins

Why Joins Exist

Relational databases split data into separate tables to avoid repeating information - a customers table holds each customer once, and an orders table holds each order once, referring back to the customer by id rather than repeating the customer's full name and address on every order row. This is normalization, and it keeps data consistent, but it means the information you actually want - 'show me each order with the customer's name' - lives across two tables. A join is the operation that stitches those tables back together at query time, using a shared column, usually a foreign key, to line up matching rows.

The Basic Shape of a Join

SELECT orders.order_id, customers.name, orders.total_amount
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

The ON clause is the heart of the join - it tells MySQL exactly which column in each table should be compared to decide whether two rows belong together. Get the ON condition wrong, and you either lose rows you wanted or gain rows you didn't; the FROM and JOIN keywords just say which tables are involved.

The Join Types

• INNER JOIN returns only rows that have a match in both tables - a customer with no orders, or an order with a missing customer_id, is left out entirely.
• LEFT JOIN returns every row from the left table, filling in NULLs for any columns from the right table when there's no match.
• RIGHT JOIN is the mirror image of LEFT JOIN - every row from the right table is kept, with NULLs filling in for the left table.
• A SELF JOIN isn't a different keyword - it's the same table joined to itself under two aliases, used for hierarchical or peer relationships like 'employee reports to manager'.
• MySQL has no FULL OUTER JOIN keyword, but the same result - unmatched rows from either side - can be built by combining a LEFT JOIN and a RIGHT JOIN with UNION.

SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Table Aliases Keep Joins Readable

Once a query joins two or more tables, typing the full table name in front of every column gets noisy fast, and it's required whenever both tables share a column name like id. Aliasing each table with a short letter or two - o for orders, c for customers - keeps queries compact and makes every column reference unambiguous about which table it came from.

SELECT o.order_id, c.name, p.product_name, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id;