Re: SQL Update query

From: Tom (Tom_at_discussions.microsoft.com)
Date: 08/19/04 

Date: Thu, 19 Aug 2004 06:35:01 -0700

Hi Vishal,

Thank you for your communication.

The detailed explaination had helped us clarifying some of the doubts.

Thanks
Thomas

"Vishal Parkar" wrote:

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

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: 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)
  • Re: Syntax error (missing operator) in query expression
    ... then save that SQL as a passthrough and enjoy ... INNER JOIN ... requires brackets when the ON clause doesn't immediately follow the JOIN ... customerID]. ...
    (microsoft.public.access.queries)