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.
N/A

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.

  • Filters
  • JOINS
Tags (3)

Accepted Solutions
N/A

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
N/A

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. 

N/A

Re: Teradata Query Join Logic

Thanks a lot dnoeth!