A Guide to SQL, Seventh Edition

1

• A Guide to SQL, Seventh Edition

2
Objectives

• Use joins to retrieve data from more than one
  table
• Use the IN and EXISTS operators to query multiple
  tables
• Use a subquery within a subquery
• Use an alias

A Guide to SQL, Seventh Edition
3
Objectives

• Join a table to itself
• Perform set operations (union, intersection, and
  difference)
• Use the ALL and ANY operators in a query
• Perform special operations (inner join, outer
  join, and product)

A Guide to SQL, Seventh Edition
4
Querying Multiple Tables

• When querying more than one table, the tables
  must be joined
• Join tables by finding columns with matching data
• Join tables by using a condition in the WHERE
  clause

A Guide to SQL, Seventh Edition
5
Joining Two Tables

• In the SELECT clause, list all columns you want
  to display
• In the FROM clause, list all tables involved in
  the query
• In the WHERE clause, restrict to the rows that
  have common values in matching columns

A Guide to SQL, Seventh Edition
6
A Guide to SQL, Seventh Edition
7
A Guide to SQL, Seventh Edition
8
A Guide to SQL, Seventh Edition
9
Comparing JOIN, IN, and EXISTS

• Tables can be joined using IN or EXISTS clause
• Use IN operator with a subquery
• Use the EXISTS operator to retrieve data from
  more than one table

A Guide to SQL, Seventh Edition
10
A Guide to SQL, Seventh Edition
11
A Guide to SQL, Seventh Edition
12
A Guide to SQL, Seventh Edition
13
A Guide to SQL, Seventh Edition
14
Correlated Subquery

• Subquery involves a table listed in the outer
  query
• In Figure 4.7 the ORDERS table, listed in the
  FROM clause of the outer query, is used in the
  subquery
• You need to qualify ORDER_NUM column in subquery
  as ORDERS.ORDER_NUM

A Guide to SQL, Seventh Edition
15
Correlated Subquery

• For each row in the ORDERS table
• Subquery executed using the value of
  ORDERS.ORDER_NUM that appears in the row
• The inner query makes a list of rows in the
  ORDER_LINE table
• Where ORDER_LINE.ORDER_NUM matches this value and
• In which PART_NUM is equal to DR93

A Guide to SQL, Seventh Edition
16
Using a Subquery within a Subquery

• A nested subquery is a subquery within a subquery
• SQL evaluates the queries from the innermost
  query to the outermost
• It is possible that there is more than one
  approach to formulation of the queries
• Many DMBS have optimizers that analyze queries
  for efficiency

A Guide to SQL, Seventh Edition
17
A Guide to SQL, Seventh Edition
18
Using an Alias

• An alias is an alternate name for a table
• Used when tables are listed in the FROM clause
• Created by typing the name of the table, hitting
  a space, then typing the name of the alias
• Allows for simplicity

A Guide to SQL, Seventh Edition
19
Joining a Table to Itself

• A self-join is when you are joining a table to
  itself
• A second use for using an alias
• Used when comparing records within one table
• Alias allows you to treat one table as two
  separate tables

A Guide to SQL, Seventh Edition
20
A Guide to SQL, Seventh Edition
21
Using a Self-Join on a Primary Key

• It is possible to create a self-join that
  involves the primary key of the table
• Just as in previous examples, you would list the
  table twice in the FROM clause with aliases

A Guide to SQL, Seventh Edition
22
A Guide to SQL, Seventh Edition
23
Joining Several Tables

• Condition shows how the columns are related for
  each pair of tables

A Guide to SQL, Seventh Edition
24
Joining Several Tables Step-By-Step

• In the SELECT clause list all the columns to
  display
• Qualify the column name if needed
• In the FROM clause list all tables
• Include tables used in the WHERE clause, even if
  they are not in the SELECT clause

A Guide to SQL, Seventh Edition
25
Joining Several Tables Step-By-Step

• Take one pair of related tables at a time
• Indicate in the WHERE clause the condition that
  relates the tables
• Join conditions with the AND operator
• Include any additional conditions in the WHERE
  clause
• Connect them with the AND operator

A Guide to SQL, Seventh Edition
26
Set Operations

• Set operations are used for taking the union,
  intersection, and differences of two tables
• The union of two tables is a table containing
  every row that is in either the first table, the
  second table, or both tables

A Guide to SQL, Seventh Edition
27
Set Operations

• The intersection (intersect) of two tables is a
  table containing all rows that are in both tables
• The difference (minus) of two tables is the set
  of all rows that are in the first tables but are
  not in the second table

A Guide to SQL, Seventh Edition
28
Restrictions to Set Operations

• Requirements for tables to be union compatible
• Have the same number of columns
• AND
• Their corresponding columns have identical data
  types and lengths

A Guide to SQL, Seventh Edition
29
Special Operations

• Inner Join
• A join that compares the tables in the FROM
  clause and lists on those rows that satisfy the
  condition in the WHERE clause
• Outer Join
• A join that lists all the rows from one of the
  tables in a join, regardless of matching

A Guide to SQL, Seventh Edition
30
Outer Joins

• Left outer join all rows from the table on the
  left (listed first in the query) will be
  included matching rows only from the table on
  the right will be included
• Right outer join all rows from the table on the
  right will be included matching rows only from
  the table on the left will be included
• Full outer join all rows from both tables will
  be included regardless of matches

A Guide to SQL, Seventh Edition
31
Product

• The product (Cartesian Product) of two tables is
  the combination of all rows in the first table
  and all rows in the second table
• Omit the WHERE clause to form a product

A Guide to SQL, Seventh Edition
32
A Guide to SQL, Seventh Edition
33
Summary

• Join tables with various methods
• A subquery can contain another subquery
• An alias can be used to simplify the SQL command
  as well to create self join
• UNION, INTERSECT, MINUS commands are introduced
• To form a product of two tables, include both
  tables in the FROM clause and omit the WHERE
  clause

A Guide to SQL, Seventh Edition