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?

    Requires Free Membership to View

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.

This was first published in May 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: