Chapter 4 MultipleTable Queries

1
Chapter 4Multiple-Table Queries
2
Objectives

• Retrieve data from more than one table by joining
  tables
• Use the IN and EXISTS operators to query multiple
  tables
• Use a subquery within a subquery
• Use an alias
• Join a table to itself
• Perform set operations (union, intersection and
  difference)
• Use the ALL and ANY operators in a query

3
Querying Multiple Tables

• To retrieve data from two or more tables
• Join the tables
• Formulate a query using the same commands as for
  single tables

4
Joining Two Tables

• Two methods
• Find rows in the two tables that have identical
  values in matching columns
• or
• Use the appropriate conditions in the WHERE clause

5
Example 1

• List the customer number, last name, and first
  name for every customer, together with the sales
  rep number, last name, and fist name for the
  sales rep who represents each customer

6
Create a Join

• Indicate in the SELECT clause all columns to
  display
• List in the FROM clause all tables involved in
  the query
• Give condition(s) in the WHERE clause to restrict
  the data to be retrieved to only those rows that
  have common values in matching columns

7
Qualify Columns

• SLSREP_NUMBER is in both the SALES-REP table and
  the CUSTOMER table, which causes ambiguity among
  column names
• Qualify columns by separating the table name and
  the column name with a period.

8
Joining Tables
9
Example 2

• List the customer number, last name, and first
  name of every customer whose credit limit is
  1,000 together with the sales rep number, last
  name, and first name of the sales rep who
  represents each customer

10
Restricting the Rows in a Join
11
Example 3

• For every part on order, list the order number,
  part number, part description, number of units
  ordered, quoted price, and the unit price

12
Joining the ORDER_LINE and PART Table
13
Comparison of JOIN, IN, and EXISTS

• Include the where condition to ensure that
  matching columns contain equal values
• Similar results are obtained using IN (Chapter 3)
  or EXISTS operator within a subquery

14
Example 4

• Find the descriptions for every part included in
  the order number 12491

15
Restricting Rows Where Joining the ORDER_LINE and
PART Table
16
Using IN Instead of a Join to Query Two Tables
17
Example 5

• Find the order number and order date for every
  order that contains part number BT104

18
Using IN to Select Order Information
19
Using EXISTS to Select Order Information
20
Correlated Subquery

• Figure 4.7 illustrates a correlated subquery
• ORDERS table, listed in the FROM clause of outer
  query is used in the subquery (ORDERS.ORDER_NUMBER
  )
• Qualify ORDER_NUMBER in the subquery

21
Using a Subquery Within a Subquery

• When a subquery is within another subquery, it is
  called a nested subquery

22
Example 6

• Find the order number and order date for every
  order that includes a part located in warehouse
  number 3

23
Nested Subqueries (a Subquery Within a Subquery)
24

Nested Subquery Evaluation

• Step 1
• Innermost subquery is evaluated first, producing
  a temporary table of part numbers located in
  warehouse 3
• Step 2
• Intermediate subquery is evaluated, producing a
  second temporary table with a list of order
  numbers
• Step 3
• Outer query is evaluated last, producing the
  desired list of order numbers and order dates
  using only those orders whose numbers are in the
  temporary table produced in step 2

25
Joining Three Tables
26
Example 7

• List the customer number, order number, order
  date, and order total for every order with a
  total of over 100

27
Comprehensive Example
28
Using an Alias

• Tables listed in the FROM clause can be given an
  alternative name
• Create alias by typing the name of the table
• Press the space bar
• Type the name of the alias (no commas or periods
  are necessary)

29
Example 8

• List the sales rep number, last name, and first
  name for every sales rep together with the
  customer number, last name, and first name for
  each customer the sales rep represents

30
Using Aliases
31
More Complex Joins

• Joining a table to itself
• Joining more than one table in a query

32
Example 9

• Find every pair of customers who have the same
  first and last names

33
Using Aliases to Join a Table to Itself
34
Incorrect Joining of a Table to Itself
35
Joining Four Tables

• For each pair of tables joined, include a
  condition indicating how the columns are related

36
Example 10

• For every part on order, list the part number,
  number ordered, order number, order date,
  customer number, last name, and first name of the
  customer who placed the order, and the last name
  and first name of the sales rep who represents
  each customer

37
Joining Four Tables
38
Construct a Detailed Query

• To construct a detailed query, do the following
• List in the SELECT clause all the columns to be
  displayed (qualify columns)
• List in the FROM clause all table names involved
  in the query
• Take one pair of tables at a time and indicate in
  the WHERE clause the condition that related the
  tables using the AND operator (include any other
  conditions in the WHERE clause and connect them
  to other conditions with the AND operator)

39
Restricting the Rows When Joining Four Tables
40
Set Operations

• Normal set operations
• Union a table containing every row that is in
  either the first table or the second table, or
  both (must be union-compatible same number of
  columns and corresponding columns have identical
  data types and lengths)
• Intersection (intersect) a table containing
  every row that is in both tables
• Difference (minus) set of every row that is in
  the first table but not in the second table

41
Tables of Customers of Sales Rep 12 (TEMP1) and
Customers Who Have Orders (TEMP2)
42
Union, Intersect, and Minus of the TEMP1 and
TEMP2 Tables
43
Example 11

• List the customer number, last name, and first
  name for every customer who is either represented
  by sales rep number 12 or who currently has
  orders on file, or both

44
Using the Union Operator
45
Example 12

• List the customer number, last name, and first
  name for every customer who is represented by
  sales rep number 12 and who currently has orders
  on file

46
Using the Intersect Operator
47
Example 13

• List the customer number, last name, and first
  name for every customer who is either represented
  by sales rep number 12 or who does not have
  orders currently on file

48
Using the Minus Operator
49
ALL and ANY

• ALL and ANY operators are used with subqueries to
  produce a single column of numbers
• ALL condition is true only if it satisfies all
  values produced by the subquery
• ANY condition is true if it satisfies any value
  (one or more) produced by the subquery

50
Example 14

• Find the customer number, last name, first name,
  current balance, and sales rep number for every
  customer whose balance is greater than the
  individual balances of every customer of sales
  rep 12

51
SELECT Command With an ALL Condition
52
Alternative to ALL Condition
53
Example 15

• Find the customer number, last name, first name,
  current balance, and sales rep number of every
  customer whose balance is larger than the
  balances of at least one customer of sales rep
  number 12

54
SELECT Command With an ANY Condition
55
Alternative to ANY Condition
56
Summary

• To join tables together, indicate in the SELECT
  clause all columns to display, list in the FROM
  clause all tables to join, and then include in
  the WHERE clause any conditions requiring values
  in matching columns to be equal
• When referring to matching columns in different
  tables, you must qualify the column names to
  avoid confusion
• You qualify column names using the following
  format table name.column name
• Use the IN operator or the EXISTS command with an
  appropriate subquery as an alternate way of
  performing a join

57
Summary

• A subquery can contain another subquery. The
  innermost subquery is executed first
• The name of a table in a FROM clause can be
  followed by an alias, which is an alternate name
  for the table
• The alias can be used in place of the table name
  throughout the SQL command
• By using two different aliases for the same table
  in a single SQL command, you can join a table to
  itself

58
Summary

• The UNION command creates a union of two tables
  that is, the collection of rows that are in
  either or both tables
• The INTERSECT command creates the intersection of
  two tables that is, the collection of rows that
  are in both tables
• The MINUS command creates the difference of two
  tables that is, the collection of rows that are
  in the first table but not in the second table
• To perform any of these operation, the tables
  must be union-compatible

59
Summary

• Two tables are union-compatible if they have the
  same number of columns, and if their
  corresponding columns have identical data types
  and lengths
• If a subquery is preceded by the ALL command, the
  condition is true only if it is satisfied by all
  values produced by the subquery
• If a subquery is preceded by the ANY command, the
  condition is true if it is satisfied by any value
  (one or more) produced by the subquery