SQL query to select records which do not exist in a different table with same field structure

I have two tables with two columns in each that have the same structure:



I want to select all of the records in web_site that do NOT have a corresponding match in web_site_prev. I am doing this via an ASP script... currently scrolling through a huge recordset, but that isn't very efficient. How can I do this select in a single statement?

Thank you!
Brad BansnerWeb DeveloperAsked:
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
select * from web_site ws where not exists (select 1 from web_site_prev where  web_site_prev.itemnumber = ws.itemnumber and web_site_prev.category = ws.category )
Scott PletcherSenior DBACommented:
NOT EXISTS is the best approach.

But you should also create an index on web_site_prev on those two columns; whichever column is more commonly used for lookup and/or more selective should be listed first in the index.
Brad BansnerWeb DeveloperAuthor Commented:
Looks good, thanks so much!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.