All Forums General
naveed_4481m 1 post Joined 09/14
27 Mar 2015
Is there a way to avoid NOT IN function

Hi Team,
Sorry to ask such a simple Question but i didnt find any concrete way to avoid NOT IN 
Ex:
sel * from table_a where ID not in ( sel ID from table_b) 
1. would like to whether this will have any impact in Performance, i believe yes.
2. also the alternative way to write the above query.
Thanks
Navy

VandeBergB 182 posts Joined 09/06
27 Mar 2015

select a.* from table_a a left outer join table_b b on (a.id=b.id) where b.id is null;

Some drink from the fountain of knowledge, others just gargle.

dnoeth 4628 posts Joined 11/04
27 Mar 2015

Hi Navy,
you should always try to avoid NOT IN unless both columns (from the inner and outer table) are defined as NOT NULL (that's similar for all DBMSes), because otherwise you have to deal with three-valued logic.
Every NOT IN can easily be rewritten using NOT EXISTS:

sel * from table_a as a 
where not exists 
  ( sel * from table_b as b
    where a.ID = b.ID ) 

I've never seen a worse plan for NOT EXISTS than for NOT IN, and it's also usually better than LEFT JOIN/IS NOT NULL.

Dieter

karthikcsekar 17 posts Joined 02/14
31 Mar 2015

dnoeth - With regard to your above solution, is there anyway I can use the Explain statement to know if 'not exists' is indeed a better idea ?

CK

dnoeth 4628 posts Joined 11/04
31 Mar 2015

Simpy compare both Explains, if there are any NULLable columns you'll find aggregate steps (to check for NULLs) and steps with "skip this step if NULLs exists" for NOT IN. 
NOT EXISTS simply adds a "where unknown comparison will be ignored" to an "Exclusion Join" step.

Dieter

karthikcsekar 17 posts Joined 02/14
02 Apr 2015

Thanks ! Will try it out.
However, there is a common notion that not exists and not in cannot be used on large datasets and in those circumstances an Exception join is a better idea.
Am I right ?

CK

dnoeth 4628 posts Joined 11/04
02 Apr 2015

It might be "common notion" in other DBMSes, but Teradata rewrites both NOT IN/NOT EXISTS to joins, too. The LEFT JOIN/NOT NULL might be more efficient in some rare cases (depending on the number of matching rows), but normally NOT EXISTS is better.

Dieter

karthikcsekar 17 posts Joined 02/14
06 Apr 2015

Thanks. Will try out on a few sample datasets and let you know if I have queries.

CK

You must sign in to leave a comment.