T-SQL Fundamentals, Third 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 ...