Little gems in SQL Server 2005

I know it’s clear I don’t have all that much time for the “innovations” in SQL Server 2005, but there are two syntax additions which I’ve been yearning for for years. (Since I moved from a company which used Oracle, in fact). These are: INTERSECT and EXCEPT (the latter known as MINUS in other dialects of SQL). They complete the list of set operations which has always included UNION. And the practical use is much clearer code in some circumstances.

We have, for example, a set of data on our database and we want to know what’s been added compared with a backup set from a few days back. So:

SELECT * FROM bus_barcode EXCEPT SELECT * FROM bus_barcode_bak

What could be simpler? Throw in a UNION ALL (and some messiness to cope with operator precedence) and you have symmetric difference:

SELECT * FROM (
  SELECT * FROM bus_barcode EXCEPT SELECT * FROM bus_barcode_bak
) AS live_bak
UNION ALL
SELECT * FROM (
  SELECT * FROM bus_barcode_bak EXCEPT SELECT * FROM bus_barcode
) AS bak_live

Brilliant!