Sql server® 2012 t-sql fundamentals

202  

Microsoft SQL Server 2012 T-SQL Fundamentals

Circumventing unsupported Logical Phases

This section may be considered advanced for the book’s target audience and is provided here as 

optional reading. The individual queries that are used as inputs to a set operator support all logi-

cal query processing phases (such as table operators, WHERE, GROUP BY, and HAVING) except 

for ORDER BY. However, only the ORDER BY phase is allowed on the result of the operator. What if 

you need to apply other logical phases besides ORDER BY to the result of the operator? This is not 

supported directly as part of the query that applies the operator, but you can easily circumvent this 

restriction by using table expressions. Define a table expression based on a query with a set opera-

tor, and apply any logical query processing phases that you want in the outer query against the table 

expression. For example, the following query returns the number of distinct locations that are either 

employee or customer locations in each country.

SELECT country, COUNT(*) AS numlocations 

FROM (SELECT country, region, city FROM HR.Employees 

      UNION 

      SELECT country, region, city FROM Sales.Customers) AS U 

GROUP BY country;

This query returns the following output.

country         numlocations 

--------------- ------------ 

Argentina       1 

Austria         2 

Belgium         2 

Brazil          4 

Canada          3 

Denmark         2 

Finland         2 

France          9 

Germany         11 

Ireland         1 

Italy           3 

Mexico          1 

Norway          1 

Poland          1 

Portugal        1 

Spain           3 

Sweden          2 

Switzerland     2 

UK              2 

USA             14 

Venezuela       4 

 

(21 row(s) affected)

This query demonstrates how to apply the GROUP BY logical query processing phase to the result 

of a UNION operator; similarly, you could of course apply any logical query processing phase in the 

outer query.

www.it-ebooks.info

---