SQL query help - finding rows with no relationships
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 a开发者_StackOverflowll the Person ids which have no books? (without doing an outer join and looking for nulls)
select *
from Client as c
where not exists(select * from Client_Books where person_id =c.person_id )
select *
from Client
where person_id not in (select person_id from Client_Books)
SELECT * FROM Client WHERE person_id not in (SELECT person_id FROM Client_Books)
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.
精彩评论