Banner and the SQL Select Statement: Part Three (Joins)

1
Banner and the SQL Select Statement Part Three
(Joins)

• Mark Holliday
• Department of Mathematics and
• Computer Science
• Western Carolina University
• 4 November 2005 and 11 November 2005
• (updated 11 November 2005)

2
Outline

• The Goal
• The Concepts
• A First Example
• Single Table Selects
• Joins
• Multiple Connected Select Statements

3
A First Example

• Outline
• The Relational Model Single Table
• Lab 1 TOAD, Schema Browser
• Some Structured Query Language (SQL) Basics
• Lab 2 TOAD, SQL Editor

4
Single Table Selects

• Outline
• WHERE clause single condition, multiple
  conditions
• Lab 3
• Order By Aggregate Functions
• Lab 4
• Group By Having
• Lab 5

5
Joins

• Outline
• Why Multiple Tables?
• Inner Joins
• Lab 6
• Outer joins
• Lab 7

6
Why Multiple Tables?
(franz)

• One Table database keeps track of all purchases
  at our store (known as the flat file)

7
Every time a new row is inserted into the table,
all columns will be updated. This results in
unnecessary "redundant data". For example, every
time Wolfgang Schultz purchases something, the
following rows will be inserted into the table
Why Multiple Tables?
(franz)
8
What happens if we ONE DAY find out Wolfgang
Schultzs last name is really spelled
S-h-o-o-l-t-z-e instead of S-c-h-u-l-t-z?
(franz)He has purchased LOTS of stuff from
us.And. He has been making purchases at our
store for over five years. In fact, he is our
very BEST customer. His purchases provide for
most of our gross sales.But now He says that
he will take his business elsewhere if we dont
get this problem corrected!And YOU have to get
it corrected NOW!!!
Why Multiple Tables?
9
Hold that thought !
Why Multiple Tables?
(franz)

• This helps to exemplify WHY relational database
  table structures are so nice ...

10

Why Multiple Tables?
(franz)

• Original flat file table

11

Why Multiple Tables?
(franz)

• For this example, an ideal database would have
  two tables
• One for keeping track of customer information
  (customer_info)
• And the other to keep track of what they purchase
  (purchases).
• customer_info table
• purchases table

12
Our new tables (franz)

• The id number
• is the common
• element that
• ties the tables
• together.

13
Why Multiple Tables? (franz)

• After redesigning our data structure into two
  tables,
• whenever a change for the name or address needs
  to be made to our repeating customer,
• we update the customer_info table only.
• Only the second table, purchases, needs to be
  updated when purchases are made.

14
Why Multiple Tables?
(franz)

• We've just eliminated the space-taking,
  time-consuming, useless redundant data of our
  flat file.
• That is, we've just NORMALIZED this database!
• This is the basis for relational database
  structure.

15
Joins Introduction

• Unfortunately, having multiple tables introduces
  a problem.
• Problem Information you need for a query is
  often in more than one table.
• Example Find the zip code of the person whose
  first name is Ab and last name is Mazlan.
• need the spraddr table for the zip code and
• need the spriden table for the name

16
Joins Introduction

• Solution Need a way to temporarily (just in the
  query) connect a row in one table with the row
  (or rows) in another table that match it.
• Refined Solution Use some type of JOIN operation

17
Joins Introduction

• Problem How do we find the matching rows in
  the other table?
• In other words, how do we do a join?
• Solution
• Conceptually, match each row in the left table
  with every row in the right table.
• take the Cartesian Product of the two tables
• Only keep the row pairs that match.

18
Joins Introduction (Step One of Join)
pidm zip
1 20111
3 20311
pidm last_name
1 smith
2 jones
Cartesian Product
pidm1 Last_name pidm2 zip
1 smith 1 20111
1 smith 3 20311
2 jones 1 20111
2 jones 3 20311
--?
19
Joins Introduction (Step Two of Join)
pidm1 Last_name pidm2 zip
1 smith 1 20111
1 smith 3 20311
2 jones 1 20111
2 jones 3 20311
only keep rows that have pidm1 pidm2
pidm1 Last_name pidm2 zip
1 smith 1 20111
20
Joins Introduction

• Questions
• When does a row from the left table match a
  row from the right table?
• What to do with a row in one table that does not
  match a row in the other table?

21
Joins Introduction

• Question One When does a row from the left table
  match a row from the right table?
• Answer The two rows match if the condition
  expression is TRUE.
• The expression is a sequence of conditions
  connected by AND.
• Each condition is a comparison of a left table
  column and a right table column
• The comparison operator is equality.

22
Joins Introduction

• Example condition expression
• just one condition
• spriden.spriden_pidm spraddr.spraddr_pidm

23
Joins Introduction

• Good relational database design
• gt the only common column in two tables is the
  key for each table
• gt use the key of each table to get from a left
  table row to the matching right table row
• they have the same key value

24
Joins Introduction

• Question Two What to do with a row in one table
  that does not match a row in the other table?
• ignore them inner join
• dont ignore them outer join
• This section covers inner joins

25
INNER Join
(franz)

• An example using reserved words to indicate a
  join
• SELECT spriden_last_name, spriden_first_name,
  spraddr_city, spraddr_stat_code, spraddr_zip
• FROM spriden INNER JOIN spraddr
• ON spriden_pidm spraddr_pidm
• Implicitly,
• take cartesian product of spriden and spraddr
• 2) only keep the rows that meet the condition

26
INNER Join
(franz)

• Syntax for a join using reserved words
• SELECT "list-of-columns
• FROM table1 type JOIN table2
• ON field matching
• WHERE "search-condition(s)"

27
INNER Join
(franz)

• Table spriden

Table spraddr
28
INNER Join
(franz)
SELECT spriden_last_name, spriden_first_name,
spraddr_city, spraddr_stat_code,
spraddr_zip FROM spriden INNER JOIN spraddr ON
spriden_pidm spraddr_pidm
29
Task Select from spriden for the active
record (spriden_change_ind is null). Pull the
permanent mailing address types (MA) in the US
(spraddr_natn_code is null).
INNER Join
(franz)

• This will pull a large amount of data.
• Lets create a smaller subset by picking zip
  codes on the northern East Coast (zip codes
  starting with 0).
• Lets also order our output by the last name then
  first name.

30
INNER Join
(franz)

• To accomplish this we would take the previous SQL
  and make these additions
• SELECT spriden_last_name, spriden_first_name,
  spraddr_city, spraddr_stat_code,
• spraddr_zip
• FROM spriden INNER JOIN spraddr
• ON spriden_pidm spraddr_pidm
• WHERE spriden_change_ind is null
• and spraddr_atyp_code 'MA'
• and spraddr_zip between '000' and '1'
• and spraddr_natn_code is null
• ORDER BY spriden_last_name, spriden_first_name

31
INNER Join
(franz)

• SELECT substr(spriden_last_name,1,12) ', '
  substr(spriden_first_name,1,12),
  rtrim(spraddr_city) ', ' spraddr_stat_code
  ' ' spraddr_zip
• FROM spriden INNER JOIN spraddr
• ON spriden_pidm spraddr_pidm
• WHERE spriden_change_ind is null
• and spraddr_atyp_code 'MA'
• and spraddr_zip between '000' and '1'
• and spraddr_natn_code is null
• ORDER BY spriden_last_name, spriden_first_name

32
INNER Join
(franz)

• Part of the Resultant Subset Would Be

33
If we had left out the check for a null change
indicator, we would have received duplicate rows
of data. (We would get the Gary Abbot record
with a null indicator, and also the one with an
I indicator.)
INNER Join
(franz)

• SELECT substr(spriden_last_name,1,12) ', '
  substr(spriden_first_name,1,12),
  rtrim(spraddr_city) ', ' spraddr_stat_code
  ' ' spraddr_zip
• FROM spriden INNER JOIN spraddr
• ON spriden_pidm spraddr_pidm
• WHERE spraddr_atyp_code 'MA'
• and spraddr_zip between '000' and '1'
• and spraddr_natn_code is null
• spriden_change_ind is null
• ORDER BY spriden_last_name, spriden_first_name

34
INNER Join
(franz)
35
SELF Join

• The left table and the right table in an inner
  join might be the same table.
• This is called a SELF Join.
• When would this be useful?

36
SELF Join
(franz)

• spiffy table

Syntax
SELECT e.first_name EMPLOYEE_FIRST, e.last_name
EMPLOYEE_LAST, m.first_name MANAGER_FIRST,
m.last_name MANAGER_LAST FROM spiffy e INNER JOIN
spiffy m ON e.manager_id m.employee_id
37
SELF Join
SELECT e.first_name EMPLOYEE_FIRST, e.last_name
EMPLOYEE_LAST, m.first_name MANAGER_FIRST,
m.last_name MANAGER_LAST FROM spiffy e INNER JOIN
spiffy m ON e.manager_id m.employee_id

• Previous Inner Join examples
• instances of two different tables joining on the
  same column in both tables (e.g. pidm)
• The Self Inner Join example
• two instances of the same table joining on
  different columns of the same table (e.g.
  manager_id and employee_id)

38
SELF Join
(franz)

• Result

39
Laboratory Six

• Objectives
• Develop competence with inner joins
• Steps
• First Query

40
Laboratory Six

• Problem Find the area code and phone number for
  every one whose last name is Holliday.
• Hint Use the spriden and sprtele tables.

41
Laboratory Six
Solution One SELECT sprtele_phone_area,
sprtele_phone_number FROM spriden INNER JOIN
sprtele ON spriden_pidm sprtele_pidm WHERE
spriden_last_name Holliday Solution
Two SELECT sprtele_phone_area,
sprtele_phone_number FROM spriden, sprtele WHERE
spriden_pidm sprtele_pidm and
spriden_last_name Holliday
42
OUTER Join (franz)

• Notice that spriden has records for Bagnall,
  Baker and Barksdale.
• Spbpers has records for Bagnall and Barksdale,
  but not Baker.

43
OUTER Join (franz)

• If we join the tables by an inner join on pidm,
  we would only receive rows for Bagnall and
  Barksdale.
• To pick up all the records in spriden, and
  join it with the data in spbpers we would use
  an outer join.

44
OUTER Join
(franz)

• OUTER Joins can be split into three types
• LEFT outer join
• RIGHT outer join
• FULL outer join
• Left or Right designates what your base
  table is for the join.
• Full returns all rows in both tables.

45
LEFT OUTER Join
(franz)

• SELECT spriden_last_name, spriden_first_name,
  spbpers_sex, spbpers_birth_date
• FROM spriden LEFT OUTER JOIN spbpers
• ON spriden_pidm spbpers_pidm
• WHERE spriden_change_ind is null
• and spriden_entity_ind 'P'
• ORDER BY spriden_last_name, spriden_first_name

46
LEFT OUTER Join
(franz)

• Result

47
RIGHT OUTER Join
(franz)

• SELECT spriden_last_name, spriden_first_name,
  spbpers_sex, spbpers_birth_date
• FROM spriden RIGHT OUTER JOIN spbpers
• ON spriden_pidm spbpers_pidm
• WHERE spriden_change_ind is null
• and spriden_entity_ind 'P'
• ORDER BY spriden_last_name, spriden_first_name

48
RIGHT OUTER Join
(franz)

• Result

49
FULL OUTER Join
(franz)

• SELECT spriden_last_name, spriden_first_name,
  spbpers_sex, spbpers_birth_date
• FROM spriden FULL OUTER JOIN spbpers
• ON spriden_pidm spbpers_pidm
• WHERE spriden_change_ind is null
• and spriden_entity_ind 'P'
• ORDER BY spriden_last_name, spriden_first_name

50
FULL OUTER Join
(franz)

• Result
• PIDM added to indicate which rows were selected.

51
Laboratory Seven

• Objectives
• Develop competence with outer joins
• Steps
• Outer Join Query

52
JOIN Query (franz)

• Write a JOIN. Use either an INNER, OUTER or
  FULL.
• Use tables SPRIDEN and SPRADDR.
• Include both PIDMs, last name, first name, first
  address line, address type.
• Join on PIDM
• Select the Active name record
• Select entity indicator for a Person
• Select PIDMs less than 70,000
• Select last names that start with Bea
• Order the result set by SPRIDEN PIDM, then
  SPRADDR PIDM

53
Answer (franz)

• SELECT spriden_pidm, spraddr_pidm,
  spriden_last_name, spriden_first_name,
  spraddr_atyp_code, spraddr_street_line1
• FROM spriden LEFT OUTER JOIN spraddr
• ON spriden_pidm spraddr_pidm
• WHERE spriden_change_ind is null
• AND spriden_entity_ind 'P'
• AND spriden_pidm lt 70000
• AND spriden_last_name like 'Bea'
• ORDER BY spriden_pidm, spraddr_pidm

54
Multiple Connected Select Statements

• Outline
• Union, intersection, minus
• Lab 8
• Subqueries
• Use directly FROM clause
• Use as a set new operators
• Use as a single value aggregate functions
• Lab 9
• A Query Development Methodology