Chapter 6: Multiple Tables I

1
Chapter 6 Multiple Tables I

• Data Retrieval from Multiple Tables
• Various Types of Joins

2
Join

• Join operation is used to extract data from
  various tables.
• The join involves multiple tables and join
  conditions.
• The join condition involves common attributes
  from two tables. In most cases, the
  involved attributes are primary key from one
  table and foreign key from another.
• A SELECT query with multiple table and no join
  condition results in a Product.
• A product matches each row from one table with
  each and every row in the second table. A
  product between a table with X rows and a table
  with Y rows, results into X Y rows.
• Types of joins are Equijoin, Nonequijoin/Conditio
  njoin, Outerjoin and Selfjoin.

3
Equijoin

• Equijoin is a join involving common attributes
  from two tables.
• The common attributes are usually primary key
  from one table and foreign key from another.
• The join condition uses an equal sign, hence the
  name.
• The column names use table name qualifier, which
  is not necessary for all columns, but is
  essential for column names that appear in more
  than one table. For example, DeptId appears in
  employee and dept tables.
• To join N tables, N-1 join conditions are needed.
• There is a limit on the number of join conditions
  with a query. For Oracle 8 the limit is 4.

4
Example to Equijoin
SQLgt select from student STUDE LAST FIRST
CITY ST ZIP STAR BIRTHDATE FACULTYID
MAJORID PHONE STREET ----- --------
---------- --------- -- ----- ---- ---------
---------- ------- ---------- ----------------- 00
100 Diaz Jose Hill NJ 08863 WN00
12-FEB-80 123 100 9735551111 1 Ford
Avenue 7 00101 Tyler Mickey Bronx NY
10468 SP00 18-MAR-75 555 500
7185552222 12 Morris Avenue 00102 Patel
Rajesh Edison NJ 08837 WN00 12-DEC-82
111 400 7325553333 25 River Road 3 00103
Rickles Deborah Iselin NJ 08838 FL00
20-OCT-70 555 500 7325554444 100 Main
Street 00104 Lee Brian Hope NY
11373 WN00 28-NOV-75 345 600
2125555555 2845 First Lane 00105 Khan Amir
Clifton NJ 07222 WN00 07-JUL-81 222
200 2015556666 213 Broadway 6 rows selected.

• List all student with their advisor

SQLgt select from faculty FACULTYID NAME
ROOMID PHO DEPTID ---------- --------- ------ ---
------ 111 Jones 11 525 1
222 Williams 20 533 2 123
Mobley 11 529 1 235 Vajpayee
12 577 2 345 Sen 12 579
3 444 Rivera 21 544 4
555 Chang 17 587 5 333
Collins 17 599 3 8 rows selected.
SQLgt SELECT student.Last, student.First, faculty.N
ame, faculty.Phone FROM student, faculty WHERE
student.FacultyId faculty.FacultyId LAST
FIRST NAME PHO -------- --------
--------- --- Diaz Jose Mobley
529 Tyler Mickey Chang 587 Patel
Rajesh Jones 525 Rickles Deborah Chang
587 Lee Brian Sen 579 Khan
Amir Williams 533 6 rows selected.
5
One more example to Equijoin
SQLgt select employee.Lname, employee.Fname,
dept.DeptName, 2 position.PosDesc from
employee, dept, position 3 where
employee.DeptId dept.DeptId 4 and
employee.PositionId position.PositionId LNAME
FNAME DEPTNAME
POSDESC --------------- ---------------
------------ ---------- Smith John
Finance President Houston
Larry Marketing Manager Roberts
Sandi Finance Manager McCall
Alex InfoSys Programmer Dev
Derek InfoSys
Manager Shaw Jinku Sales
Salesman Garner Stanley Sales
Manager Chen Sunny
Finance Accountant 8 rows selected.

• List all employee with their department and their
  position in the department

6
Table Aliases

• Table aliases are used to shorten table names
  within a query.
• A table alias can be up to 30 characters long!
• For example,
• SELECT e.Lname, e.Fname, d.DeptName, p.posDesc
• FROM employee e, dept d, position p
• WHERE e.DeptId d.DeptId
• AND e.PositionId p.PositionId
• In this example, e, d and p are table aliases,
  which are defined in the FROM clause, but are
  available in all other clauses. The example also
  contains join of 3 tables with 2 join conditions.

7
Additional Conditions with Join

• In additon to join conditions, one may use
  additional conditions using the AND operator to
  restrict information.
• Example
• SQLgt SELECT e.Lname, e.Fname,
• 2 d.DeptName, p.PosDesc
• 3 FROM employee e, dept d, position p
• 4 WHERE e.DeptId d.DeptId
• 5 AND e.PositionId p.PositionId
• 6 AND e.DeptId 10
• LNAME FNAME DEPTNAME POSDESC
• ------- -------- --------- ---------
• Smith John Finance President
• Roberts Sandi Finance Manager
• Chen Sunny Finance Accountant
• 3 rows selected.

8
Non-equijoin
SQLgt select from employee EMPLOYEEID LNAME
FNAME POSITIONID SUPERVISOR HIREDATE
SALARY COMMISSION DEPTID QUALID ----------
--------- --------- ---------- ----------
--------- ------- ---------- ------- -------
111 Smith John 1
15-APR-60 265000 35000 10 1
246 Houston Larry 2 111
19-MAY-67 150000 10000 40 2
123 Roberts Sandi 2 111
02-DEC-91 75000 10 2
433 McCall Alex 3 543
10-MAY-97 66500 20 4
543 Dev Derek 2 111
15-MAR-95 80000 20000 20 1
200 Shaw Jinku 5 135
03-JAN-00 24500 3000 30 135
Garner Stanley 2 111
29-FEB-96 45000 5000 30 5
222 Chen Sunny 4 123
15-AUG-99 35000 10 3 8
rows selected.

• A non-equijoin contains a join condition that
  does not use equality operator.
• In sample database, employee and salarylevel
  tables have no common attribute, but employees
  salary can be compared to LowerLimit and
  UpperLimit attributes to get employees LevelNo.
• For example list all employee with their
  salarys level.

SQLgt select from salarylevel LEVELNO
LOWERLIMIT UPPERLIMIT ---------- ----------
---------- 1 1 25000
2 25001 50000 3 50001
100000 4 100001 500000
9
Example to Non-equijoin

• SQLgt SELECT e.Lname, e.Fname, s.LevelNo
• 2 FROM employee e, salarylevel s
• 3 WHERE e.Salary BETWEEN s.LowerLimit AND
  s.UpperLimit
• LNAME FNAME LEVELNO
• ------ ------ --------
• Smith John 4
• Houston Larry 4
• Roverts Sandi 3
• McCall Alex 3
• Dev Derek 3
• Shaw Jinku 1
• Garner Stanley 2
• Chen Sunny 2
• 8 rows selected.

10
Outer Join

• In equijoin, the rows with matching values are
  joined, but a row with no match is not joined.
• The outer join returns all marched rows as well
  as rows with no match.
• The table that does not contain the matching
  value is known as the deficient table.
• The outer join uses () operator on the deficient
  tables side of the join condition.
• The table with the foreign key in the join is
  always the deficient table.
• Why ?
• The () operator can appear on any one side of
  the join condition, but not on both sides of a
  join condition.
• For example list all students with their advisor
  including those who does not have any student.

11
Example to Outer Join

• SQLgt SELECT s.Last ', ' s.First AS
  STUDENT,
• 2 f.Name AS ADVISOR
• 3 FROM student s, faculty f
• 4 WHERE s.FacultyId () f.FacultyId
• STUDENT ADVISOR
• -------------------------------- ---------------
• Patel, Rajesh Jones
• Diaz, Jose Mobley
• Khan, Amir Williams
• , Vajpayee
• , Collins
• Lee, Brian Sen
• , Rivera
• Tyler, Mickey Chang
• Rickles, Deborah Chang
• 9 rows selected.

12
Self-join

• A self-join joins a table to itself.
• A self-join is possible on a table with two or
  more attributes with same domain.
• A self-join uses more than one copy of the same
  table by employing different aliases for it.
• For example, list all employees with their
  supervisor

13
SQLgt select from employee EMPLOYEEID LNAME
FNAME POSITIONID SUPERVISOR HIREDATE
SALARY COMMISSION DEPTID QUALID ----------
--------- --------- ---------- ----------
--------- ------- ---------- ------- -------
111 Smith John 1
15-APR-60 265000 35000 10 1
246 Houston Larry 2 111
19-MAY-67 150000 10000 40 2
123 Roberts Sandi 2 111
02-DEC-91 75000 10 2
433 McCall Alex 3 543
10-MAY-97 66500 20 4
543 Dev Derek 2 111
15-MAR-95 80000 20000 20 1
200 Shaw Jinku 5 135
03-JAN-00 24500 3000 30 135
Garner Stanley 2 111
29-FEB-96 45000 5000 30 5
222 Chen Sunny 4 123
15-AUG-99 35000 10 3 8
rows selected.
SQLgt SELECT e.Lname ' reports to ' s.Lname
2 FROM employee e, employee s 3 WHERE
e.Supervisor s.EmployeeId E.LNAME'REPORTSTO'
S.LNAME ----------------------------------------
-- Houston reports to Smith Roberts reports to
Smith McCall reports to Dev Dev reports to
Smith Shaw reports to Garner Garner reports to
Smith Chen reports to Roberts 7 rows selected.