Sorry to ask such a simple Question but i didnt find any concrete way to avoid NOT IN
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.
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.
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 ?
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.
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 ?
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.