NATURAL JOIN and USING Clauses

NATURAL JOIN matches rows on every column the two tables have in common, with no ON clause at all. It finds the shared column names, compares those columns for equality, and joins on all of them.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  region_id INTEGER NOT NULL
);

CREATE TABLE regions (
  id INTEGER PRIMARY KEY,
  label VARCHAR(60) NOT NULL
);

INSERT INTO customers (id, name, region_id) VALUES
  (1, 'Ada Lovelace', 1),
  (2, 'Grace Hopper', 2);

INSERT INTO regions (id, label) VALUES
  (1, 'EMEA'),
  (2, 'Americas');

-- The shared column is id, so this joins on customers.id = regions.id.
SELECT * FROM customers NATURAL JOIN regions ORDER BY id;

Two rows come back, each with the id, name, region_id and label columns. The join found the shared column id and matched on it, with no join condition written at all.

The risk is that the shared column set is not under your control. The moment somebody adds a column called status to both tables, NATURAL JOIN silently starts requiring equality on status too — and rows that used to match stop matching, with no error and no change to the query text. The join gets stricter every time the schema evolves, which is the opposite of what a reader expects.

The second risk is ambiguity. If the tables share two columns, NATURAL JOIN requires both to be equal. If they share a column that happens to have the same name but a different meaning — an id that is a customer id on one side and a region id on the other, but both called id — the join compares the wrong things and quietly returns nonsense.

The third risk is that the output column names become ambiguous. With NATURAL JOIN, the joined columns appear once in the result rather than twice, and you cannot tell from the output which table each remaining column came from. Every query built on top of that view then needs an alias.

The rule is: use NATURAL JOIN in a quick exploration, never in application SQL. An explicit ON clause says what the join means and fails loudly when the schema changes.

USING names the join columns explicitly, but unlike ON it also changes the shape of the output: the named column appears once, coalesced from both sides, and cannot be qualified with a table alias afterwards.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE regions (
  id INTEGER PRIMARY KEY,
  label VARCHAR(60) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper');

INSERT INTO regions (id, label) VALUES
  (1, 'EMEA'),
  (2, 'Americas');

-- USING merges the id column into a single output column.
SELECT * FROM customers JOIN regions USING (id) ORDER BY id;

-- ON keeps both id columns separate.
SELECT c.id AS customer_id, c.name, r.id AS region_id, r.label
FROM customers AS c
JOIN regions AS r ON r.id = c.id
ORDER BY c.id;

Both queries return two rows with the same information, but the shape differs. The USING version has three columns — id, name, label — because id appears once. The ON version has four columns, with the two ids kept apart under different names.

That difference is the practical reason to prefer ON in application code. With USING, the merged id cannot be qualified: customers.id is rejected because the column is now a single coalesced column with no table of its own. So you cannot say which side the value came from, and if the two ids were ever different types, USING silently coalesces them under the stricter type's rules — in PostgreSQL that means the value is taken from the left-hand table, which can surprise you.

USING is still useful in two places. It is a tidy way to write a join in an ad-hoc exploration, and it is genuinely valuable when you are joining a table to itself or to a table that shares several column names and you want exactly one of them to be the join key while the others are joined on an ON condition — you can mix USING (id) with additional ON predicates for the rest.

One last thing: NATURAL JOIN is effectively USING with an automatically computed column list, which is why it inherits exactly the same output-shape behaviour. If you know the column names, name them with USING. If you want to see the query's intent in the result columns, use ON.