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.