T-SQL syntax abbreviation when JOIN columns have same name in both tables?

Question

In T-SQL, can a JOIN specification (ON …) possibly be simplified for an equi-join between two tables when such a join happens on two columns that have the same name in both tables?

CREATE TABLE T1 (X … PRIMARY KEY);
CREATE TABLE T2 (X … REFERENCES T1 (X));

Usually I would write such a (inner) join as follows:

SELECT … FROM T1 JOIN T2 ON T1.X = T2.X;

What I would like is something simpler, such as:

SELECT … FROM T1 JOIN T2 ON X;

Is such an abbreviation of the ON … specification possible somehow? (I expect the answer will be "no", but I would like to make sure.)

If not, are there any particular reasons why not? (For instance, would it be likely to lead to frequent column ambiguities when more than two tables are joined, and therefore be of little practical use?)

Answer 1

There is a natural join in the ANSI 92 SQL standard

SELECT … FROM T1 NATURAL JOIN T2

However, this is not supported in SQL Server. Probably a good thing as there could be ambiguities when you do not specify joined columns explicity.

There is no abbreviation like the syntax you mentioned

    FROM T1 JOIN T2 ON X;

Answer 2

• In SQL Server (T-SQL), there's no such shortcut. You have to type out all the JOIN columns.
• In Oracle (P-SQL), NATURAL JOIN will match columns with the same name in both tables.
• In MySQL, USING is somewhat between SQL Server and Oracle in terms of verbosity. You only have to write the column names once A JOIN B USING (col1, col2, col3)

NATURAL JOIN is in ANSI SQL, but as we know, none of the major SQL vendors fully support the standard. Each has their own additions and deviations from the standard.