Joins

A join combines rows from two or more tables using a related column.

Concept

A join combines rows from two or more tables using a related column. MySQL supports INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, self joins, NATURAL JOIN, USING, and STRAIGHT_JOIN. MySQL does not support FULL OUTER JOIN — it is emulated with UNION.

flowchart LR
    subgraph INNER[INNER JOIN]
        I1[only matching rows]
    end
    subgraph LEFT[LEFT JOIN]
        L1[all left rows + matches<br/>NULLs where no match]
    end
    subgraph RIGHT[RIGHT JOIN]
        R1[all right rows + matches]
    end
    subgraph FULL[FULL OUTER emulation]
        F1[LEFT JOIN UNION RIGHT JOIN]
    end

Why It Matters

Normalized data lives in many tables; almost every useful query joins them. Wrong join types drop rows; joins on non-unique keys duplicate them.

Syntax

In MySQL, JOIN, INNER JOIN, and CROSS JOIN are syntactic equivalents; JOIN without ON produces a Cartesian product.

SELECT ...
FROM a
[INNER] JOIN b ON b.key = a.key
LEFT  [OUTER] JOIN c ON c.key = a.key
RIGHT [OUTER] JOIN d ON d.key = a.key
CROSS JOIN e
JOIN f USING (common_col);

Example

12.1 INNER JOIN Kiran (NULL dept) and Finance (no employees) are excluded.

12.2 LEFT JOIN and anti-join Filter placement: conditions on the optional (right) table belong in ON; putting them in WHERE turns the LEFT JOIN into an INNER JOIN.

12.3 RIGHT JOIN 12.4 FULL OUTER JOIN emulation Returns 9 rows: the 7 matched employees, Kiran with NULL department, and Finance with NULL employee. (Using UNION ALL + the IS NULL filter avoids removing legitimate duplicate rows, which plain UNION would do.) 12.5 CROSS JOIN 12.6 SELF JOIN 12.7 Multiple joins

12.8 Join fan-out (duplicate multiplication) Fix by not joining unnecessary tables or aggregating the "many" side first.

12.9 USING, NATURAL JOIN, STRAIGHT_JOIN Use STRAIGHT_JOIN or optimizer hints (/*+ JOIN_ORDER(c, o) */) only after confirming with EXPLAIN that the optimizer chose a poor order.

12.10 Join algorithms in MySQL Nested-loop join (with index lookups on the inner table) — the classic MySQL strategy; very fast when the join column of the inner table is indexed.

Hash join (8.0.18+) — used for equi-joins when no useful index exists; replaced the old Block Nested-Loop.

MySQL has no merge join.

EXPLAIN FORMAT=TREE shows which is used (Module 32).

SELECT e.emp_id, e.first_name, d.dept_name
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
ORDER BY e.emp_id;

+--------+------------+-------------+
| emp_id | first_name | dept_name   |
+--------+------------+-------------+
|      1 | Asha       | Engineering |
|      2 | Vikram     | Engineering |
|      3 | Neha       | Engineering |
|      4 | Rahul      | Sales       |
|      5 | Priya      | Sales       |
|      6 | Arjun      | HR          |
|      8 | Meera      | Engineering |
+--------+------------+-------------+
7 rows in set

SELECT c.customer_id, c.name, o.order_id, o.total_amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

+-------------+--------------+----------+--------------+
| customer_id | name         | order_id | total_amount |
+-------------+--------------+----------+--------------+
|           1 | Ananya Gupta |     1001 |     75800.00 |
|           1 | Ananya Gupta |     1003 |     18100.00 |
|           2 | Ben Carter   |     1002 |     12000.00 |
|           2 | Ben Carter   |     1006 |     93000.00 |
|           3 | Chen Wei     |     1004 |       800.00 |
|           4 | Diya Patel   |     1005 |      1600.00 |
|           5 | Elena Rossi  |     NULL |         NULL |
+-------------+--------------+----------+--------------+

-- Customers with no orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;          -- Elena Rossi

-- All customers with their DELIVERED orders (if any)
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'DELIVERED'
ORDER BY c.customer_id, o.order_id;
-- Ananya 1001, Ben 1002, Ben 1006, Chen NULL, Diya NULL, Elena NULL

SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM employees e
RIGHT JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;

+-------------+-----------+
| dept_name   | headcount |
+-------------+-----------+
| Engineering |         4 |
| Sales       |         2 |
| HR          |         1 |
| Finance     |         0 |
+-------------+-----------+

SELECT e.first_name, d.dept_name
FROM employees e LEFT JOIN departments d ON d.dept_id = e.dept_id
UNION ALL
SELECT e.first_name, d.dept_name
FROM employees e RIGHT JOIN departments d ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;          -- only the right-side rows with no match

SELECT d.dept_name, q.quarter
FROM departments d
CROSS JOIN (SELECT 'Q1' AS quarter UNION ALL SELECT 'Q2') q
ORDER BY d.dept_id, q.quarter;      -- 4 × 2 = 8 rows

Expected Result

Revenue per product including unsold products:

SELECT p.product_name,
       IFNULL(SUM(oi.quantity), 0)                 AS units,
       IFNULL(SUM(oi.quantity * oi.unit_price), 0) AS revenue
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
GROUP BY p.product_id, p.product_name
ORDER BY revenue DESC;

+---------------+-------+-----------+
| product_name  | units | revenue   |
+---------------+-------+-----------+
| Laptop        |     2 | 150000.00 |
| Monitor       |     2 |  36000.00 |
| Desk Chair    |     1 |  12000.00 |
| Mouse         |     4 |   3200.00 |
| Notebook      |     2 |    100.00 |
| Standing Desk |     0 |      0.00 |
+---------------+-------+-----------+

Common Mistakes

Expecting FULL OUTER JOIN to work in MySQL.

Filtering the right table in WHERE after a LEFT JOIN.

COUNT(*) instead of COUNT(child.pk) with LEFT JOIN.

Joining columns with different types or collations (index not used; possible error 1267).

Missing indexes on join columns → slow nested loops or hash joins on large tables.

Best Practices

Explicit JOIN ... ON, table aliases, qualified columns.

Index foreign-key/join columns (InnoDB requires it for declared FKs).

Aggregate before joining multiple one-to-many relationships.

Interview Relevance

Very high: join types, FULL OUTER JOIN in MySQL, anti-joins, self joins, fan-out, "employees who earn more than their managers".

Practice Questions

List all departments with employee names (including empty departments).

Find employees whose manager is in a different department.

Emulate a FULL OUTER JOIN between customers and orders.

Show each product and how many distinct customers bought it.