The most common way to use more than one table in a query is by joining them, which you learned about in Chapter 5. This chapter will explain several other ways to combine tables in a query. Subqueries and common table expressions (CTE) allow you to isolate the logic of complex queries. Union queries allow you to combine the results of two independent queries into one result set. The first thing you will learn in this chapter is how to substitute a query for a hard-coded IN list.

Writing Subqueries

This section demonstrates using subqueries in the WHERE clause. A subquery is a nested query—a query within a query. One reason to use a subquery is to find the rows in one table that match the rows in another table without actually joining the second table. For example, without actually joining the order table, you could use a subquery to display a list of the customers who have placed an order.

Using a Subquery in an IN List

Using a subquery in an IN list is similar to the hard-coded IN list you learned to use in a WHERE clause in Chapter 3. Here is the syntax:

SELECT <select list> FROM <table1>
WHERE <col1> IN (SELECT <col2> FROM <table2>)

Listing 6-1 demonstrates this technique. Type in and execute the code.

Listing 6-1. Using a Subquery in the IN List

SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE CustomerID IN (SELECT CustomerID FROM Sales.SalesOrderHeader);

This query returns a list of the customers who have placed an order (see Figure 6-1). The difference between this example and the examples in Chapter 5 that join these tables is that the columns from the Sales.SalesOrderHeader table don’t show up in the results. Each customer displays only once in the results, not once for each order placed. The subquery produces a list of possible values from one, and only one, column. The outer query compares a column to that list.

Using a Subquery and NOT IN

A subquery in the WHERE clause can also be used to find rows that don’t match the values from another table by adding the NOT operator. You can find the customers who have not placed an order by adding the word NOT to the previous query. Type in and execute the code in Listing 6-2, which demonstrates using NOT IN.

Listing 6-2. A Subquery with NOT IN

SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE CustomerID NOT IN
    (SELECT CustomerID FROM Sales.SalesOrderHeader);

This query returns the opposite results of Listing 6-1 (see Figure 6-2). The subquery returns a list of all the CustomerID values found in Sales.SalesOrderHeader. By using NOT IN, the query returns all the rows from Sales.Customer that don’t match.

Using a Subquery Containing NULL with NOT IN

Recall that you will often get unexpected results if you don’t take NULL values into account. If the subquery contains any NULL values, using NOT IN will incorrectly produce no rows. This is not a bug in SQL Server; you simply must take NULL values into account. For example, the values returned by a subquery are NULL, 1, 2, and 3. The values from the outer query (1, 2, and 10) must each be compared with that list. The database engine can tell that 10 is not 1, 2, or 3, but it can’t tell whether it is the same as NULL. The intended result is 10 because it doesn’t match any of the values from the subquery, but because of the NULL, the comparison returns no results at all. Type in and execute the code in Listing 6-3, which shows unexpected results and how to correct the problem.

Listing 6-3. A Subquery with NOT IN

--1
SELECT CurrencyRateID, FromCurrencyCode, ToCurrencyCode
FROM Sales.CurrencyRate
WHERE CurrencyRateID NOT IN
    (SELECT CurrencyRateID
     FROM Sales.SalesOrderHeader);

--2
SELECT CurrencyRateID, FromCurrencyCode, ToCurrencyCode
FROM Sales.CurrencyRate
WHERE CurrencyRateID NOT IN
    (SELECT CurrencyRateID
     FROM Sales.SalesOrderHeader
     WHERE CurrencyRateID IS NOT NULL);

Figure 6-3 shows the results. Query 1 does not return any results because NULL values exist in the values returned by the subquery. Because any value from CurrencyRateID compared to NULL returns UNKNOWN, it is impossible to know whether any of the values meet the criteria. Query 2 corrects the problem by adding a WHERE clause to the subquery that eliminates NULL values.

Using a subquery in the WHERE clause is a very popular technique. Just make sure you always eliminate the possibility of NULL values in the subquery.

Using EXISTS

Another way to find rows that are included or missing from another table is by using a subquery along with EXISTS. This usually performs better as well. Instead of comparing one column to the results of a subquery, this technique joins the subquery with the outer query in the WHERE clause. If a row is returned when using EXISTS, the subquery returns TRUE and the outer row is returned. Conversely, if no rows are returned, NOT EXISTS returns TRUE and the row from the outer query is returned. Listing 6-4 shows how to write queries using this method.

Listing 6-4. Using the EXISTS Subquery

--1
SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE EXISTS
    (SELECT* FROM Sales.SalesOrderHeader AS SOH
     WHERE SOH.CustomerID = Customer.CustomerID);

--2
SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE NOT EXISTS
    (SELECT * FROM Sales.SalesOrderHeader AS SOH
     WHERE SOH.CustomerID = Customer.CustomerID);

Figure 6-4 shows the results. Notice that the subquery uses the asterisk instead of a list of columns. In this case, it is perfectly fine to do so because no actual data is returned from the subquery. In the WHERE clause, the subquery joins to the outer query on the CustomerID. The outer query table name must be specified. You will learn about other ways to use correlated subqueries in Chapter 10.

Using CROSS APPLY and OUTER APPLY

Two more techniques for joining subqueries are CROSS APPLY and OUTER APPLY. These work similarly to functions that join the results to the left side of the query. The inner query runs once for each input of the outer query. The difference between the two is that OUTER APPLY returns all rows from the left side even if there is not a match. You’ll see some more advanced examples of these techniques in Chapter 14, but for now take a look at the simple examples in Listing 6-5.

Listing 6-5. Using CROSS APPLY and OUTER APPLY

--1
SELECT CustomerID, AccountNumber, SalesOrderID
FROM Sales.Customer AS Cust
CROSS APPLY(SELECT  SalesOrderID
FROM Sales.SalesOrderHeader AS SOH
WHERE Cust.CustomerID = SOH.CustomerID) AS A;

--2
SELECT CustomerID, AccountNumber, SalesOrderID
FROM Sales.Customer AS Cust
OUTER APPLY(SELECT  SalesOrderID
FROM Sales.SalesOrderHeader AS SOH
WHERE Cust.CustomerID = SOH.CustomerID) AS A;

Figure 6-5 shows the results. Query 1 returns the customers along with the orders. Query 2 is similar to a LEFT OUTER JOIN. All customers are returned even if the customer has not placed an order.

Writing UNION Queries

A UNION query is not really a join, but it is a way to combine the results of two queries with the same structure together. I like to think of it as “folding” one set of results into another set of results. One reason for using a UNION query is to view data with one query that combines data from a production table along with data that has been archived into another table. A UNION query combines two queries, and the results are returned in one result set. Here is the syntax:

SELECT <col1>, <col2>,<col3>
FROM <table1>
UNION [ALL]
SELECT <col4>,<col5>,<col6>FROM <table2>

Figure 6-6 shows a diagram of how two UNION queries might look. In this case there are three queries combined with two UNION operators. Each individual query must contain the same number of columns and be of compatible data types. For example, you could have an INT column and a VARCHAR column line up as long as the VARCHAR column contains only numbers. There is an optional keyword, ALL, that actually performs better than without. When the word ALL is left out, the database engine must expend resources removing duplicate rows.

Type in and execute the code in Listing 6-6 to learn how to use UNION.

Listing 6-6. Using UNION

--1
SELECT BusinessEntityID AS ID
FROM HumanResources.Employee
UNION
SELECT BusinessEntityID
FROM Person.Person
UNION
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY ID;

--2
SELECT BusinessEntityID AS ID
FROM HumanResources.Employee
UNION ALL
SELECT BusinessEntityID
FROM Person.Person
UNION ALL
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY ID;

Notice the difference in the two queries in Listing 6-6. Figure 6-7 shows the results. Query 2 uses UNION ALL, which returns all rows, even if they are duplicates. Leaving out the keyword ALL eliminates the duplicates. If at all possible, include the keyword ALL because of the performance benefits. For example, if you are certain that there will not be duplicates, don’t make SQL Server do the work of eliminating duplicates that don’t exist. The first query in the UNION query determines the number of columns and the name of each column. When using a UNION query, only one ORDER BY clause can be used, and it will be located at the end of the statement.

A UNION query is often used to combine the results of two tables so that they look the same. For example, a database has separate customer tables for each division of the company. By using a UNION query, the customers can be displayed together as if they were in the same table. It is also possible to write UNION queries using the same table.

When writing a UNION query, you must make sure that both queries contain the same number of columns in the results and that the data types are compatible. The first query sets the number of columns and the name of each column. The second and later queries must match up to the first query. The data type of each column follows precedence rules, so you can’t allow one query to return an integer where the other query returns a string unless the string can be implicitly converted to an integer. Run these practice queries to see what happens when a UNION query doesn’t follow these rules:

--Incompatible types
SELECT 1
UNION ALL
SELECT 'a'
--Number of columns don't match up
SELECT 1
UNION ALL
SELECT 1,2

This section covered some alternate ways to utilize more than one table within a query. Practice these techniques by completing Exercise 6-1.

Using EXCEPT and INTERSECT

Two query types that are similar to UNION are the EXCEPT and INTERSECT queries. Instead of combining the data, queries written with EXCEPT return rows from the left side that do not match the right side. Queries written with INTERSECT will return rows that are found in both sides. Listing 6-7 shows an example of each query.

Listing 6-7. Using EXCEPT and INTERSECT

--1
SELECT BusinessEntityID AS ID
FROM HumanResources.Employee
EXCEPT
SELECT BusinessEntityID
FROM Person.Person;

--2
SELECT BusinessEntityID AS ID
FROM HumanResources.Employee
INTERSECT
SELECT BusinessEntityID
FROM Person.Person;

Figure 6-8 shows the partial results. There are no BusinessEntityID values found in HumanResources.Employee that do not exist in Person.Person, so no rows are returned by query 1. Query 2 returns rows that exist in both of these tables.

Using Derived Tables and Common Table Expressions

Derived tables and CTEs are techniques used to create a temporary set of results that is used within a query. It’s a way to isolate part of the logic from the rest of the query. Using derived tables and CTEs allows T-SQL developers to solve some complicated query problems. You will find these techniques useful as you learn about aggregate queries (see Chapter 7) and updating data (see Chapter 10). With only the skills you have learned so far, using these techniques does not actually make writing queries any easier, but you will appreciate having learned about them before you progress to more advanced skills.

Using Derived Tables

A derived table is a subquery that appears in the FROM clause of a query. Derived tables allow developers to join to queries instead of tables so that the logic of the query is isolated. At this point, I just want you to learn how to write a query using a derived table. This technique will be very useful as you learn to write more advanced queries. Here is the syntax:

SELECT * FROM (SELECT <select list> FROM <table>) AS <alias>

SELECT <select list> FROM <table1>
[INNER] JOIN (SELECT <select list>
              FROM <table2>) AS <alias> ON <table1>.<col1> = <alias>.<col2>

The first syntax example shows that you can just have any query as a subquery in the FROM clause and select from it in the outer query. The second syntax example shows INNER JOIN, but this could also be done with OUTER JOIN as well. Figure 6-9 shows a diagram representing a LEFT OUTER JOIN query joining the Sales.Customer table to a query of the Sales.SalesOrderHeader table as a derived table. One interesting thing about derived tables is that they must always be aliased.

Listing 6-8 demonstrates how to use a derived table. Type in and execute the code. Make sure you take the time to understand how this works with these simple examples even though a regular join makes more sense at this point.

Listing 6-8. Using a Derived Table

SELECT c.CustomerID, s.SalesOrderID
FROM Sales.Customer AS c
INNER JOIN (SELECT SalesOrderID, CustomerID
            FROM Sales.SalesOrderHeader) AS s ON c.CustomerID = s.CustomerID;

Obviously, you could write this query using a regular INNER JOIN. Figure 6-10 shows the results. Keep in mind these rules when using derived tables. First, any columns that will be needed outside the derived table must be included in its SELECT list. Even though only SalesOrderID from the derived table appears in the main SELECT list, CustomerID is required for joining. Second, the derived table requires an alias. Use the alias to refer to columns from the derived table in the outer query. Finally, the derived table may contain multiple tables, a WHERE clause, and even another derived table. You can use an ORDER BY clause in the derived table only if you use TOP. A derived table cannot contain a CTE, which you will learn about in the next section.

Using Common Table Expressions

Microsoft introduced the CTE feature with SQL Server 2005. This gives developers another way to separate out the logic of part of the query. When writing a CTE, you define one or more queries upfront, which you can then immediately use. This technique will come in handy when learning more advanced skills. For simple problems, there is no advantage over derived tables, but CTEs have several advanced features, which will be covered in Chapter 11, that are not available with derived table subqueries. There are two ways to write CTEs, one of which specifies alias columns names upfront. Here is the syntax:

WITH <CTE Name> AS (SELECT <select list FROM <table>)
SELECT * FROM <CTE Name>

WITH <CTE Name> AS (SELECT <select list> FROM <table1>)
SELECT <select list> FROM <table2>
[INNER] JOIN <CTE Name> ON <table2>.<col1> = <CTE Name>.<col2>

WITH <CTE Name> [(<colname1>,<colname2>,...<colnameN>)]
AS (SELECT <select list> FROM <table1>)
SELECT <select list> FROM <table2>
[INNER] JOIN <CTE Name> ON <table2>.<col1> = <CTE Name>.<col2>

Type in and execute the code in Listing 6-9. Again, these examples are very simple but they should help you learn the technique.

Listing 6-9. Using Common Table Expressions

--1
;WITH orders AS (
    SELECT SalesOrderID, CustomerID, TotalDue + Freight AS Total
    FROM Sales.SalesOrderHeader
    )
SELECT c.CustomerID, orders.SalesOrderID, Orders.Total
FROM Sales.Customer AS c
INNER JOIN orders ON c.CustomerID = orders.CustomerID;

--2
;WITH orders ([Order ID],[Customer ID], Total)
AS (SELECT SalesOrderID, CustomerID, TotalDue + Freight
    FROM Sales.SalesOrderHeader )
SELECT c.CustomerID, orders.[Order ID], Orders.Total
FROM Sales.Customer AS c
INNER JOIN orders ON c.CustomerID = orders.[Customer ID];

You can see the partial results in Figure 6-11. The CTE begins with the word WITH. Because WITH is a keyword in several T-SQL commands, it must be either the first word in the batch, as in this example, or it must be preceded by a semicolon. Because the semicolon is not required yet at the end of T-SQL statements, you may want to get in the habit of staring the common table expression with a semicolon. Supply the CTE name followed by the definition in query 1. In query 2, the column aliases are defined before the AS keyword. In this case, it is required because the expression TotalDue + Frieght doesn’t have an alias. If aliases are defined upfront, they will be used instead of the column names. The main query immediately follows the CTE definition in both cases. Treat the CTE as a regular table in the main query. Once the query completes executing, the CTE goes out of scope and can no longer be used.

Using a Common Table Expression to Solve a Complicated Join Problem

The examples in Chapter 5 on joining tables demonstrated very simple join conditions; one or two columns from one table equal to the same number of columns in another table. Join conditions may be much more complicated. For example, suppose you wanted to produce a list of all customers along with the orders, if any, placed on a certain date. Figure 6-12 shows a diagram of this query. The left-hand circle represents the Customer table in the AdventureWorks database, while the right-hand circle represents the CTE. The Customer table is then joined to the results of the CTE query.

Listing 6-10 demonstrates the problem and how to solve it with a CTE.

Listing 6-10. Using a CTE to Solve a Problem

--1
SELECT c.CustomerID, s.SalesOrderID, s.OrderDate
FROM Sales.Customer AS c
LEFT OUTER JOIN Sales.SalesOrderHeader AS s ON c.CustomerID = s.CustomerID
WHERE s.OrderDate = '2005/07/01';

--2
;WITH orders AS (
    SELECT SalesOrderID, CustomerID, OrderDate
    FROM Sales.SalesOrderHeader
    WHERE OrderDate = '2005/07/01'
    )
SELECT c.CustomerID, orders.SalesOrderID, orders.OrderDate
FROM Sales.Customer AS c
LEFT OUTER JOIN orders ON c.CustomerID = orders.CustomerID
ORDER BY orders.OrderDate DESC;

Take a look at the results in Figure 6-13. Query 1 returns only the 43 rows with the specified order date. The nonmatching rows dropped out of the query because of the NULLs and values other than 2005/07/01 in the OrderDate column. If you want to show all customers even if there is not an order placed on the specified date, then by adding the WHERE clause to the CTE instead, the NULL values and other OrderDate values do not cause any problems, and the correct results are returned.

Another way to solve this when joining two tables with a LEFT OUTER JOIN is to move any conditions affecting the right table to the join criteria. This works fine when dealing with just two tables. Once more than two tables are involved, this technique can fall apart and a CTE or derived table is a better choice.

This section demonstrated how to use derived tables and CTEs. The examples, except for the last one, covered queries that could have easily been written using joins. In Chapter 11, you will learn about the advanced features of CTEs.  Practice writing queries with derived tables and CTEs by completing Exercise 6-2.

Thinking About Performance

The section on UNION queries stated that you will see better performance when using UNION ALL in place of UNION. If you are certain that there are no duplicate rows in the results, you can easily use UNION ALL with no issues.

Compare the performance of the two queries shown in Listing 6-11. Be sure to toggle on the Include Actual Execution Plan setting before running the queries.

Listing 6-11. Compare UNION to UNION ALL

--1
SELECT SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID <= 59549
UNION
SELECT SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID > 59549;

--2
SELECT SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID <= 59549
UNION ALL
SELECT SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID > 59549;

Because the SalesOrderDetailID column makes the Sales.SalesOrderDetail table rows unique, there are no duplicates in the results. The database engine will not know that, however, and will expend resources attempting to eliminate duplicates anyway. Figure 6-14 shows that query 1 using UNION takes 77 percent of the resources. The amount of the difference will vary from query to query. Most of the resources in query 1 are used by the Hash Match operator, which you will always see when using UNION without the ALL keyword.

It seems logical that nesting the UNION ALL query inside a CTE or derived table subquery and then selecting with DISTINCT in the outer query should increase performance and eliminate duplicates at the same time. The optimizer doesn’t work that way, however, and processes the query as if you had used UNION without the ALL keyword. Listing 6-12 shows an example.

Listing 6-12. An Attempt to Get Better Performance and Eliminate Duplicates

SELECT DISTINCT SalesOrderDetailID
FROM (
SELECT SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID <= 59549
UNION ALL
SELECT SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID > 59549
) AS SOD;

Figure 6-15 shows the execution plan, which is identical to the plan for query 1 in Listing 6-11. There was no improvement in the performance with this technique over using UNION without ALL. This also demonstrates that the optimizer can change the query around behind the scenes and still return the same results.

If you know that there are duplicates within each of the queries, but there are no duplicate rows that span both queries, you can use DISTINCT in the individual queries along with UNION ALL. Just keep in mind that you should use UNION ALL whenever possible to improve performance. Another interesting thing about UNION queries is that they use the Hash Match operator. The Hash Match operator can be used to eliminate duplicates. See Chapter 5 to learn more about join operators.

Summary

This chapter covered several ways to involve multiple tables in a query besides joins: subqueries, common table expressions, and union. You can use subqueries in a WHERE clause or to isolate part of the query logic. Starting with SQL Server 2005, you can use common table expressions in place of many subqueries, especially derived tables. Finally, you learned about union queries, which combine the results of multiple queries into one set of results.

The examples shown in this chapter were simple, but it is important to understand these techniques as you progress to more advanced material. In Chapter 7 you will learn how to group and summarize data.

Answers to the Exercises

This section provides solutions to the exercises found on writing queries with subqueries, CTEs, and unions.

Solutions to Exercise 6-1: Using Subqueries

Use the AdventureWorks database to complete this exercise.

1. Using a subquery, display the product names and product ID numbers from the Production.Product table that have been ordered.

   SELECT ProductID, NameFROM Production.Product
   WHERE ProductID IN
               (SELECT ProductID FROM Sales.SalesOrderDetail);

2. Change the query written in question 1 to display the products that have not been ordered.

   SELECT ProductID, Name
   FROM Production.Product
   WHERE ProductID NOT IN (
             SELECT ProductID FROM Sales.SalesOrderDetail
             WHERE ProductID IS NOT NULL);

3. If the Production.ProductColor table is not part of the AdventureWorks database, run the code in Listing 5-11 to create it. Write a query using a subquery that returns the rows from the Production.ProductColor table that are not being used in the Production.Product table.

   SELECT Color
   FROM Production.ProductColor
   WHERE Color NOT IN (
             SELECT Color FROM Production.Product
             WHERE Color IS NOT NULL);

4. Write a query that displays the colors used in the Production.Product table that are not listed in the Production.ProductColor table using a subquery. Use the keyword DISTINCT before the column name to return each color only once. Use the NOT EXISTS method in this query.

   SELECT DISTINCT Color
   FROM Production.Product AS P
   WHERE NOT EXISTS (
             SELECT Color FROM Production.ProductColor AS PC
             WHERE P.Color = PC.Color);

5. Write a query that combines the ModifiedDate from Person.Person and the HireDate from HumanResources.Employee with no duplicates in the results.

   SELECT ModifiedDate
   FROM Person.Person
   UNION
   SELECT HireDate
   FROM HumanResources.Employee;

Solutions to Exercise 6-2: Using Derived Tables and Common Table Expressions

Use the AdventureWorks database to complete this exercise.

1. Using a derived table, join the Sales.SalesOrderHeader table to the Sales.SalesOrderDetail table. Display the SalesOrderID, OrderDate, and ProductID columns in the results. The Sales.SalesOrderDetail table should be inside the derived table query.

   SELECT SOH.SalesOrderID, SOH.OrderDate, ProductID
   FROM Sales.SalesOrderHeader AS SOH
   INNER JOIN (
             SELECT SalesOrderID, ProductID
             FROM Sales.SalesOrderDetail) AS SOD
             ON SOH.SalesOrderID = SOD.SalesOrderID;

2. Rewrite the query in question 1 with a common table expression.

   ;WITH SOD AS (
             SELECT SalesOrderID, ProductID
             FROM Sales.SalesOrderDetail)
   SELECT SOH.SalesOrderID, SOH.OrderDate, ProductID
   FROM Sales.SalesOrderHeader AS SOH
   INNER JOIN SOD ON SOH.SalesOrderID = SOD.SalesOrderID;

3. Write a query that displays all customers along with the orders placed in 2005. Use a common table expression to write the query and include the CustomerID, SalesOrderID, and OrderDate columns in the results.

   WITH SOH AS (
             SELECT SalesOrderID, OrderDate, CustomerID
             FROM Sales.SalesOrderHeader
         WHERE OrderDate BETWEEN '1/1/2005' AND '12/31/2005'
         )
   SELECT C.CustomerID, SalesOrderID, OrderDate
   FROM Sales.Customer AS C
   LEFT OUTER JOIN SOH ON C.CustomerID = SOH.CustomerID;