Often on the topic of the different JOIN syntaxes in SQL I mention that I consider ON the only reasonable way to specify JOIN clauses in production code, and I get asked for details. It always takes me a while to recall the specific problem and to come up with a plausible scenario, so I figured it would be easier to document this once and for all.
Suppose you have a database containing information about cars and the parts that go into building said cars. Your schema could look something like this:
create table cars (
carid serial primary key,
model text not null
);
create table manufacturers (
manufacturerid serial primary key,
name text not null
);
create table parts (
partid serial primary key,
manufacturerid int references manufacturers,
model text not null
);
create table car_parts (
carid int references cars,
partid int references parts,
primary key (carid, partid)
);
And then a query to get a specific car and the parts that go into building it might look like this:
select
cars.model,
car_parts.partid,
manufacturers.name as manufacturer
from cars
join car_parts using (carid)
join parts using (partid)
join manufacturers using (manufacturerid)
where cars.carid = $1
;
Everything is working great, until some day someone thinks that you should also track the manufacturer for each car. So you run the following DDL:
alter table cars
add column manufacturerid integer
references manufacturers;
.. and boom. The query shown above that previously worked correctly won't work anymore. Even worse, if it's in a view (as opposed to a function or SQL in the application), the view will continue to work, and you might not even know that it's broken until you try to restore a dump of the database.
This is a huge caveat, and also the reason I think USING does not belong to production code. ON is far more reasonable, though it requires a bit more typing. SQL is not for the lazy.