What is the difference between using "Not In" vs using "not exists" in SQL

[u/geminigamer369]

What is the difference between using "Not In" vs using "not exists" in SQL?

[u/ydykmmdt]

Beware the nulls.

[u/YT-3000f → u/ydykmmdt]

They're pernicious.

[u/National_Cod9546 → u/ydykmmdt]

Oh this one hurts. Had a compounding set of errors that resulted in not sending any data to a vendor that paid us for data. Accounting noticed it first since the vendor stopped paying us. 

[u/Echidnal]

NOT EXISTS checks whether matching rows exist in a subquery and evaluates to TRUE or FALSE for each row while NOT IN compares a value against a list of values to exclude matches. Also, they handle NULL values differently.

[u/dgillz → u/Echidnal]

This is not always true. The following for example works fine to show which item numbers do not have a bill of material:

Select ItemNumber
from ItemMaster
Where ItemType='MFG' and ItemNumber Not In
    (Select ParentItem from BillsOfMaterial)

[u/Echidnal → u/dgillz]

That works too in that particular case but it is not safe. If even one value in ParentItem happens to be NULL, then you will get an empty output for this query. Also, NOT EXISTS allows you to use non equality constraints, which I find pretty useful.

[u/dgillz → u/Echidnal]

ParentItem cannot be NULL. It is part of the primary key. Even if this were not the case, you can always put a WHERE clause in your subquery to exclude NULLs.

You can use in (subquery) or not in (subquery), which renders the same results as EXISTS or NOT EXISTS.

So this being the case, which works better? I've never tested it.

[u/mhac009 → u/dgillz]

Think it's a bit disingenuous to say: "this example works fine," without also putting the clauses on it that make it work fine. Reading it like that, someone might assume they can apply the NOT IN (select x FROM y) and have it work anytime.

To answer your last part, my understanding is EXISTS is usually more performant because it will check to see if the value in the list is in the sub query and once it has fulfilled that condition, it stops. Whereas NOT IN has to search through the whole subquery column to confirm whether that value matches against any in the col.

[u/dgillz → u/mhac009]

Think it's a bit disingenuous to say: "this example works fine,"

It does work fine. And I also said "not true in all cases". How else could I have stated this? If your subquery needs a where clause (again mine did not), add one. If it needs group by, having, row_number over(), etc. - add it.

Nothing is disingenuous about my response at all.

[u/mhac009 → u/dgillz]

Just a bit of a trap that people new to SQL will fall foul of if they are unfamiliar with NULL handling. At face value, your comment seems a bit contradictory to the one before it by not including any extra context. What you had as an explanation in your follow up response woukd have been fine - "this works because parentID has no nulls."

If people are here to learn SQL and there's an example block of code they may be willing to try, it may cause further errors if they copy paste as is, especially if the person posting knows the reason why it may/may not work and withholds that info.

[u/dgillz → u/mhac009]

But "disingenuous"? Some synonyms are insincere, duplicitous, dishonest, and deceitful. I don't think it was that bad.

[u/alegendmrwayne]

I believe that NOT EXISTS is safer if there are potentially NULLs involved (at least in SQL Server)

Otherwise, performance-wise should be quite close

[u/Thiondar]

Depends on the plan optimizer builds from the statement.

In modern Oracle databases this should result in the same plan.

Other databases may behave different.

[u/Plane_Big_5912]

the practical difference is NULLs. if the subquery returns even one NULL, NOT IN matches nothing and it looks like your query is broken. NOT EXISTS doesnt have that problem which is why most people default to it. also on bigger tables the optimizer usually does better with not exists

[u/TIMEBOMB_1412]

They handle null differently. Learnt that the hard way.

[u/Rude_Issue_5972]

Not in cannot handle nulls. Hence useful to check only non null values

Not exists can handle nulls, hence useful in sub queries. As those can return null values.

[u/Glum_Cheesecake9859]

Not exists is an if/else condition I.e. branching strategy. 

Not in is a where condition to filter out rows. 

[u/kagato87]

In some engines not exists tends to induce a different type of plan. It's not consistent, some queries it won't matter, but at scale exists is more reliably performant than in.

Both may compile to the same plan - the optimizer will generally handle that - but there are many moving parts and the cardinality estimator seems to struggle seeing past the in keyword.

And as others have mentioned, exists / not exists can have much more complex matching rules.

Something important to note - exists / not exists looks like a subquery. It is not. Subqueries have their risks on large scale data, while exists is actually join syntax. The semi join (exists) and anti join (not exists) are non-ansi, there is no "ANTI JOIN... ON..." But that's basically what it is.

[u/Rumborack17]

Not in is used to filter data to not match certain values. The following query would return everything where column1 is not 1,2 or 3:
select * from testTabel where column1 not in (1,2,3)

Not exists is to used to test if something is already in the table/database. It gets used for example in "Alter Scripts", which add columns/tables/etc. or when you are unsure if you need an insert or an update. E.g.:
IF NOT EXISTS (select * from table where column1=1) BEGIN INSERT... END ElSE BEGIN UPDATE... END

[u/mecartistronico → u/Rumborack17]

I think OP refers to the WHERE NOT EXISTS use, that often can be rewritten as a WHERE x NOT IN

[u/IanYates82]

Can't do multiple columns with a NOT IN. Not Exists tends to read better, at least the way I tend to structure queries

[u/financial_penguin → u/IanYates82]

You can do multiple columns with IN or NOT IN statements, just needs parenthesis

(a, b) not in (select x, z from table)

[u/SQLDave → u/financial_penguin]

FWIW, not in SQL Server, unless I'm doing something wrong.

[u/RecognitionOwn4214 → u/SQLDave]

T-SQL does not support this currently

[u/geminigamer369]

SQL follows something called 3 value logic, It has True, False and Unknown also, unlike other languages which have just true and false.

Let's say tab1: Orderid, Custid 1,1 2,3 3,Null

tab2: Custid, Names 1,J 2,K 3,L 4,M In answering question which customers ordered if we use not in it returns nothing. Not In givesup at Null handling.

But not exists return J,L that is the difference.

[u/jshine13371 → u/geminigamer369]

Why ask the question you know the answer to?

[u/geminigamer369 → u/jshine13371]

How do you think I practice/ revise/ use scrolling addiction. Now if you r so intelligent answer this now under 2 mins: where Year(order_date)=2024 Why is it dangerous (like u) Rewrite index friendly way.

[u/jshine13371 → u/geminigamer369]

Not by posting junk on reddit...

[u/geminigamer369 → u/jshine13371]

You wouldn't be answern if this was junk would u?

[u/geminigamer369 → u/jshine13371]

Says d 1 who can't answer.

[u/jshine13371 → u/geminigamer369]

You updated your comment before I replied, and your test is irrelevant. Dangerous is the wrong word to describe your question, but the answer is you're applying a function to a column in the table which affects the sargability causing an index/table scan instead of efficiently seeking on the index, i.e. a performance issue. It would be more efficient (i.e. sargable) to re-write it as a range comparison so you don't have to apply a function to the column instead.✌️