Joins Behaviors with More than one On Condtions in SQL

    Joins Behaviors with More than one On Conditions

Yesterday in my company a fresher asked me can we write more then one On statement in Join Clause

So i thought to write a blog on this.

Yes we can write more than one On condition in a Join clause 

but the thing to  keep in mind that 

if there are only two tables to join then you cannot write ON condition more than once simultaneously if you will try to write you will get the below error.

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'on'.

You should have minimum of three table to write more than one On condition simultaneously in a Query.

Let Me Explain You through an example

--create three tables

create table table1

(empid int ,empname varchar(50),orderid int,MangrID int)

create table table2

(orderid int,orderdate  datetime,MangrId int)

create table table3

(empid int,MangrID int)

--inserting value in all three tables

insert into table1 values(1,'Test',10,2),(2,'Test1',9,3),(3,'Test2',8,4),(4,'Test3',7,5),(5,'Test4',6,7),(6,'Test5',5,8),(7,'Test6',4,9),(8,'Test7',null,null),(9,'Test8',null,null)

insert into table2 values(4,getdate(),1),(5,getutcdate(),2),(6,getdate()-1,3),(7,getdate()+2,4)

insert into table3 values(4,1),(5,2),(6,3),(7,4),(9,9)

--Join condition fetching all common Record from all three tables  with more than 1 On Statement

--Script 1 

select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate  from table1 t1 

join table2 t2  join table3 t3 on t2 .MangrID =t3.MangrID on t1.orderid =t2.orderid 

empid empname MangrID orderid MangrID orderdate

4 Test3 5 7 4                               2013-08-01 11:19:01.173

5 Test4 7 6 3                               2013-07-29 11:19:01.173

6 Test5 8 5 2                               2013-07-30 18:19:01.173

7 Test6 9 4 1                              2013-07-30 11:19:01.173

--Left Join condition with join  from all three tables  with more than 1 On Statement

--Script 2

select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate  from table1 t1 

left outer join table2 t2  join table3 t3 on t2 .MangrID =t3.MangrID on t1.orderid =t2.orderid 

empid empname MangrID orderid MangrID orderdate

1 Test 2 NULL NULL NULL

2 Test1 3 NULL NULL NULL

3 Test2 4 NULL NULL NULL

4 Test3 5 7 4 2013-08-01 11:19:01.173

5 Test4 7 6 3 2013-07-29 11:19:01.173

6 Test5 8 5 2 2013-07-30 18:19:01.173

7 Test6 9 4 1 2013-07-30 11:19:01.173

8 Test7 NULL NULL NULL NULL

9 Test8 NULL NULL NULL NULL

-- Left Join condition followed by Right join  from all three tables with more than 1 On Statement

--Script 3

select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate  from table1 t1 

left outer join table2 t2 right outer join table3 t3 on t2 .MangrID =t3.MangrID on t1.orderid =t2.orderid 

empid empname MangrID orderid MangrID orderdate

1 Test 2 NULL NULL NULL

2 Test1 3 NULL NULL NULL

3 Test2 4 NULL NULL NULL

4 Test3 5 7 4 2013-08-01 11:19:01.173

5 Test4 7 6 3 2013-07-29 11:19:01.173

6 Test5 8 5 2 2013-07-30 18:19:01.173

7 Test6 9 4 1 2013-07-30 11:19:01.173

8 Test7 NULL NULL NULL NULL

9 Test8 NULL NULL NULL NULL

If you will notice in above script 2 and script 3 the result is same

 in script 2 we have left join table2 join table 3

and Script 3 left join table t2 right join table 3

The Reason Behind this is whenever you are writing more than one On condition with different table 

It will take data based on first join mean if the first join between table is Left Join then it will 

fetch data based on Left Join irrespective of  second Join condition 

The Point to Remember is You can write Multiple On  in a Join Clause Depending on no.of  tables you are joining but it will consider only the Join Clause  of First two  Joining tables other tables it will show only matched data.

IF you want to know more on this topic or have some confusion try to run below queries and match the Output with above Scripts:

select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate from table1 t1 left outer join table2 t2 on t1.orderid =t2.orderid  left outer join table3 t3 on t2 .MangrID =t3.MangrID 

select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate from table1 t1 left outer join table2 t2 on t1.orderid =t2.orderid  right outer join table3 t3 on t2 .MangrID =t3.MangrID 

select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate from table1 t1 left outer join table2 t2 on t1.orderid =t2.orderid  join table3 t3 on t2 .MangrID =t3.MangrID 

select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate from table1 t1  join table2 t2 on t1.orderid =t2.orderid  join table3 t3 on t2 .MangrID =t3.MangrID