Posted by SQL Menace on 10/02/84 11:51

be carefull with NULLS and IN, if tableB has even 1 NULL for the
client_id column nothing will be returned
better to add IS NOT NULL

SELECT * FROM tableA WHERE client_id NOT IN (SELECT client_id FROM
tableB WHERE client_id IS NOT NULL )

or use NOT EXISTS

SELECT * FROM tableA A WHERE NOT EXISTS (SELECT * FROM
tableB WHERE client_id = A.client_id )


Denis the SQL Menace
http://sqlservercode.blogspot.com/


Jason wrote:
> Try one of these:
>
> SELECT * FROM tableA WHERE client_id NOT IN (SELECT client_id FROM
> tableB)
>
> SELECT a.*
> FROM tableA a
> LEFT JOIN tableB b ON a.client_id = b.client_id
> WHERE b.client_id IS NULL
>
> you should see better performance on the second one if you have a lot
> of rows in your tables.
>
> Jason

• Next in forum: Re: Have Insert statement, need equivalent Update.
• Prev in forum: Re: What is Your Strategy for Upgrade an Access db to SQL Server 2000?
• Thread view: Re: Help With Syntax

[Reply to this message]