A Guide to SQL, Ninth Edition Page 5–1
Chapter 5
Multiple-Table Queries
At a Glance
Instructor’s Manual Table of Contents
• Overview
• Objectives
• Teaching Tips
• Quick Quizzes
• Class Discussion Topics
• Additional Projects
• Additional Resources
• Key Terms
A Guide to SQL, Ninth Edition Page 5–2
Lecture Notes
Overview
In this chapter, students learn how to retrieve data from two or more tables using one SQL
command. Students join tables and examine how similar results are obtained using the IN
and EXISTS operators. Then, students use aliases to simplify queries and join a table to
itself. Students also implement the set operations of union, intersection, and difference
using SQL commands. They examine two related SQL operators: ALL and ANY. Finally,
students perform inner joins, outer joins, and products.
Chapter Objectives
In this chapter, students learn about:
• Using joins to retrieve data from more than one table
• Using the IN and EXISTS operators to query multiple tables
• Using a subquery within a subquery
• Using an alias
• Joining a table to itself
• Performing set operations (union, intersection, and difference)
• Using the ALL and ANY operators in a query
• Performing special operations (inner join, outer join, and product)
Teaching Tips
Querying Multiple Tables
1. Many queries require the retrieval of data from two or more tables. This type of
retrieval requires that the tables be joined.
3. Point out the Note on page 133.
Joining Two Tables
2. Use Example 1 and Figure 5-1 to illustrate joining two tables using the WHERE clause.
(1) In the SELECT clause, list all columns you want to display.
(3) In the WHERE clause, list the condition that restricts the data to be retrieved to only
©2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part,
except for use as permitted in a license distributed with a certain product or service or otherwise on a password–
protected website for classroom use.
4. When you join tables, it is often necessary to qualify column names because the same
5. Use the embedded Q&A on page 135 to check students’ understanding of join.
7. Use Example 3 and Figure 5-3 to illustrate another join.
9. Review the Embedded Q&A on page 137.
Teaching
Tip
Spend some time reviewing the relationships in the TAL Distributors, Colonial
Adventure Tours, and Solmaris Condominium Group databases. Students need to
really understand that a join matches the foreign key in one table with the
primary key in another table.
Comparing Joins, In, and Exists
1. In SQL, tables are joined by including a condition in the WHERE clause to ensure that
matching columns contain equal values.
2. Point out that there are two other ways to achieve similar results: the IN operator
4. Point out that all tables involved in a join must appear after the FROM clause even if no
data from the table is displayed.
Using the IN Operator
1. An alternate way to join tables is to use the IN operator with a subquery.
3. Point out that the query results are identical to those shown in Figure 5-4.
Using the EXISTS Operator
2. Use Example 5 and Figure 5-6 to illustrate the IN operator.
4. Use Figure 5-7 to describe a correlated subquery. In a correlated query, a table is
listed in the outer query and used in the subquery.
A Guide to SQL, Ninth Edition Page 5–4
Using a Subquery within a Subquery
1. A nested subquery is a subquery within a subquery.
3. Mention that SQL evaluates queries from the innermost query to the outermost.
4. Use Figure 5-8 to describe how SQL first retrieves item numbers for those items located
6. Discuss the role of built-in optimizations that analyze queries to determine the best way
to satisfy them.
A Comprehensive Example
1. Use Example 7 and Figure 5-10 to illustrate using many of the features of the SELECT
statement. The example joins two tables using a WHERE clause, groups records using
Using an Alias
1. When tables are listed in the FROM clause, you can give each table an alias; that is, an
alternate name that you can use in the rest of the statement.
2. To create an alias, type the name of the table, press the Spacebar, and then type the
4. Mention the Note on page 143.
Joining a Table to Itself
1. A self-join joins a table to itself. Self-joins are useful for tables with a self-referencing
3. Review the Embedded Q&A on page 145 and use Figure 5-13 to test students’
understanding of self-joins.
Using a Self-Join on a Primary Key Column
1. A self-join can involve the primary key of a table. This occurs when a foreign key in the
table is the same as the primary key of the table; for example, in an Employee table,
EMPLOYEE_NUM may be the primary key, and MGR_EMPLOYEE_NUM could be
2. Use Figure 5-14 to illustrate a table of employee data where this occurs.
3. Use Figure 5-15 to illustrate using a self-join on a primary key.
Joining Several Tables
1. To join several tables, include a condition for each pair of tables to indicate how the
columns are related.
2. Use Example 10 and Figure 5-16 to illustrate joining four tables. Because there are four
4. To construct a detailed query in a step-by-step fashion, do the following:
(2) List all the tables involved in the query following the FROM clause. Even though
(3) For each pair of related tables, indicate in the WHERE clause the condition that
5. Use Figure 5-17 to illustrate restricting retrieval when joining four tables.
Teaching
Tip
To help students construct queries with several tables, give them the following
hint. Count the number of tables following the FROM clause. Subtract 1. That is
the number of conditions that you need to join the tables.
Quick Quiz 1
1. To retrieve data from more than one table, _____ the tables.
2. A(n) _____ subquery is a subquery within a subquery.
3. A(n) _____ is an alternate name given to a table.
Set Operations
1. In SQL, union, intersection, and difference are set operations.
3. The intersection (intersect) of two tables is a table containing all rows that are in both
tables.
4. The difference (minus) of two tables is the set of all rows that are in the first table but
that are not in the second table.
6. Point out that, for a union to be appropriate, the two tables must be union-compatible;
that is, they must have the same structure.
8. Mention that, if the SQL implementation truly supports the union operation, it will
9. Use Example 12 and Figure 5-21 to illustrate the INTERSECT operator.
11. Point out the Note on page 153. Microsoft Access does not support the INTERSECT
operator.
13. Use Example 13 and Figure 5-24 to illustrate a different approach when you cannot use
the MINUS operator.
14. Point out the Note on page 154. SQL Server and Microsoft Access do not support the
MINUS operator.
Teaching
Tip
Relational databases and SQL are based on concepts from relational algebra and
set theory. For more about these topics, see the Additional Resources section.
Quick Quiz 2
1. The _____ of two tables is a table containing every row that is in either the first table, the
second table, or both tables.
2. The _____ of two tables is a table containing all rows that are in both tables.
3. The _____ of two tables is a set of all rows that are in the first table but that are not in the
second table.
All and Any
2. If you precede the subquery with the ALL operator, the condition is true only if it satisfies
all values produced by the subquery.
©2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part,
except for use as permitted in a license distributed with a certain product or service or otherwise on a password–
protected website for classroom use.
4. Use Example 14 and Figure 5-25 to illustrate a SELECT command with an ALL condition.
5. Use the Embedded Q&A on page 155 and Figure 5-26 to explain how to achieve the same
result without using the ALL operator.
6. Use Example 15 and Figure 5-27 to illustrate a SELECT command with an ANY condition.
Special Operations
1. Special operations within SQL include: self-join (previously discussed), inner join, outer
Inner Join
2. Use Example 16 to illustrate an inner join.
Outer Join
1. An outer join is a join that lists all the rows from one of the tables in the join, regardless of
2. In a left outer join, all rows from the table on the left (the table listed first in the query)
4. In a full outer join, all rows from both tables will be included, regardless of whether they
5. Use Example 17 and Figure 5-30 to illustrate a left outer join.
Product
1. The product (Cartesian Product) of two tables is the combination of all rows in the first
table and all rows in the second table.
3. Point out that a Cartesian product sometimes inadvertently results when an SQL command
to join two tables is coded incorrectly.
Class Discussion Topics
1. You can join tables using the WHERE clause, the EXISTS operator, or the IN clause.
Which one is the easiest for you to understand? Why?
2. The OR and AND operators are used in compound conditions. Is there any relationship
between these operators and the ALL and ANY operators?
Additional Projects
1. Other types of joins mentioned in database texts are: equi-join, natural join, and cross
Additional Resources
1. Concepts of Database Management, Eighth Edition by Philip Pratt and Mary Last
2. Relational Algebra: http://db.grussell.org/section010.html
4. SQL Tutorial: http://www.tutorialspoint.com/sql/
Key Terms
➢ alias: An alternate name for a table
➢ ALL: Operator that precedes a subquery to make a condition true only if it satisfies all
values produced by the subquery
that satisfy the condition in the WHERE clause
➢ INTERSECT: Operator that creates a temporary table containing all rows that are in both
tables
➢ intersection: A table that contains all rows that are in the two tables
➢ join: The process of combining two or more tables by finding rows in the tables that have
A Guide to SQL, Ninth Edition Page 5–9
©2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part,
except for use as permitted in a license distributed with a certain product or service or otherwise on a password–
protected website for classroom use.
➢ MINUS: Operator that creates a temporary table containing the set of all rows that are in
the first table but that are not in the second table
➢ nested subquery: A subquery with a subquery
➢ outer join: A join in which all rows from one table in a join is listed, regardless of whether
they match any rows in the other table
➢ product: The combination of all rows in the first table and all rows in the second table
➢ right outer join: A join in which all rows from the table on the right will be included,
regardless of whether they match rows from the table on the left
➢ self-join: The process of joining a table to itself
➢ union: A table containing every row that is in either the first table, the second table, or both
tables