Database Programming

1
Database Programming

• Sections 4 Joins

2
Overview

• Oracle Proprietary Joins (8i and prior)
• Cartesian Product
• Equijoin
• Non-equijoin
• Outer join
• Self join

• SQL 1999 Compliant Joins
• Cross joins
• Natural joins
• Using clause
• Full or two sided outer joins
• Arbitrary join conditions for outer joins

3
Cross Join

• ANSI/ISO SQL 1999 syntax for achieving of a
  Cartesian product
• Syntax
• SELECT FROM employeesCROSS JOIN departments
• Last weekSELECT FROM employees, departments

4
Natural Join

• ANSI/ISO SQL 1999 Join equivalent of an equijoin
• Join on all common columnsie. columns with same
  name and data type
• SYNTAXSELECT field1, field2FROM table1 NATURAL
  JOIN table2WHERE fieldn value

5
Example 4.2.3

• SELECT event_id, song_id, cd_numberFROM
  d_play_list_items NATURAL JOIN d_track_listingsWH
  ERE event_id 105

6
Example Natural Join

• SELECT employee_id, last_name, department_id,
  location_idFROM employeesNATURAL JOIN
  departments

7
4.2.6.5

• 5. Use an equijoin between the DJs on Demand
  database tables, d_songs and d_types. Display the
  type code, description and title. Limit the rows
  returned to those type codes between 70 and 80.

8
Join Using

• ANSI/ISO SQL 1999 join condition used to join
  two tables on only one column
• Typically used where NATURAL JOIN cannot be used
  because there are other common columns with same
  name and different data types
• No table name or alias can be used on the
  referenced column anywhere in the statement

9
Join Using

• SYNTAXSELECT field1, field2, field3FROM
  table1JOIN table2USING(column name)
• ExampleSELECT e.employee_id, e.last_name,
  d.location_idFROM employees eJOIN departments
  dUSING(department_id)

10
Example

• SELECT e.employee_id, e.last_name,
  d.location_idFROM employees e JOIN departments
  dUSING(department_id)

11
JOIN ON

• ANSI/ISO 1999 join clause that may be used to
  specify the condition or columns used
• SyntaxSELECT field1, field2, field3FROM
  table1 JOIN table2ON(table1.fieldxtable2.fieldy)
  SELECT e.last_name emp, m.last_name mgrFROM
  employees e JOIN employees mON(e.manager_id
  m.employee_id)
• SELECT e.last_name as "EMP", w.last_name as
  "MGRFROM employees e JOIN employees wON
  (e.manager_id w.employee_id)WHERE e.last_name
  like 'H'Where clause can limit results.

12
Example of JOIN ON

• SELECT e.last_name, e.department_id,
  d.department_nameFROM employees e JOIN
  departments dON (e.department_id
  d.department_id)

13
Summary/Comparison
14
Examples 4.3.6-8

• 2. Join DJs on Demand d_play_list_items,
  d_track_listings, and d_cds tables with the JOIN
  USING syntax. Include the song ID, CD number,
  title, and comments in the output.
• 3. Display the city, department name, location
  ID, and department ID for departments 10, 20, and
  30 for the city of Seattle.
• 6. Display job title, employee first name, last
  name, and email for all employees that are stock
  clerks.
• 9. (Use Join On) Query and display manager ID,
  department ID, department name, first name, and
  last name for all employees in departments 80,
  90, 110, and 190.

15
Three-way Joins with the ON clause

• A three-way join is the join of three tables
• SyntaxSELECT employee_id, city,
  department_nameFROM employees eJOIN departments
  dON (d.department_id e.department_id)JOIN
  locations lON (d.location_idl.location_id)

16
Example

• SELECT employee_id, city, department_nameFROM
  employees eJOIN departments dON
  (d.department_id e.department_id)JOIN
  locations lON (d.location_idl.location_id)

17
Revised example

• SELECT e.employee_id, l.city, d.department_nameFR
  OM employees e, departments d, locations lWHERE
  e.department_id d.department_idAND
  d.location_id l.location_id

18
INNER JOINS

• An inner join returns only those rows that match
  the join conditionSELECT e.last_name,
  e.department_id, d.department_nameFROM employees
  eJOIN departments dON (e.department_id
  d.department_id)

19
OUTER JOIN

• Outer joins return those rows that match the join
  condition and those that do not
• There are three ANSI/ISO SQL 1999 outer joins
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

20
LEFT OUTER JOIN

• SELECT e.last_name, e.department_id,
  d.department_nameFROM employees eLEFT OUTER
  JOIN departments dON(e.department_idd.department
  _id)
• SQL 99 equivalentSELECT e.last_name,
  e.department_id, d.department_nameFROM employees
  e, departments dWHERE e.department_idd.departmen
  t_id()
• This statement will return those employees who do
  not have a department_id

21
RIGHT OUTER JOIN

• SELECT field1, field2 ....FROM table1 aRIGHT
  OUTER JOIN table2 bON (a.fieldb.field)or
  USING(field name)
• SELECT e.last_name, e.department_id,
  d.department_nameFROM employees eRIGHT OUTER
  JOIN departments dON(e.department_idd.department
  _id)
• This statement will return those departments who
  do not have any employees in them

22
FULL OUTER JOIN

• The FULL OUTER JOIN returns both matched and all
  unmatched rows
• SyntaxSELECT field1, field2, field3FROM table1
  aFULL OUTER JOIN table2 bON a.fieldb.fieldSE
  LECT e.last_name, e.department_id,
  d.department_nameFROM employees eFULL OUTER
  JOIN departments dON(e.department_idd.department
  _id)
• There is no direct comparable Oracle specific join

23
Example 4.4.5

• Construct a join to display a list of Global Fast
  Foods customers whether or not they have placed
  an order as yet and all the customers who have
  placed orders.
• SELECT c.first_name, c.last_name,
  o.order_number,o.order_date, o.order_totalFROM
    f_customers cLEFT OUTER JOIN f_orders oON
    (c.id o.cust_id)

24
Types of Joins

• Oracle Proprietary Joins (8i and prior)
• Cartesian Product
• Equijoin
• Non-equijoin
• Outer join
• Self join

• SQL 1999 Compliant Joins
• Cross joins
• Natural joins
• Using clause
• Full or two sided outer joins
• Arbitrary join conditions for outer joins