Understanding JOINs in MySQL

Relational databases store data across multiple tables to avoid duplication. JOINs let you recombine that data by linking rows from different tables based on a related column. Mastering JOINs is essential -- nearly every real-world query involves at least one.

INNER JOIN

INNER JOIN returns only the rows that have matching values in both tables. If a row in one table has no match in the other, it is excluded from the result.

SELECT
  o.order_id,
  c.first_name,
  c.last_name,
  o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

LEFT JOIN

LEFT JOIN returns all rows from the left table and the matching rows from the right table. When there is no match, the right-side columns contain NULL. This is useful when you want to include items that may not have related records.

-- All customers, including those with no orders
SELECT
  c.first_name,
  c.last_name,
  o.order_id,
  o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

RIGHT JOIN

RIGHT JOIN is the mirror of LEFT JOIN -- it keeps all rows from the right table. In practice, most developers prefer LEFT JOIN and simply swap the table order, but RIGHT JOIN can improve readability in certain scenarios.

SELECT
  e.first_name,
  d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

CROSS JOIN

CROSS JOIN produces the Cartesian product of two tables -- every row from one table paired with every row from the other. This is rarely used with large tables but is handy for generating combinations, such as all sizes and colors of a product.

SELECT s.size_label, c.color_name
FROM sizes s
CROSS JOIN colors c;

Joining Multiple Tables

You can chain as many JOINs as needed. Each additional JOIN adds another table to the query. Use table aliases to keep the query readable.

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

Self JOIN

A table can be joined to itself. This is common for hierarchical data like employees and their managers.

SELECT
  e.first_name AS employee,
  m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;