Teradata Query Join Logic

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Fan

Teradata Query Join Logic

Hi Team,

 

I have a doubt on how the following queries work and how are they different?

 

--Picks the values where A.Value in 10 or 20
Select * from
Table1 A
Left join Table2 B
ON (A.ID = B.ID and A.Value NOT IN (10,20))
WHERE B.ID IS NULL;

 

--Does not picks the values where A.Value in 10 or 20
Select * from
Table1 A
Left join Table2 B
ON (A.ID = B.ID)
WHERE B.ID IS NULL
AND A.Value NOT IN (10,20);

 

My question is, why the first query is picking the 10 or 20 values even though the NOT IN condition is mentioned with the on clause.

Appreciate your response.

 

Thanks.

Tags (3)

Accepted Solutions
Junior Contributor

Re: Teradata Query Join Logic

This is how Outer Joins work :-)

 

A condition in ON is not restricting the result set, it only determines which rows can be properly joined, but you still get the row due to the Outer Join.

 

There's a nice Outer Join Case Study in the manuals explaning details.

 

Btw, it's usually more efficient to use NOT EXISTS over LEFT JOIN/IS NULL to find non-matching rows. 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Teradata Query Join Logic

This is how Outer Joins work :-)

 

A condition in ON is not restricting the result set, it only determines which rows can be properly joined, but you still get the row due to the Outer Join.

 

There's a nice Outer Join Case Study in the manuals explaning details.

 

Btw, it's usually more efficient to use NOT EXISTS over LEFT JOIN/IS NULL to find non-matching rows. 

Fan

Re: Teradata Query Join Logic

Thanks a lot dnoeth!