Simple-Minded Syntax -- Redmondmag.com

Simple-Minded Syntax

SQL Server 2008's EXCEPT and INTERSECT can produce filtered results that require almost no brain power.

By Eric Johnson
02/01/2010

SQL Server 2008 shipped with several enhancements to the T-SQL language. Last time, we looked at the new MERGE syntax. This time, let's check out the new EXCEPT and INTERSECT syntax. Both of these operators allow you to compare results of two queries. Using EXCEPT, you can find all the rows that exist in one query but not the other. Using INTERSECT, you can find all the rows that are the same in both tables. It'll make more sense with an example, so let's take a look.

I took a copy of the HumanResources.Employee table from AdventureWorks and made two copies called Employee1 and Employee2. Then to give us different results, I ran the following delete statement:

DELETE FROM Employee1 WHERE EmployeeID < 10
DELETE FROM Employee2 WHERE EmployeeID > 300

This gives us two tables that have matching records as well as unique records that the other table doesn't have. Now we can explore the statements. First let's run an EXCEPT comparison:

SELECT * FROM Employee1
EXCEPT
SELECT * FROM Employee2

This will return the rows in Employee1 that are not also found in Employee2; in this case, it's all the records with an EmployeeID greater than 300. Reversing the location of the SELECT statements would return all the rows in Employee2 that were not also found in Employee1 (that is, all rows with an EmployeeID less than 10).

Next let's use INTERSECT . The following statement will return all the records that are contained in both queries -- in our case, all records with an EmployeeID between 10 and 300:

SELECT * FROM Employee2
INTERSECT
SELECT * FROM Employee1

These are great little additions to T-SQL that allow you to do some quick comparision work on your result sets. With a few twists, you could even write a query that returns all rows from both tables with no match in the other table.

Next time you find yourself with data to compare, give EXCEPT and INTERSECT a shot.

About the Author

Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology consulting. He is also the President of the Colorado Springs SQL Server User Group. He can be contacted at www.consortioservices.com.

Featured

To Support AI Workloads, Microsoft Taps Lumen for Network Boost

With AI workloads demanding more from its datacenters, Microsoft is looking to outside partners for infrastructure support.
Which Enterprises Should Ditch Passwords? All of Them!

As security biometrics continue to sophisticate, many organizations are still using flawed passwords to safeguard their data. That needs to change. Now.
CrowdStrike Blames Internal Testing Faults for Mass Outage

In an initial post-mortem, Austin-based security firm CrowdStrike said that an issue in its testing software led to the company pushing through a faulty update that took down more than 8.5 million Windows systems last week.
Build Your Own Custom Copilot, Part 2: Making the Connection

Now that you have a plan, it's time to create your personalized Copilot.
Microsoft Releases CrowdStrike Outage Recovery Tool

Microsoft has released a tool to help recover affected systems after last week's global outage caused by a faulty update pushed through by security firm CrowdStrike.