|
This is a classic NOT EXISTS situation. Let me give you an example.
We want to send an email to all registered members except those who
have already bought from us:
select name
, email
from members
where not exists
( select *
from orders
where member_id = members.id )
This is a correlated subquery. The "list of excluded codes"
is the orders table, because any member who has placed an order will have one or
more rows with that member's id in the orders table.
In the query, each members id is used to search in
the orders table. If any order is found for that member id (there could
be more than one, but with the EXISTS operator, database optimizers stop
searching as soon as any one is found), then this isn't a
member we want. Thus the WHERE NOT EXISTS condition.
There's another way to achieve the same result, involving a LEFT OUTER
JOIN with an IS NULL test, but the SQL is not as intuitive as the WHERE NOT
EXISTS correlated subquery.
|
