Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 75 ยท Querying with MySQL

Joins in MySQL

Learn joins in MySQL: INNER, LEFT and RIGHT, emulating FULL JOIN with UNION, CROSS and self joins, nested loop and hash join, and multi-table UPDATE and DELETE.

Relational data lives in several tables, and a join is how you put it back together: customers with their orders, employees with their managers, products with their categories. MySQL supports the joins you know from standard SQL, lacks one of them (FULL JOIN), adds a pair of handy extensions (joins inside UPDATE and DELETE), and runs joins with a small set of algorithms that explain why some of them are fast and others are not.

This lesson uses small tables so every result is easy to check. If joins are new to you, read the general Joins lesson first. Here we focus on how MySQL treats them.

The joins MySQL gives you

INNER Only rows that match on both sides.
LEFT Every left row, matches or NULLs.
RIGHT Every right row, matches or NULLs.
CROSS Every left row with every right row.

FULL JOIN is not on the list. We will build it from other parts.

1 / 2

The tables we will use

All examples use two tiny tables: departments and employees. One employee has no department (NULL), one department has no employees, and employees also point to their manager, which gives us a self join later.

SQL INNER and LEFT JOIN

Press Run (or Ctrl + Enter) to see the result here.

The inner join returns three rows, because Dev has no department. The left join returns all four, with NULL for Dev. JOIN on its own means INNER JOIN in MySQL, and OUTER is optional (LEFT OUTER JOIN is the same as LEFT JOIN).

ON, USING and WHERE

The condition after ON says how rows match. If both tables use the same column name, USING (column) is shorter and keeps one copy of the column in the output:

SQL
SELECT * FROM orders JOIN order_items USING (order_id);

For outer joins, where you put a filter changes the result. A condition in ON decides which rows match. A condition in WHERE removes rows from the final result, and so can turn a left join back into an inner join:

SQL A filter in ON versus in WHERE

Press Run (or Ctrl + Enter) to see the result here.

The first query lists all three departments (Sales and Legal with NULL employees); the second lists only Engineering. Both are valid, but they answer different questions.

A classic use of a left join is finding rows with no match: add WHERE right_table.key IS NULL.

SQL
SELECT d.name
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.id
WHERE e.id IS NULL;          -- departments with no employees

USING and NATURAL JOIN

Besides ON, MySQL offers two shorthand ways to say how tables match. Both rely on columns that have the same name in both tables.

USING

USING (column, ...) means "match rows where these same-named columns are equal". It is shorter than ON, and it changes the result in one visible way: the join column appears once, not twice. With SELECT *, MySQL puts the merged column first. These results come from a MySQL 8.4 server with employees(emp_id, name, dept_id) and departments(dept_id, name):

SQL
SELECT * FROM employees e JOIN departments d ON e.dept_id = d.dept_id;
-- emp_id | name | dept_id | dept_id | name          (dept_id appears twice)

SELECT * FROM employees e JOIN departments d USING (dept_id);
-- dept_id | emp_id | name | name                     (dept_id appears once, first)

Because the merged column belongs to both sides, you may name it without a table prefix, and in a LEFT JOIN it is the left table's value. A prefixed reference such as e.dept_id still works too. USING is a good habit when your naming convention gives foreign keys the same name as the key they point to (dept_id everywhere). It does not help when the names differ (employees.department_id versus departments.id), and it cannot express extra conditions, so use ON there.

NATURAL JOIN

NATURAL JOIN goes one step further: it automatically joins on every column name the two tables share, with no condition written at all. It is tempting, and it is dangerous, because the join condition is hidden and changes when someone adds a column.

In the example above, both tables have a column called name (the employee's name and the department's name). A natural join between them uses both dept_id and name:

SQL
SELECT * FROM employees e NATURAL JOIN departments d;
-- Empty set: no employee is named "Engineering" or "Sales"

The query is valid and returns no rows, with no error and no warning. The same thing happens in reverse: a join that works today starts returning wrong results the day somebody adds a created_at column to both tables. A second example makes the quiet failure visible. Two tables share id and name, and the names disagree for id 2:

SQL
SELECT * FROM a1 NATURAL JOIN b1;
-- 1 | p | 10 | 100          (id 2 vanished, because 'q' <> 'zzz')

SELECT * FROM a1 JOIN b1 USING (id);
-- 1 | p | 10 | p | 100
-- 2 | q | 20 | zzz | 200    (both rows, joined on id only)

NATURAL LEFT JOIN and NATURAL RIGHT JOIN exist too, with the same hidden rule. The advice most teams follow is: use JOIN ... ON for anything important, and USING when the names really are the shared key. Avoid NATURAL JOIN in application code. The general SQL view of both is in NATURAL and USING Joins.

The example below runs in SQLite, which implements USING and NATURAL JOIN the same way, so you can watch a natural join silently match nothing. It uses explicit column lists, so the output does not depend on how each database orders *.

SQL USING versus NATURAL JOIN

Press Run (or Ctrl + Enter) to see the result here.

The first query lists all three employees with their departments. The second prints 0. The third returns only the row for id 1, while the USING version returns both rows.

RIGHT JOIN and the missing FULL JOIN

RIGHT JOIN is a left join with the tables swapped. Most people rewrite it as a LEFT JOIN to keep queries reading in one direction.

MySQL has no FULL JOIN (FULL OUTER JOIN), which would return all rows from both sides. Emulate it by taking a left join and adding the rows that only exist on the right. UNION removes the duplicates for you:

SQL
SELECT e.name AS employee, d.name AS department
FROM employees AS e LEFT JOIN departments AS d ON d.id = e.department_id
UNION
SELECT e.name, d.name
FROM employees AS e RIGHT JOIN departments AS d ON d.id = e.department_id;

Use UNION ALL with a WHERE e.id IS NULL filter on the second half when rows can legitimately repeat, because plain UNION would collapse identical rows. Modern SQLite (3.39 and newer) does have RIGHT and FULL joins, so both the idea and the emulation can be tried live:

SQL FULL JOIN built from two outer joins

Press Run (or Ctrl + Enter) to see the result here.

The result has five rows: the four employees (Dev with no department) plus the Legal department with no employee. Because it uses LEFT JOIN for both halves and no FULL JOIN keyword, the same text works on MySQL.

CROSS JOIN and self joins

A CROSS JOIN pairs every row of one table with every row of the other, giving m ร— n rows. In MySQL it is also the same as JOIN without an ON clause. It is useful for generating combinations (sizes ร— colors) and dangerous when you forget a join condition by accident, because two tables of 100,000 rows each produce ten billion rows.

A self join joins a table to itself under two aliases. The usual use is a hierarchy such as employee and manager. Use a left join to keep the top of the tree, whose manager is NULL:

SQL Self join and cross join

Press Run (or Ctrl + Enter) to see the result here.

The first result shows Ada with no manager, Ben and Cara reporting to Ada, and Dev reporting to Cara. The second returns the six combinations of three sizes and two colors.

How MySQL executes a join

Knowing the algorithms helps you read EXPLAIN output and decide where an index goes (EXPLAIN and EXPLAIN ANALYZE).

Nested loop join is the classic strategy. MySQL reads a row from the first (outer) table and, for it, looks up matching rows in the next (inner) table, then repeats. This is fast when the inner lookup uses an index on the join column: each lookup touches a handful of rows. If the inner table has no usable index, every outer row triggers a scan of the whole inner table, and the cost multiplies. Indexing the join columns (and foreign keys) is the single most valuable join optimization. When no index exists, older versions used a block nested loop that buffered many outer rows at once to scan the inner table less often.

Hash join arrived in MySQL 8.0.18 for inner joins and was extended to outer joins, semi-joins and anti-joins in 8.0.20. MySQL reads the smaller input into an in-memory hash table keyed by the join column, then scans the other input once and probes the table. It replaces the block nested loop and shines for equality joins on large tables with no useful index. It works best for equality conditions; a join with only a range condition (a.x < b.y) gains little from hashing. In EXPLAIN FORMAT=TREE you will see Inner hash join when MySQL chose it.

Three practical consequences:

  1. Index the columns you join on, especially the column in the inner table.
  2. Join on columns of the same type and collation. A mismatch (int to varchar, or two different collations) can disable index use.
  3. Put selective filters in WHERE, so fewer rows enter the join. The optimizer reorders joins for you, but statistics can mislead it (SELECT: MySQL Specifics shows STRAIGHT_JOIN for forcing an order).

Joins in UPDATE and DELETE

MySQL lets UPDATE and DELETE use joins, which is handy for set-based fixes. In an UPDATE, list the tables and put the join in the statement:

SQL
-- give every Sales employee a 10% raise
UPDATE employees AS e
JOIN departments AS d ON d.id = e.department_id
SET e.salary = e.salary * 1.10
WHERE d.name = 'Sales';

For DELETE, name the table or tables whose rows should go before FROM:

SQL
-- remove employees of the Legal department
DELETE e
FROM employees AS e
JOIN departments AS d ON d.id = e.department_id
WHERE d.name = 'Legal';

-- delete the matching rows from both tables at once
DELETE e, d
FROM departments AS d
JOIN employees AS e ON e.department_id = d.id
WHERE d.name = 'Legal';

Two cautions. ORDER BY and LIMIT are not allowed in multi-table UPDATE and DELETE. And always run the same join as a SELECT first to see exactly which rows will change, because a forgotten join condition touches far more than you meant.

SQLite has no multi-table UPDATE, but supports UPDATE ... FROM, which does the same job. You can try the idea live:

SQL Update with a join (SQLite UPDATE FROM)

Press Run (or Ctrl + Enter) to see the result here.

Ben's salary becomes 1,100 and Cara's 1,320, while Ada's stays at 1,000.

What ran live here

All joins, USING and NATURAL JOIN, the FULL JOIN emulation, self and cross joins and an update with a join ran in SQLite. The UPDATE ... JOIN and multi-table DELETE syntax, hash joins and EXPLAIN output are MySQL-only and appear as read-only code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which join does MySQL not support directly?

    MySQL has no FULL OUTER JOIN. You emulate it with a LEFT JOIN combined by UNION with a RIGHT JOIN (or the unmatched right rows).
  2. 2. How do you find departments that have no employees?

    A left join keeps every department, and the rows with no match have NULL in the employee columns, which the IS NULL filter selects.
  3. 3. What changed in MySQL 8.0.18 for joins?

    Hash join (initially for inner joins) replaced the block nested loop for equality joins without a useful index.
  4. 4. A LEFT JOIN query adds WHERE d.name = 'Engineering' on the right table. What is the usual effect?

    Rows with no matching right row have NULL in d.name, so the WHERE condition removes them. Put the condition in ON to keep the unmatched left rows.
  5. 5. Which statement about multi-table UPDATE or DELETE in MySQL is true?

    The multi-table forms do not allow ORDER BY or LIMIT. Preview the affected rows with a SELECT using the same join first.

Summary

  • INNER, LEFT, RIGHT and CROSS joins work as in standard SQL. JOIN alone is an inner join, and OUTER is optional.
  • A filter in ON decides which rows match. A filter in WHERE can remove unmatched rows from an outer join.
  • There is no FULL JOIN. Combine a LEFT JOIN with the unmatched right rows using UNION.
  • Self joins use two aliases of one table. A forgotten condition in a cross join explodes the row count.
  • MySQL runs joins as nested loops, and since 8.0.18 can use hash joins. Index the join columns and keep types matching.
  • UPDATE ... JOIN and DELETE ... JOIN change data set by set. Preview with SELECT first.

Next: Subqueries, Derived Tables and LATERAL.