Re: SQL Update query

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 08/18/04

  • Next message: Hugo Kornelis: "Re: SQL Update query" 
    Date: Thu, 19 Aug 2004 02:56:17 +0530

    hi tom,

    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 or LEFT OUTER JOIN to get the required result
    set.

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

    2nd method:
    select a.*
    from customers a left outer join
    (select distinct customerid from orders union all select null)b
    on a.customerid = b.customerid
    where b.customerid is null 

    -- 
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com
  • Next message: Hugo Kornelis: "Re: SQL Update query"

    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: 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)