Lesson 9. Powerful Join Techniques
Now that you understand the mechanics of inner and outer joins, you are ready to delve into more advanced join techniques. In this lesson, you learn:
What full joins are and why you might need them
The power of self-joins
How to utilize union queries
What subqueries are and how to take advantage of them
How to utilize the INTERSECT operator
What the EXCEPT operator can accomplish for you
Utilizing Full Joins
A full join combines the behavior of the left and right outer joins. It looks like this:
SELECT Person.BusinessEntityID,
FirstName, LastName, PhoneNumber
FROM Person.Person
FULL JOIN Person.PersonPhone
ON Person. BusinessEntityID = PersonPhone.BusinessEntityID
In this example, all people appear in the output regardless of whether they have phone numbers, and all phone numbers appear in the output whether or not they are associated with people. The results appear in Figure 9.1.
FIGURE 9.1 A SELECT statement that shows the result of joining the Person table to the PersonPhone table using a full join.
Taking Advantage of Self-Joins
A self-join involves joining a table to itself. Although it is not the most common type of join, it valuable. Imagine the scenario in which an Employee table contains a field called EmployeeID and another field called SupervisorID. The SupervisorID field must contain a valid EmployeeID. It would not make sense to have separate Employee and Supervisor tables because supervisors are employees. This is where the self-join comes in. A self-join looks like this:
SELECT Employee.BusinessEntityID,
EmployeeInfo.FirstName AS EmployeeFName,
EmployeeInfo.LastName AS EmployeeLName,
SupervisorInfo.FirstName AS SupervisorFName,
SupervisorInfo.LastName AS SupervisorLName
FROM HumanResources.Employee AS Supervisor INNER JOIN
HumanResources.Employee AS Employee
ON Supervisor.BusinessEntityID = Employee.SupervisorID INNER JOIN
Person.Person AS EmployeeInfo
ON Employee.BusinessEntityID = EmployeeInfo.BusinessEntityID INNER JOIN
Person.Person AS SupervisorInfo
ON Supervisor.BusinessEntityID = SupervisorInfo. BusinessEntityID
In this example, the SupervisorID from the Employee table is joined to the BusinessEntityID field in an alias of the Employee table (called Supervisor). The resulting employee and supervisor information is output from the query (see Figure 9.2).
If you take a close look at the results in Figure 9.2, you see that only employees who have supervisors appear in the result. This is because our self-join is an inner join. The example that follows uses a right outer join rather than an inner join to link the Person table to itself.
SELECT Employee.BusinessEntityID,
EmployeeInfo.FirstName AS EmployeeFName,
EmployeeInfo.LastName AS EmployeeLName,
SupervisorInfo.FirstName AS SupervisorFName,
SupervisorInfo.LastName AS SupervisorLName
FROM Person.Person AS SupervisorInfo INNER JOIN
HumanResources.Employee AS Supervisor
ON SupervisorInfo.BusinessEntityID = Supervisor.BusinessEntityID
RIGHT OUTER JOIN Person.Person AS EmployeeInfo
INNER JOIN HumanResources.Employee AS Employee
ON EmployeeInfo.BusinessEntityID = Employee.BusinessEntityID
ON Supervisor.BusinessEntityID = Employee.SupervisorID
The results appear in Figure 9.3. Notice that the employee with a BusinessEntityID of 1 has no supervisor but does appear in the result.
Exploring the Power of Union Queries
Union queries enable you to combine rather than join data from more than one table. A typical example of a union query is one that combines data from a Product table and a DiscontinuedProduct table. Another example is a query that combines data from a Customer table and a CustomerArchive table. Here’s an example of a union query:
SELECT TransactionID, ProductID, TransactionDate, Quantity
FROM Production.TransactionHistory
UNION
SELECT TransactionID, ProductID, TransactionDate, Quantity
FROM Production.TransactionHistoryArchive
This example outputs all rows from the TransactionHistory table as well as from the TransactionHistoryArchive table (see Figure 9.4).
FIGURE 9.4 A SELECT statement that outputs all rows from the TransactionHistory table as well as from the TransactionHistoryArchive table.
If you want to order the results, you must place the ORDER BY statement after the second SELECT statement:
SELECT TransactionID, ProductID, TransactionDate, Quantity
FROM Production.TransactionHistory
UNION
SELECT TransactionID, ProductID, TransactionDate, Quantity
FROM Production.TransactionHistoryArchive
ORDER BY Quantity DESC
In this example, SQL Server combines the results of both SELECT statements in descending order by Quantity. The results appear in Figure 9.5.
FIGURE 9.5 A SELECT statement that outputs the results of a UNION query in descending order by Quantity.
It is often helpful to be able to identify the source table for each row in the output. The SQL statement that follows identifies how to accomplish this task.
SELECT TransactionID, ProductID, TransactionDate,
Quantity, 'C' as RecordSource
FROM Production.TransactionHistory
UNION
SELECT TransactionID, ProductID, TransactionDate,
Quantity, 'A' as Recordsource
FROM Production.TransactionHistoryArchive
ORDER BY Quantity DESC
Notice that in the example, the SQL statement adds a column called RecordSource. It sets the value of the column to a different value within each SELECT statement. The results appear in Figure 9.6.
FIGURE 9.6 A SELECT statement that includes an indicator identifying which table that record came from.
Notice in the figure that some rows contain ‘C’ in the RecordSource column, indicating that they come from the TransactionHistory table. Other rows contain ‘A’ in the RecordSource column, indicating that they come from the TransactionHistoryArchive table.
Working with Subqueries
A subquery is a query that SQL Server evaluates before it evaluates the main query. Here’s an example:
SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE
BusinessEntityID Not In(Select BusinessEntityID FROM Person.PersonPhone)
In this example, SQL Server executes the statement that selects data from the PersonPhone table before it evaluates the statement that selects data from the Person table (see Figure 9.7). It returns all people without records in the PersonPhone table.
This is not a very efficient method of accomplishing the task of finding all the people without phones. A better solution would be to use an outer join to solve this problem. You could modify the SQL statement to look like this:
SELECT Person.BusinessEntityID, FirstName, LastName
FROM Person.Person LEFT OUTER JOIN Person.PersonPhone
ON Person.BusinessEntityID = PersonPhone.BusinessEntityID
WHERE PersonPhone.BusinessEntityID Is Null
This example uses a left outer join to select all people who do not have phones. Because this uses a left outer join, people are included whether or not they have phone records. Because the criteria designate that only rows with a null BusinessEntityID appear in the output, only people without phone records are included.
Using the INTERSECT Operator
An alternative technique to use when you want to only return rows in one table that have corresponding rows in another table is to use the INTERSECT operator. The INTERSECT operator looks like this:
SELECT ProductID
FROM Production.Product
INTERSECT
SELECT ProductID
FROM Production.WorkOrder
ORDER BY ProductID
The results show only products that have work orders (see Figure 9.8).
Working with the EXCEPT Operator
The INTERSECT operator’s counterpart is the EXCEPT operator. The EXCEPT operator returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in the second SELECT statement. The EXCEPT clause looks like this:
SELECT ProductID
FROM Production.Product
EXCEPT
SELECT ProductID
FROM Production.WorkOrder
ORDER BY ProductID
This example returns all Products that do not have associated work orders. The results appear in Figure 9.9.
Swapping the order of the tables returns a totally different result. The example that follows returns the work orders that aren’t associated with a product. With referential integrity in place, this statement will never return any rows.
SELECT ProductID
FROM Production.WorkOrder
EXCEPT
SELECT ProductID
FROM Production.Product
ORDER BY ProductID
Summary
This lesson began with a discussion of full joins. You then learned how to create self joins, which allow you to join a table to itself. The lesson moved on to union queries, which allow you to combine data from two tables as if they were within one table. After exploring subqueries, you learned about the INTERSECT and EXCEPT operators that enable you to build queries that mimic the behavior of inner and outer joins.
Q&A
Q. Explain what a full join is.
A. A full join combines the behavior of a left outer join and a right outer join. An example is where you show all customers whether or not they have orders and all orders whether or not they are associated with a customer.
Q. Describe what a subquery does.
A. SQL Server Express evaluates a subquery before it evaluates the main query.
Q. Provide an example of where a self-join is useful.
A. You can use a self-join to determine the name of the supervisor associated with each employee.
Q. Provide an example of where a union query is useful.
A. You can use a union query to combine data from a sales table and a sales history table, displaying the results in a report.
Workshop
Quiz
1. A union query joins two tables (true/false).
2. What operator simulates an inner join?
3. Subqueries are the most efficient way to return rows in one table that do not have associated rows in another table (true/false).
Quiz Answers
1. False. Union queries allow you to combine two tables.
2. INTERSECT.
3. False. Outer joins and the EXCEPT operator are far more efficient techniques to use when you need to return rows in one table that do not have associated rows in another table.
Activities
Create a SQL statement that uses a full join to join the Product and WorkOrder tables. Add some people to the Person table. You will need to create new BusinessEntityIDs in the Person.BusinessEntity table first. Then create a subquery that finds all people in the Person table who do not have records in the PersonPhone table. Build a SQL statement with an outer join that returns the sample people. Finally, accomplish the same task using the EXCEPT operator.