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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading