Avoiding SQL Ambiguities attributable to JOIN USING and NATURAL JOIN

Some SQL operators are as esoteric as they’re highly effective. One of many oldest operator that you just’ve probably hardly utilized in actual world purposes is NATURAL JOIN which is the default in relational algebra. We’ve lined a cool use-case for NATURAL JOIN earlier on this weblog.

The principle motive why it’s not very helpful is as a result of it joins two tables utilizing all column names from each joined tables. In the event you have a look at the Sakila database, this contains audit columns like LAST_UPDATE, which is current on all tables. For instance, you wouldn’t need:

As a result of that might be equal to:

That is an apparent motive, however even when you fastidiously design your schema to by no means include two columns that “unintentionally” share their identify, somebody may add one accidentally, nonetheless, which renders present queries ineffective.

JOIN USING to the rescue?

So, you’d assume that the syntax that ought to actually be used as a substitute is JOIN .. USING? This seems to be good, doesn’t it?

And sure, that is actually cool for ad-hoc SQL. In case your schema is designed this fashion (major key column identify = international key column identify), then this syntax can be utilized to shortly be a part of tables throughout the schema.

However in an actual world software, it is best to nonetheless keep away from the syntax to stop disagreeable surprises when evolving the schema. Have a look at this script, for instance:

The question works completely effective as meant. However what occurs if we add:

Now, there’s an ambiguity within the final JOIN. If we repeat the SELECT assertion from earlier, PostgreSQL complains:

ERROR: widespread column identify “j” seems greater than as soon as in left desk

Oracle confirms:

SQL Error [918] [42000]: ORA-00918: J: column ambiguously specified – seems in A and B

Why? As a result of we’re actually becoming a member of:

And since j shouldn’t be a be a part of column between a and b, it’s now ambiguous. Be aware how the ALTER TABLE assertion broke a question that was completely effective earlier than. It might not have damaged if we used the JOIN .. ON syntax:

Too dangerous! Once more, when writing ad-hoc SQL for fast querying the Sakila database, JOIN .. USING is a great tool – similar to SELECT *. However in manufacturing queries, this ad-hoc SQL software ought to be prevented.