Intersect and Except….almost

There's a new Community Technology Preview of SQL Server 2005 available on MSDN for universal subscribers. I'm sure you've all heard of it by now, I was out of the country last week with limited bandwidth and just downloaded mine yesterday. I was meandering through the BOL, looking for interesting things, when it dawned on me that the BOL title bar read "Microsoft SQL Server 2005 Beta 3". Oh.

I'd heard rumors that the SQL set operations INTERSECT and EXCEPT might be added in beta 3. So I went to my handy BOL Search tab (it took some time to get used to Search being a Tab in the main page), and entered INTERSECT. They were doc'd! Cool.

Brought up SQL Server Management Studio and hammered out my example tables for the test. Then entered:

SELECT id FROM table1 INTERSECT SELECT id FROM table2

drumroll…answer is: "Incorrect syntax near the keyword 'intersect'."

Darn…maybe next CTP release. Either the rumor was mistaken or BOL is just a wee bit ahead of the implementation/testing. It happens.

For more on INTERSECT and EXCEPT check out the BOL. Or your E.F. Codd or ANSI SQL-92 book. Looks like it will be coming. For those of us that remember set theory (I studied it in the public school system in the 60s in 4th grade, does this fact date me?) it's how sets "always worked". For SQL relational calculus afficiandos we've been missing these keywords, although it was relatively straightforward to get the same results with using EXISTS and NOT EXISTS. Yet another feature from the standard that's implemented. I'm sure there's more to it than that, such as interesting questions like "can I use INTERSECT and EXCEPT with indexed views? or in common table expressions? or updateable ADO recordsets?"

So in this version of SQL Server there's the complement of relational calculus operations in T-SQL. And with .NET procedural code, you can whip up UDFs with formulas from differential and integral calculus that run fast, too. [sorry. couldn't help it]

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.