Re: NOT IN Query Returns No Results

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 09/17/04 

Date: Fri, 17 Sep 2004 22:01:27 +0530

use "not exists"

Internally in and exists clauses are evaluated differently. In clause is
internally evaluated as multiple OR conditions. Whereas EXISTS
clause checks for the subquery returns at least one row.

This is typical scenario when checking non-existing rows from the table.

select * from customers
where customerid not in(select customerid from orders union all select null)

If any of the rows from the subquery returns null value and if you are using
NOT IN clause then you will get any empty resultset.

You can use NOT EXISTS clause to get the required result set.

same query can be re-written as

select * from customers
where not exists
(select * from
(select distinct customerid from orders union all select null) a
where a.customerid = customers.customerid
) 

-- 
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com

Relevant Pages

  • Re: SQL 2005 Ambiguous column name
    ... ORDER BY is even more interesting here since it has to resolve against two scopes: The "exposed" column names in the select list as well as the columns accessible through the FROM clause. ... AFAIK the closer scope for ORDER BY is the select list. ... So it's within the perogative of the DBMS DBMS to raise an error here. ... SELECT customerid AS X, customerid * -1 AS X FROM orders ORDER BY X ...
    (comp.databases.ms-sqlserver)
  • Re: NOT IN More Efficient???
    ... Internally in and exists clauses are evaluated differently.EXISTS clause ... --create sample data table with one row that has customerid null. ... If any of the rows from the subquery returns null value and if you are using ... You can use NOT EXISTS clause or LEFT OUTER JOIN to get the required result ...
    (microsoft.public.sqlserver.mseq)
  • Re: SQL Update query
    ... "Vishal Parkar" wrote: ... Whereas EXISTS clause checks ... > where customerid not in(select customerid from orders union all select null) ...
    (microsoft.public.sqlserver.mseq)
  • Re: Group By?!
    ... If I were to take out that huge group by clause, ... Let's try and demonstrate the principle with a simpler query. ... Now lets group by CustomerID and EmployeeID, ...
    (microsoft.public.access.devtoolkits)
  • Re: SQL Update query
    ... Internally in and exists clauses are evaluated differently.In clause is ... where customerid not in(select customerid from orders union all select null) ... If any of the rows from the subquery returns null value and if you are using ...
    (microsoft.public.sqlserver.mseq)