Except and Intersect Operations

We recently talked about the logical Semi Join operation and the Anti Semi Join operation, and demonstrated method to generate those logical joins using traditional SQL operations found in just about any SQL engine. They are produced by using the IN or EXISTS operation for a Semi Join, or the NOT IN or NOT EXISTS operations for an Anti Semi Join.

If you are using TSQL in SQL Server, they introduced two new set operators specific to performing a Semi Join and an Anti Semi Join. Those operators are EXCEPT and INTERSECT.

INTERSECT produces a Semi Join result. The Semi Join returns a distinct list of records from the left side of the INTERSET operation, having one or more matching records on the right side of the INTERSECT operator.

The EXCEPT operator produces the Anti Semi Join. Again the results are the distinct list of records from the left side of the EXCEPT operator, having no matching record from the set on the right side of the EXCEPT operator.

You can still use the old school syntax. However, I find these new operators to be more meaningful in that the intention is clear. There is no doubt about the intention of the query, because they only return Semi Join or Anti Semi Join results.

You can find detailed information about these operators along with examples at https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql