CODEWITHSUNDEEP
sqlsql-serverjoinsyntax
SE1986
SE1986

Reputation: 2760

SQL Join with two ON clauses (Nested Join?)

I am debugging a report that has been created by someone else and it has the join criteria below. (I have laid out to my preference and anonymised the table names)

SELECT  --columns
FROM    Table1
        JOIN Table2
        JOIN Table3
            ON Table2.pred = Table3.pred
        JOIN Table4
            ON Table2.pred = Table4.pred
        JOIN Table5
            ON Table2.pred = Table5.pred AND Table2.pred = Table5.pred AND Table3.pred = Table5.pred
        JOIN Table6
            ON Table3.pred = Table6.pred
            ON Table1.pred = Table5.pred AND Table1.pred = Table5.pred
        LEFT JOIN Table7
            ON Table5.pred = Table7.pred AND Table5.pred = Table7.pred
        LEFT JOIN Table7 AS Table7_1
            ON Table5.pred = Table7_1.pred AND Table5.pred = Table7_1.pred
WHERE   --conditions

What I find confusing is that there is no join that associates Table1 with Table 2 and there are two ON clauses after the join to table 6. Usually multiple tables are joined:

FROM  Table
      JOIN Table2
          ON --criteria
      JOIN Table3
          ON --criteria
      JOIN Table4
          ON --criteria
      --etc

The query works but as someone trying to work out what it is doing I am struggling as I have never seen the join syntax before.

is anyone able to explain the syntax to me?

Upvotes: 2

Views: 128

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

That is valid syntax. Here is a simpler example:

from t1 join
     t2 join
     t3
     on t2.? = t3.?
     on t1.? = t2.?

If written with parentheses, it makes more sense:

from t1 join
     (t2 join
      t3
      on t2.? = t3.?
     ) on t1.? = t2.?

Although allowed, I strongly discourage you from using this syntax. Rewrite the query so the joins and ons are interleaved.

Upvotes: 5

Related Questions