MySQL JOIN USING and NATURAL JOIN: Shorter Joins

JOIN … USING

When the join columns have the same name in both tables, USING is a shorter way to write the condition:

query.sql

SQL

CREATE TABLE departments (department_id INT PRIMARY KEY, department_name VARCHAR(20));
CREATE TABLE staff (staff_id INT PRIMARY KEY, name VARCHAR(20), department_id INT);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Support');
INSERT INTO staff VALUES (10, 'Asha', 1), (11, 'Ben', 1), (12, 'Chitra', 2), (13, 'Dev', NULL);

SELECT name, department_name, department_id
FROM staff
JOIN departments USING (department_id)
ORDER BY staff_id;

Output

OUTPUT

+--------+-----------------+---------------+
| name   | department_name | department_id |
+--------+-----------------+---------------+
| Asha   | Engineering     |             1 |
| Ben    | Engineering     |             1 |
| Chitra | Sales           |             2 |
+--------+-----------------+---------------+
3 rows in set

USING (department_id) means ON staff.department_id = departments.department_id. Dev has no department (NULL), so the inner join drops Dev.

One column, not two

With ON, the result has both department_id columns. With USING, the join column appears once, and you can write it without a table prefix:

query.sql

SQL

SELECT * FROM staff JOIN departments USING (department_id) ORDER BY staff_id;

Output

OUTPUT

+---------------+----------+--------+-----------------+
| department_id | staff_id | name   | department_name |
+---------------+----------+--------+-----------------+
|             1 |       10 | Asha   | Engineering     |
|             1 |       11 | Ben    | Engineering     |
|             2 |       12 | Chitra | Sales           |
+---------------+----------+--------+-----------------+
3 rows in set

Compare with ON, which returns the column twice:

query.sql

SQL

SELECT * FROM staff s JOIN departments d ON d.department_id = s.department_id ORDER BY s.staff_id;

Output

OUTPUT

+----------+--------+---------------+---------------+-----------------+
| staff_id | name   | department_id | department_id | department_name |
+----------+--------+---------------+---------------+-----------------+
|       10 | Asha   |             1 |             1 | Engineering     |
|       11 | Ben    |             1 |             1 | Engineering     |
|       12 | Chitra |             2 |             2 | Sales           |
+----------+--------+---------------+---------------+-----------------+
3 rows in set

USING with several columns and outer joins

USING (a, b) joins on both columns. With LEFT JOIN ... USING, the shared column shows the value from the left table:

query.sql

SQL

SELECT department_id, department_name, name
FROM departments
LEFT JOIN staff USING (department_id)
ORDER BY department_id, staff_id;

Output

OUTPUT

+---------------+-----------------+--------+
| department_id | department_name | name   |
+---------------+-----------------+--------+
|             1 | Engineering     | Asha   |
|             1 | Engineering     | Ben    |
|             2 | Sales           | Chitra |
|             3 | Support         | NULL   |
+---------------+-----------------+--------+
4 rows in set

Support (department 3) has no staff, and still appears.

When to use USING

It reads well for simple joins where both tables name the key the same way (customer_id in both).
It forces a naming convention that is good in itself: a foreign key has the same name as the key it references.

When the column names differ (orders.customer_id and customers.id), you need ON. Many teams use ON everywhere for consistency.

NATURAL JOIN: avoid it

NATURAL JOIN joins on all columns that have the same name in the two tables, without you naming them:

query.sql

SQL

SELECT name, department_name FROM staff NATURAL JOIN departments ORDER BY staff_id;

Output

OUTPUT

+--------+-----------------+
| name   | department_name |
+--------+-----------------+
| Asha   | Engineering     |
| Ben    | Engineering     |
| Chitra | Sales           |
+--------+-----------------+
3 rows in set

That works, because department_id is the only shared name. The danger is that the join silently changes meaning when someone adds a column.

query.sql

SQL

ALTER TABLE staff ADD COLUMN created_at DATE NULL;
ALTER TABLE departments ADD COLUMN created_at DATE NULL;

SELECT name, department_name FROM staff NATURAL JOIN departments ORDER BY staff_id;

Output

OUTPUT

Empty set

Now both tables have created_at, and NATURAL JOIN also requires created_at to be equal. Both are NULL, and NULL = NULL is not true, so every row disappears. Nothing in the query changed, and the result went from four rows to none, with no error.

A harmless column such as id, name, created_at or updated_at shared by two tables is enough to break a natural join, or to make it match nonsense.

Rule: do not use NATURAL JOIN in real code. Name your join columns with ON or USING.

Summary

Form	Condition	Safe?
`ON a.x = b.y`	Explicit	Yes
`USING (x)`	Same-name column(s) you list	Yes
`NATURAL JOIN`	All same-name columns, implicit	No

warning

Quiet breakage

A schema change in one table must not change a query’s meaning. NATURAL JOIN breaks that rule. USING and ON do not.