Help With Syntax

[PeterA]

I have two tables.
The first table (a) has a list of all my clients.
The second table (b) contains all of the clients who have placed
orders.
How do I get a list of all of the clients from table "a"
who have NOT placed an order based on table "b?"
Keeping in mind, that if they have not placed an order, they will not
appear in table "b."

Both tables have a client_id column

Help

[Jason]

Re: Help With Syntax

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

[SQL Menace]

Re: Help With Syntax

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:[color=blue]
> 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[/color]

[Erland Sommarskog]

Re: Help With Syntax

SQL Menace (denis.gobo@gma il.com) writes:[color=blue]
> or use NOT EXISTS
>
> SELECT * FROM tableA A WHERE NOT EXISTS (SELECT * FROM
> tableB WHERE client_id = A.client_id )[/color]

Yes, this is the preferred syntax for the given problem, as it clearly
expresses what it's all about.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[PeterA]

Re: Help With Syntax

Thanks, Jason. I got it to work using basically this core of code. I
had simplified the question for the purpose of getting a straight
answer, but your code is the core of the soluton. Much appreciated!!


Jason wrote:[color=blue]
> 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[/color]

[--CELKO--]

Re: Help With Syntax

>> The first table (a) has a list of all my clients. The second table (b) contains all of the clients who have placed orders. <<

The quick answer is to use a LEFT OUTER JOIN or an EXISTS() predicate.


The right answer is to ask why you consider these clients to be
logically diffferent entities. It sounds like what you need is one
table with a status code of some kind to tell you when you have a
client who placed an order versus one who has not (and who returns
orders, etc.).

Why persist redundant data in physical storage? This is called
"attribute splitting" -- you are converting an attribute into a table.