Menu
I spent some time trying to figure out why this query isn't pulling the results i expected:
SELECT * FROM NGS WHERE ESPSSN NOT IN (SELECT SSN FROM CENSUS)
finally i tried writing the query another way and this ended up getting the expected results:
SELECT * FROM NGS n WHERE NOT EXISTS (SELECT * FROM CENSUS WHERE SSN = n.ESPSSN)
The first query seems more appropriate and "correct". I use "in" and "not in" all the time for similar selects and have never had a problem that i know of.
210
The main difference between them is that IN selects a list of matching values, whereas EXISTS returns the Boolean value TRUE or FALSE. Before making the comparison, we will first know these SQL clauses.
The most important thing to note about NOT EXISTS and NOT IN is that, unlike EXISTS and IN, they are not equivalent in all cases. Specifically, when NULLs are involved they will return different results. To be totally specific, when the subquery returns even one null, NOT IN will not match any rows.
Ans:- NOT EXISTS SQL means nothing returned by the subquery. It is used to restrict the number of rows returned by the SELECT statement. In the server, it checks the Subquery for row existence, and if there are no browns then it will return true otherwise false.
NOT IN clause in SQL Server is nothing but a series of NOT EQUAL TO. One of the values from the subquery is a NULL. The result set (custname from tbl_customers) contains A, B & NULL. Every value from the outer query is compared with every value from the inner query.
If you write out the syntactic sugar, x not in (1,2,3) becomes:
x <> 1 AND x <> 2 AND x <> 3
So if the ssn column contains a null value, the first query is the equivalent of:
WHERE ESPSSN <> NULL AND ESPSSN <> ...
The result of the comparison with NULL is unknown, so the query would not return anything.
64
As Andomar said, beware of NULL values when using NOT IN
Also note that a query using the NOT IN predicate will always perform nested full table scans, whereas a query using NOT EXISTS can use an index within the sub-query, and be much faster as a result.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
