All Forums General
09 Mar 2015
Is Null conditions in ON (Join) Vs. Is Null in Where

Hi, 
Here are my sample tables. 

Table A	

Col1	Col2	Col3
1	A	ABC
2	B	ABD
3	C	ADC



Table B	

Col1	Col2	Col3
1	A	ABC
2	B	ABD

 

Query 1

Select * from  A 
Left outer join  B 
ON A.Col1 =B.Col1 
and A.Col1 =B.Col1
Where 
 B.Col1 is Null
and B.Col2 is Null

 

 

is getting the record that are present in Table A, but missing in Table B. So basically, COL1 or COL2 Null values are being matched for and returned. 

3	C	ADC

 

But, If i use the IS Null check in the Join condition as below, I am getting all the 3 records from Table A. 

 

Select * from  A 
Left outer join  B 
ON A.Col1 =B.Col1 
and A.Col1 =B.Col1
and B.Col1 is Null
and B.Col2 is Null

 

Would like to know the behaviour of a 'Is Null' check in Join conditions and in a Where condition. 

 

Thanks!

dnoeth 4628 posts Joined 11/04
09 Mar 2015

This is not specific for IS NULL, whenever you add a condition to the ON it's evaluated during the join, while a condition on WHERE is evaluated after the join.
 
See: http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/SQL_Reference/B035_1146_111A/ch02.033.077.html

Dieter

yuvaevergreen 93 posts Joined 07/09
10 Mar 2015

Its about the order or precedence in which the query is executed.
Case 1: Left outer join is performed and then where condition is applied.
Case 2: Lefter outer join is performed only on the rows which qualify for null values in B.
"Explain plan" will surely help you to understand deeper.
 
Thanks
Yuva
 
 

You must sign in to leave a comment.