Is Null conditions in ON (Join) Vs. Is Null in Where

General
Enthusiast

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!

Tags (1)
2 REPLIES
Senior Apprentice

Re: Is Null conditions in ON (Join) Vs. Is Null in Where

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

Enthusiast

Re: Is Null conditions in ON (Join) Vs. Is Null in Where

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