Today I listened to a web cast on TSQL Enhancements in SQL Server 2005. The presenter talked about 2 new keywords that will appear in the next Community Technology Preview. They are EXCEPT and INCLUDE. They will let you look for records that appear in 2 different rowsets. EXCEPT will return the set of all rows in the first rowset that are not in the second rowset. You will write it like:

Select col1 from a
EXCEPT
Select col_1 from b

I think it will be functionally equivalent to:

Select col1 from a
where col1 not in (select col_1 from b)

The INCLUDE will return the set of rows that are in both rowsets.

The one restriction that he mentioned was that the column lists need to be “union compatible” which means they need the same number of columns and the datatype for each column needs to match.

I think this is an interesting solution to the problem of writing a bunch of nested in() or not in () clauses. I can’t wait to get the next CTP and start playing with this.