Except
SQL Server 2000
Except
Finds the difference between two sets, optionally retaining duplicates.
Syntax
Except(«Set1», «Set2»[, ALL])
Remarks
Duplicates are eliminated from both sets prior to finding the difference. The optional ALL flag retains duplicates. Matching duplicates in «Set1» are eliminated and nonmatching duplicates are retained.
Example
This example
Except({Canada, [British Columbia], Mexico, [British Columbia], USA, Washington}, {Canada, Mexico, California})
returns
{[British Columbia], USA, Washington}
and this example
Except({Canada, [British Columbia], Mexico, [British Columbia], USA, Washington}, {Canada, Mexico, California}, ALL)
returns
{[British Columbia], [British Columbia], USA, Washington}.
