Is there a way to avoid NOT IN function

General

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

7 REPLIES
Enthusiast

Re: Is there a way to avoid NOT IN function

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

Senior Apprentice

Re: Is there a way to avoid NOT IN function

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.

Enthusiast

Re: Is there a way to avoid NOT IN function

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 ?

Senior Apprentice

Re: Is there a way to avoid NOT IN function

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.

Enthusiast

Re: Is there a way to avoid NOT IN function

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 ?

Senior Apprentice

Re: Is there a way to avoid NOT IN function

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.

Enthusiast

Re: Is there a way to avoid NOT IN function

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