SQL Server Technology Insider's 6 collection query

1. Definition

The set operation compares the result set of two input queries by row by line, determining whether a row should be included in the result of the collection operation, based on the comparison result and the set operation used. Because the set operation is a calculation for a collection, the two queries involved in the set operation cannot contain an ORDER BY clause. The query that contains the ORDER BY clause ensures that the results are sorted, so that the query returns not the collection, but the cursor.

ANSI SQL supports two options for each set operation: DISTINCT (default) and all,distinct logically can de-duplicate rows from two inputs, and then return a collection. All operations on two multiple sets do not delete duplicate rows, but instead return a multi-set that may contain duplicate rows. SQL Server 2008 supports the distinct option, but only the all option is supported in union.

2. Features

(1) The two collections participating in the set operation must contain the same number of columns, and the corresponding columns must have compatible data types, so-called "compatible data types" means that the lower priority data types must be implicitly converted to higher-level data types
(2) The column name in the result of the set operation is determined by the first query, so if you want to assign an alias to the result column, you should assign the appropriate alias in the first query.
(3) When a set operation compares rows, two null is considered equal.

3.UNION (set) set operation
The set of two sets means that if an element belongs to any one of the input collections, it also belongs to the result set.
(1) UNION all set operation
The UNION all set operation returns all rows that appear in the input multiset and does not actually compare the rows, nor does it delete duplicate rows. Query Query1 returns m row, query Query2 returns n rows, then query 1 UNION all query 2 returns m + N rows. Because union ALL does not delete duplicate rows, it results in multiple sets instead of real collections.
(2) UNION DISTINCT
Union set operation by removing duplicate records, the resulting result is a real collection, not a multiset.

4.INTERSECT (intersection) Set operation

In set theory, the intersection of two sets (recorded as collections A and B) refers to a collection of all the elements that are both property A and B.
(1) INTERSECT all set operation
However, intersect all differs from UNION ALL: The former does not return all duplicate rows, but returns all duplicate rows of that multi-set with a smaller number of duplicates. If row R appears x times in set A and y occurs in set B, row R should appear min (x, y) in the result of the operation.
Although SQL Server does not support the built-in intersect all operation, the same results can be generated with other solutions. You can use the Row_number function to calculate the number of occurrences of each row in each input query, specifying all rows participating in the set operation in the partition BY clause of the function, and using Select < constant > in the ORDER BY clause to indicate that the order of the rows is not important. Here's a complete solution:

5.EXCEPT (subtraction set operation)
In set theory, the difference between set a and set B is a collection of elements that belong to set a, but do not belong to set B.
(1) EXCEPT all set operation
Assuming that row R appears x times in set A, y times in set B, and X>y, R appears x-y in a EXCEPT all B. Solution can refer to the Intersect all set operation solution

6. Precedence of SET operations
SQL defines the precedence between set operations. The Intersect operation has a higher precedence than the Union and except operations, and the Union and except have equal precedence.

7. Avoid unsupported logical query processing
(1) Only the order by stage allows direct application to the result of the set operation, as shown in the following example:

What if you want to apply a logical phase other than order by for the results of a set operation? You can easily circumvent this restriction by using a table table.

SQL Server Technology Insider's 6 collection query