UNION

• Combines the results of two or more queries into a single result set.
• **UNION** only selects distinct values.
• UNION ALL selects all the values.

SELECT ProductModelID, [Name]
FROM Production.ProductModel 
WHERE ProductModelID BETWEEN 1 AND 10

UNION ALL

SELECT ProductModelID, [Name]
FROM Production.ProductModel
WHERE ProductModelID BETWEEN 20 AND 30

ORDER BY [Name];

EXCEPT & INTERSECT

• Returns distinct values by comparing the results of two queries.
• EXCEPT returns any distinct values from the left query that are not also found on the right query.
• INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.
• When you compare rows for determining distinct values, two NULL values are considered equal.
• The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

SELECT ProductID 
FROM Production.Product

INTERSECT

SELECT ProductID 
FROM Production.WorkOrder;

• The following query returns any distinct values from the query to the left of the EXCEPT operand that are not also found on the right query.

SELECT ProductID 
FROM Production.WorkOrder

EXCEPT

SELECT ProductID 
FROM Production.Product;

• Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
• The nullability of any column in the result set returned by EXCEPT or INTERSECT is the same as the nullability of the corresponding column that is returned by the query on the left side of the operand.
• If EXCEPT or INTERSECT is used together with other operators in an expression, it is evaluated in the context of the following precedence:
  1. Expressions in parentheses
  2. The INTERSECT operand
  3. EXCEPT and UNION evaluated from left to right based on their position in the expression
• If EXCEPT or INTERSECT is used to compare more than two sets of queries, data type conversion is determined by comparing two queries at a time, and following the previously mentioned rules of expression evaluation.
• Fast forward-only and static cursors are fully supported in the result set when they are used with an EXCEPT or INTERSECT operation.
  • If a keyset-driven or dynamic cursor is used together with an EXCEPT or INTERSECT operation, the cursor of the result set of the operation is converted to a static cursor.