Re: NOT IN More Efficient???

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 05/22/04

  • Next message: gjoko_at_mt.net.mk: "Please for Help" 
    Date: Sat, 22 May 2004 18:42:37 +0530

    hi bob,

    You can try the same query with not exists.

    Internally in and exists clauses are evaluated differently.EXISTS clause
    checks for the subquery returns at least one row. I would rely on exists as
    compared to IN clause. You may see unexpected result set using not in clause
    in following situation. From performance point of view i would give a go
    ahead to NOT EXISTS compare to NOT IN, also make sure you have necessary
    indexes on the columns that are part of join.

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

    use northwind
    go
    --create sample data table with one row that has customerid null.
    select top 5 customerid into #t_exists from customers union all select null

    select * from customers
    where customerid not in(select customerid from #t_exists)

    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 , as shown above.

    You can use NOT EXISTS clause or LEFT OUTER JOIN to get the required result
    set, NOT EXISTS would be faster compare to NOT IN. However you can see the
    execution plan and resultant time to retrieve the result set. Also make sure
    you have indexes on required columns to be joined.

    1st method:
    select * from customers
    where not exists
    (select * from #t_exists a
    where a.customerid = customers.customerid)

    2nd method:
    select a.*
    from customers a left outer join #t_exists b
    on a.customerid = b.customerid
    where b.customerid is null 

    --
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com
  • Next message: gjoko_at_mt.net.mk: "Please for Help"

    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: 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: NOT IN Query Returns No Results
      ... clause checks for the subquery returns at least one row. ... 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)
    • 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)