I need to compare one field in my table against an entire column in another table. The second table has a list of excluded codes that do not need to be in the first table. How would I go about doing this?
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.
Oracle White Papers: Fusion Middleware