Except and Intersect

Here's a powerful feature of SQL Server 2005:
EXCEPT and INTERSECT are operands that return distinct results from automatic joins on every column in two selects.

SELECT ProductID
FROM TableA
EXCEPT
SELECT ProductID
FROM TableB

is equivalent to

SELECT ProductID
FROM TableA
LEFT OUTER JOIN TableB ON TableA.ProductID = TableB.ProductID
WHERE TableB.ProductID IS NULL

the advantage of EXCEPT would grow with more columns to compare.

INTERSECT returns the equivalent of the distinct results of an inner join on every column selected on both sides of the INTERSECT operand.