Sql server® 2012 t-sql fundamentals

 

CHAPTER 6

 

Set Operators

 

 209

2

You can solve this exercise by using the EXCEPT set operator. The left input is a query that returns 

customer and employee pairs that had order activity in January 2008. The right input is a query that 

returns customer and employee pairs that had order activity in February 2008. Here’s the solution 

query.

USE TSQL2012; 

 

SELECT custid, empid 

FROM Sales.Orders 

WHERE orderdate >= '20080101' AND orderdate < '20080201' 

 

EXCEPT 

 

SELECT custid, empid 

FROM Sales.Orders 

WHERE orderdate >= '20080201' AND orderdate < '20080301';

3

Whereas Exercise 2 requested customer and employee pairs that had activity in one period but not 

another, this exercise concerns customer and employee pairs that had activity in both periods. So this 

time, instead of using the EXCEPT operator, you need to use the INTERSECT operator, like this. 

SELECT custid, empid 

FROM Sales.Orders 

WHERE orderdate >= '20080101' AND orderdate < '20080201' 

 

INTERSECT 

 

SELECT custid, empid 

FROM Sales.Orders 

WHERE orderdate >= '20080201' AND orderdate < '20080301';

4

This exercise requires you to combine set operators. To return customer and employee pairs that had 

order activity in both January 2008 and February 2008, you need to use the INTERSECT operator, as 

in Exercise 3. To exclude customer and employee pairs that had order activity in 2007 from the result, 

you need to use the EXCEPT operator between the result and a third query. The solution query looks 

like this.

SELECT custid, empid 

FROM Sales.Orders 

WHERE orderdate >= '20080101' AND orderdate < '20080201' 

 

INTERSECT 

 

www.it-ebooks.info

---

210  

Microsoft SQL Server 2012 T-SQL Fundamentals

SELECT custid, empid 

FROM Sales.Orders 

WHERE orderdate >= '20080201' AND orderdate < '20080301' 

 

EXCEPT 

 

SELECT custid, empid 

FROM Sales.Orders 

WHERE orderdate >= '20070101' AND orderdate < '20080101';

Keep in mind that the INTERSECT operator precedes EXCEPT. In this case, the default precedence 

is also the precedence you want, so you don’t need to intervene by using parentheses. But you might 

prefer to add them for clarity, as shown here.

(SELECT custid, empid 

 FROM Sales.Orders 

 WHERE orderdate >= '20080101' AND orderdate < '20080201' 

 

 INTERSECT 

 

 SELECT custid, empid 

 FROM Sales.Orders 

 WHERE orderdate >= '20080201' AND orderdate < '20080301') 

 

EXCEPT 

 

SELECT custid, empid 

FROM Sales.Orders 

WHERE orderdate >= '20070101' AND orderdate < '20080101';

5

The problem here is that the individual queries are not allowed to have ORDER BY clauses, and for a 

good reason. You can solve the problem by adding a result column based on a constant to each of 

the queries involved in the operator (call it sortcol). In the query against Employees, specify a smaller 

constant than the one you specify in the query against Suppliers. Define a table expression based on 

the query with the operator, and in the ORDER BY clause of the outer query, specify sortcol as the first 

sort column, followed by country, region, and city. Here’s the complete solution query.

SELECT country, region, city 

FROM (SELECT 1 AS sortcol, country, region, city 

      FROM HR.Employees 

 

      UNION ALL 

 

      SELECT 2, country, region, city 

      FROM Production.Suppliers) AS D 

ORDER BY sortcol, country, region, city;

www.it-ebooks.info

---