Problem solve Get help with specific problems with your technologies, process and projects.

SQL MINUS when you need it

Use these queries when your SQL syntax doesn't support the MINUS operator.

When your SQL Server syntax doesn't support the MINUS operator for finding the difference between two sets, you can use the following two queries, based on left/right outer joins to do the equivalent. My problem was to find new data (new in the current data but not in the previous month's data) and deleted data (not in current data but in the previous month's data) in monthly data dumps from a vendor.

This tip is based on answer found at: Experts Exchange. Acperkins gets the original credit.

select a.* from 
 taba a left join tabb b on a.key=b.key
 where b.key is null
 
 select b.* from 
 taba a right join tabb b on a.key=b.key
 where a.key is null

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.

This was last published in April 2003

Dig Deeper on SQL Server Migration Strategies and Planning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close