Lesson 8. Building SQL Statements Based on Multiple Tables
Now that you’ve learned the basics of the T-SQL language, you’re ready to move on to more advanced techniques. To really take advantage of what T-SQL has to offer, you must know how to return recordsets that contain data based on multiple tables. In this lesson, you learn:
What inner joins are and how to implement them
What outer joins are and how they differ from inner joins
How to output data based on multiple tables
Working with Join Types
When you build a system based on normalized table structures, you must join the tables back together to see the data in a useable format. For example, if you have separated customers, orders, and order details, you need to join these tables in a query to see the name of the customer who placed an order for a particular item. Several types of joins are available. They include inner joins, outer joins, full joins, and self-joins. This lesson covers the inner and outer join types. The next lesson, “Powerful Join Techniques,” covers full joins and self-joins.
Using Inner Joins
An inner join is the most common type of join. When you use an inner join, only rows on the one side of the relationship that have matching rows on the many side of the relationship are included in the output. Here’s an example:
SELECT Person.BusinessEntityID,
FirstName, LastName, PhoneNumber
FROM Person.Person
INNER JOIN Person.PersonPhone
ON Person.BusinessEntityID = PersonPhone.BusinessEntityID
This example includes only those people who have phone numbers. The results appear in Figure 8.1.
At times, you need to join more than two tables in a SQL statement. The most common syntax is as follows:
FROM table1 JOIN table2 ON condition1 JOIN table3 ON condition2
The following example joins the Person, PersonPhone, and PhoneNumberType tables:
SELECT Person.BusinessEntityID,
FirstName, LastName, PhoneNumber, Name as PhoneNumberType
FROM Person.Person
INNER JOIN Person.PersonPhone
ON Person.BusinessEntityID = PersonPhone.BusinessEntityID
INNER JOIN Person.PhoneNumberType
ON PersonPhone.PhoneNumberTypeID = PhoneNumberType.PhoneNumberTypeID
The results appear in Figure 8.2. Notice that data appears from all three tables.
FIGURE 8.2 An inner join that includes data from the Person, PersonPhone, and PhoneNumberType tables.
In the example, the order of the joins is unimportant. The exception to this is when you combine inner and outer joins. When you combine inner and outer joins, the SQL Server engine applies two specific rules. First, the nonpreserved table in an outer join cannot participate in an inner join. The nonpreserved table is the one whose rows may not appear. In the case of a left outer join from Customers to Orders, the Orders table is considered the nonpreserved table. Therefore, it cannot participate in an inner join with OrderDetails. The second rule is that the nonpreserved table in an outer join cannot participate with another nonpreserved table in another outer join.
Creating Outer Joins
An outer join enables you to include rows from one side of the join in the output, regardless of whether matching rows exist on the other side of the join. Two types of outer joins exist: left outer joins and right outer joins. With a left outer join, SQL Server includes in the output all rows in the first table specified in the SELECT statement. Here’s an example:
SELECT Person.BusinessEntityID,
FirstName, LastName, PhoneNumber
FROM Person.Person
LEFT OUTER JOIN Person.PersonPhone
ON Person.BusinessEntityID = PersonPhone.BusinessEntityID
Note
The word OUTER is assumed in the LEFT JOIN clause used when building a left outer join.
In the previous example, people are included regardless of whether they have phone numbers. Notice that if you look at the lower-right corner of Figure 8.3, you see that 19974 rows were returned from the SELECT statement. This is because I added two records to the Person.Person table without adding any records into the Person.PersonPhone table for the people that I added.
FIGURE 8.3 A left outer join that includes records from the Person table regardless of whether they have phone numbers.
Contrast Figure 8.3 with Figure 8.4. The SELECT statement used in Figure 8.4 looks like this:
SELECT Person.BusinessEntityID,
FirstName, LastName, PhoneNumber
FROM Person.Person
INNER JOIN Person.PersonPhone
ON Person.BusinessEntityID = PersonPhone.BusinessEntityID
FIGURE 8.4 An inner join that excludes records from the Person table if those records don’t have associated phone numbers.
Because the SELECT statement contains an inner join, the two people without phone numbers in Person.PersonPhone do not appear in the result set. Therefore, only 19972 rows appear in the result.
With the right outer join shown next, phone numbers are included whether or not they have associated people. If you have properly enforced referential integrity, this scenario should never exist.
SELECT Person.BusinessEntityID,
FirstName, LastName, PhoneNumber
FROM Person.Person
RIGHT OUTER JOIN Person.PersonPhone
ON Person.BusinessEntityID = PersonPhone.BusinessEntityID
Summary
In a world of normalized data, it is important that you understand how to use queries to join your tables back together. This lesson showed you how to join table data. You learned about both inner and outer joins. The examples provided clearly illustrated the differences between them.
Q&A
Q. Why must you join tables together in a query?
A. When you build a system based on normalized table structures, you must join the tables back together to see the data in a useable format.
Q. Explain what an inner join is.
A. An inner join outputs just those rows on the one side of the join that have matching rows on the many side of the join.
Q. Explain what an outer join is.
A. An outer join outputs rows on the one side of the join regardless of whether they have matching rows on the many side of the join.
Q. What is the difference between a left outer join and a right outer join?
A. With a left outer join, SQL Server includes all rows in the output for the first table in the SELECT statement. With a right outer join, SQL Server includes all rows in the output for the second table in the SELECT statement.
Workshop
Quiz
1. When joining a Customer table and an Order table, all customers will always appear (true/false).
2. When joining a Customer table and an Order table with a left outer join, all customers will always appear (true/false).
Quiz Answers
1. False. Only the customers who have associated orders appear.
2. False. This is true if the Customer table appears in the SELECT statement before the Order table.