T-SQL Fundamentals, 4th Edition

Chapter 6

Set operators

Set operators are operators that combine rows from two query result sets (or multisets). Some of the operators remove duplicates from the result, and hence return a set, whereas others don’t, and hence return a multiset. T-SQL supports the following operators: UNION, UNION ALL, INTERSECT, and EXCEPT. In this chapter, I first introduce the general form and requirements of these operators, and then I describe each operator in detail.

The general form of a query with a set operator is as follows:

Input Query1
<set_operator>
Input Query2
[ORDER BY ...];

A set operator compares complete rows between the results of the two input queries involved. Whether a row will be returned in the result of the operator depends on the outcome ...