CODEWITHSUNDEEP
sql-servert-sql
Peter
Peter

Reputation: 38495

What does a join with multiple ON's mean?

I found a view in one of our databases that has multiple ON statements after a LEFT OUTER JOIN. I didn't know this was valid TSQL and I'm even more confused as to what it means?

Upvotes: 2

Views: 819

Answers (2)

Alsin
Alsin

Reputation: 1618

ON clauses in FROM force order of joins.

If in expression like this 

A LEFT JOIN B on A.id = B.id JOIN C on b.id = c.id

you'd like to do B INNER JOIN C first, and OUTER JOIN then, you can use parentheses 

A LEFT JOIN (B JOIN C on b.id = c.id) on A.id = B.id

parentheses are not required, you can write ON one after another

A LEFT JOIN B JOIN C on b.id = c.id on A.id = B.id

ON clauses should be in chiatric relation (first-last, second - one before last etc.)

It is like 2 + 2 * 2. If expected result is 8, you have to write (2 + 2) *2 or use reverse Polish notation to avoid ambiguity, 2 2 + 2 *. Parentheses are much easier to understand by human but used rarely. When I see several ON in a row without parentheses, usually it is automatically-generated code.

Upvotes: 4

Swapnil
Swapnil

Reputation: 434

There is only one ON clasue of each JOIN. You might observe multiple ON clauses when you are joining a Table with result of subsequent JOIN.

Select statements in below code will give you same result.

CREATE TABLE #Table1
(
    ID int, 
    Name VARCHAR(50)
)

INSERT INTO #Table1
VALUES 
(1, 'ABC'),
(2, 'PQR')

CREATE TABLE #Table2
(
    ID int, 
    Address VARCHAR(50)
)

INSERT INTO #Table2
VALUES 
(1, 'Addr1'),
(2, 'Addr2')


CREATE TABLE #Table3
(
    ID int, 
    Dept VARCHAR(5)
)

INSERT INTO #Table3
VALUES 
(1, 'Dept1'),
(2, 'Dept2')

SELECT T2.ID,T1.Name,T2.Address,T3.Dept
FROM #Table1 T1
LEFT JOIN #Table2 T2 ON T1.ID=T2.ID
LEFT JOIN #Table3 T3 ON T1.ID=T3.ID

SELECT T2.ID,T1.Name,T2.Address,T3.Dept
FROM #Table1 T1
LEFT JOIN #Table2 T2 
LEFT JOIN #Table3 T3 ON T2.ID=T3.ID ON T1.ID=T2.ID

DROP TABLE #Table1,#Table2,#Table3

Note: In second select statement, one can not use ON T1.ID=T3.ID in place of ON T2.ID=T3.ID. This because, Table1 is not directly joined with Table2/Table3 but with result of their join.

Thanks,

Swapnil

Upvotes: 3

Related Questions