Q

NOT EXISTS with a correlated subquery

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 do I do this in SQL?

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.

This was first published in May 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close