Why does SQL Server allow this JOIN syntax?

advertisements

I just generated some SQL from within Cognos Report Studio. I've sanitized the tables and fields but left the structure in place. Below is the Native SQL that was generated:

SELECT DISTINCT TABLE1.FIELD1
FROM TABLE1
TABLE1 LEFT OUTER JOIN TABLE2
TABLE2 ON TABLE1.KEY1 = TABLE2.KEY1
WHERE TABLE2.FLAG1 = 0
AND TABLE1.FLAG2 = 0
AND TABLE1.FLAG3 = 0
AND TABLE2.FLAG4 = 0
AND TABLE2.FLAG5 = 0

To my surprise the above syntax actually runs in SQL Server. I was expecting a syntax error.

I normally code these up like so:

SELECT DISTINCT TABLE1.FIELD1
FROM TABLE1
/*TABLE1*/ LEFT OUTER JOIN TABLE2
/*TABLE2*/ ON TABLE1.KEY1 = TABLE2.KEY1
WHERE TABLE2.FLAG1 = 0
AND TABLE1.FLAG2 = 0
AND TABLE1.FLAG3 = 0
AND TABLE2.FLAG4 = 0
AND TABLE2.FLAG5 = 0

The above syntax also runs but I expected it to.

So, I'm curious as to why SQL Server allows both of these query syntax. The top query appears to have redundant references to TABLE1 and TABLE2.

Why does the query1 work?

Is there a name for the syntax used in query1?


The general form of a join would look as follows (with [] indicating optional parts, as usual):

SELECT col1, col2, ...
FROM   table1 [table1 alias]
[LEFT] [OUTER] JOIN table2 [table2 alias]
[WHERE ...]

So in your first snippet, you have the table table1 with the alias table1 being joined on table2 with the alias table2. These aliases are pointless, but not illegal.