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'.
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