Extracting unmatched records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extracting unmatched records

Author

Topic

MarkHC
Starting Member

5 Posts

Posted - 2005-08-31 : 01:51:17

Hey guys

Im pretty new to SQL so i was wondering if you might be able to give me a hand with something.

I have 2 tables, clients + orders. Each table contains a column called custID which contains the client code, each order creates a new record in the orders table so the orders table contains many more records than the clients table.

I am trying to find out what clients have not placed orders at all, but i can't wrap my head around it, i can obviously work out who HAS, but i need to find out who HASN'T.

Any help would be really appreciated.

Mark

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 02:07:33

Try this

Select CustId from Clients where Custid not in (Select CustId from Orders)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 02:12:47

Hi MarkHC, Welcome to SQL Team!

Or try this:


SELECT CustId 
FROM   Clients AS C 
       LEFT OUTER JOIN Orders AS O
           ON O.CustId = C.Custid
WHERE  O.CustId IS NULL

EDIT: Actually, I wonder which is more efficient - Mady does the "NOT IN (SELECT SomeID FROM SomeTable)" inherently do a DISTINCT? If so that would make Mady's one better I reckon!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 02:28:17

I think

SELECT SomeID FROM SomeTable

is faster than

SELECT Distinct SomeID FROM SomeTable

Because Distinct will do order By as well

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 02:30:55

I was wondering if a NOT IN (SELECT ...) was smart enough to remove the duplicates in some efficient manner.

Strikes me that if there is a large average number of Orders per Client that the "IN" list (or my OUTER JOIN) is going to have a lot of duplciate "false" hits.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources