#19 - How to use EXCEPT in Microsoft Sql Sever

Back by popular demand is stuff that Ben Knows already.

#19 - How to use EXCEPT in Microsoft SQL

EXCEPT and INTERSECT (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns distinct rows by comparing the results of two queries.

EXCEPT returns distinct rows from the left input query that aren’t output by the right input query.

INTERSECT returns distinct rows that are output by both the left and right input queries operator.

The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:

• The number and the order of the columns must be the same in all queries.
• The data types must be compatible.

Syntax

{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }