Re: SQL help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thank you.

That's correct, that column does not allow null. The query confused me until
I realized that - I couldn't understand why it was getting a result set.

EXCEPT is new to me. I'll have to read up on that.



"Plamen Ratchev" <Plamen@xxxxxxxxxxxxx> wrote in message
news:Hs2dnT9tGO9LOizXnZ2dnUVZ_qOdnZ2d@xxxxxxxxxxxxxxxx
Yes, they are logically the same (assuming the column from table1 has no
NULLs because then the NOT IN version will return always empty set).

In terms of efficiency most likely they will produce the same or very
similar execution plan.

I would prefer to use version (C) using the NOT EXISTS predicate, or if on
SQL Server 2005/2008 then EXCEPT:

SELECT column FROM TableA
EXCEPT
SELECT column FROM TableB;

--
Plamen Ratchev
http://www.SQLStudio.com


.

Relevant Pages

  • Re: Problem with a join due to multiple Nulls
    ... Using TableA and TableB below i will demonstrate the problem. ... Please post DDL rather than sketches of tables otherwise ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: Require Solution for this SQL problem
    ... runs every morning and based on some logic dumps rows from TableA to ... Now when I run stored proc I want rows copied as below ... FROM TableB (UPDLOCK) ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Problem with a join due to multiple Nulls
    ... multiple NULLS which I do not want to ignore and I fear they will cause ... Using TableA and TableB below i will demonstrate the problem. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Copy records from one table to another
    ... SQL Server MVP ... Columnist, SQL Server Professional ... I have two identical tables (TableA has data, TableB is empty) ...
    (microsoft.public.sqlserver.programming)
  • Re: Conditional UNION
    ... You cannot change the query with conditional IF. ... FROM tableA ... FROM tableB ... Plamen Ratchev ...
    (microsoft.public.sqlserver.programming)