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.