tags:

views:

37

answers:

4

Consider a DB with a Client table and a Book table:

Client: person_id

Book: book_id

Client_Books: person_id,book_id

How would you find all the Person ids which have no books? (without doing an outer join and looking for nulls)

+2  A: 
select *
from Client 
where person_id not in (select person_id from Client_Books)
RedFilter
+3  A: 
select * 
from Client  as c
where not exists(select * from Client_Books where person_id =c.person_id ) 
Madhivanan
A: 
SELECT * FROM Client WHERE person_id not in (SELECT person_id FROM Client_Books)
Sir Graystar
damn, just beaten to it...
Sir Graystar
A: 
select *  
from Client as c 
where (select coun(*) from Client_Books where person_id =c.person_id ) = 0

COUNT for completeness, since there are already EXISTS and IN solutions posted.

Neil N