Sql server® 2012 t-sql fundamentals

  

191

C H A P T E R   6

Set Operators

S

et operators are operators that are applied between two input sets—or, to use the more accu-

rate SQL term, multisets—that result from two input queries. Remember, a multiset is not a true 

set, because it can contain duplicates. When I use the term multiset in this chapter, I’m referring to 

the intermediate results from two input queries that might contain duplicates. Although there are 

two multisets as inputs to an operator, depending on the flavor of the operator, the result is either a 

set or a multiset. If the operator is a true set operator (a DISTINCT flavor), the result is a set with no 

duplicates. If the operator is a multiset operator (an ALL flavor), the result is a multiset with possible 

duplicates. This chapter focuses on set operators but also covers multiset operators.

T-SQL supports three set operators: UNION, INTERSECT, and EXCEPT. In this chapter, I first intro-

duce the general form and requirements of the operators, and then I describe each operator in detail.

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

Input Query1 

 

Input Query2 

[ORDER BY ...]

A set operator compares complete rows between the result sets of the two input queries involved. 

Whether a row will be returned in the result of the operator depends upon the outcome of the 

comparison and the operator used. Because by definition a set operator is applied to two sets (or, in 

SQL, multisets) and a set has no guaranteed order, the two queries involved cannot have ORDER BY 

clauses. Remember that a query with an ORDER BY clause guarantees presentation order and there-

fore does not return a set (or a multiset)—it returns a cursor. However, although the queries involved 

cannot have ORDER BY clauses, you can optionally add an ORDER BY clause that is applied to the 

result of the operator.

In terms of logical query processing, each of the individual queries can have all logical query pro-

cessing phases except for a presentation ORDER BY, as I just explained. The set operator is applied to 

the results of the two queries, and the outer ORDER BY clause (if one exists) is applied to the result of 

the set operator.

The two queries involved in a set operator must produce results with the same number of columns, 

and corresponding columns must have compatible data types. By compatible data types I mean that 

the data type that is lower in terms of data type precedence must be implicitly convertible to the 

higher data type.

www.it-ebooks.info

---

192  

Microsoft SQL Server 2012 T-SQL Fundamentals

The names of the columns in the result of a set operator are determined by the first query; there-

fore, if you need to assign aliases to result columns, you should assign those in the first query. 

An interesting aspect of set operators is that when it is comparing rows, a set operator considers 

two NULLs as equal. I’ll demonstrate the importance of this point later in the chapter.

Standard SQL supports two “flavors” of each operator—DISTINCT (the default) and ALL. The DIS-

TINCT flavor eliminates duplicates and returns a set. ALL doesn’t attempt to remove duplicates and 

therefore returns a multiset. All three operators in Microsoft SQL Server support an implicit distinct 

version, but only the UNION operator supports the ALL version. In terms of syntax, you cannot explic-

itly specify the DISTINCT clause. Instead, it is implied when you don’t specify ALL explicitly. I’ll provide 

alternatives to the missing INTERSECT ALL and EXCEPT ALL operators in the “The INTERSECT ALL 

Multiset Operator” and “The EXCEPT ALL Multiset Operator” sections later in this chapter.