SQL Part 2: Kal@ittelkom - Ac.id

SQL Part 2
[email protected]
Outline
Obtaining Data from Multiple Tables Cartesian Product Types of Join
Inner Join Outer Join Self Join
Obtaining Data from Multiple Tables

EMPLOYEES DEPARTMENTS
Cartesian Products
When a join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed.
All rows in the first table are joined to all rows in the second table
To avoid a Cartesian product, always include a valid join condition in a WHERE clause.
Generating a Cartesian Product

EMPLOYEES (20 rows) DEPARTMENTS (8 rows)
Cartesian product: 20x8=160 rows
Types of Join
Inner Join
Equijoin Natural Join Cross Join
Outer Join
Left Outer Join Right Outer Join Full Outer Join
Self Join
What is an Equijoin?
EMPLOYEES
DEPARTMENTS
Foreign key
Primary key
EQUIJOIN
To determine an employees department name, you compare the value in the DEPARTMENT_ID column in the EMPLOYEES table with the DEPARTMENT_ID values in the DEPARTMENTS table. The relationship between the EMPLOYEES and DEPARTMENTS tables is an equijointhat is, values in the DEPARTMENT_ID column on both tables must be equal. Note: Equijoins are also called simple joins or inner joins.
Retrieving Records with the USING Clause

SELECT e.employee_id, e.last_name, d.location_id FROM employees e JOIN departments d USING (department_id) ;
Retrieving Records with the ON Clause

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id);
Joining More than Two Tables

EMPLOYEES DEPARTMENTS LOCATIONS
To join n tables together, you need a minimum of n-1 join conditions.
Creating Three-Way Joins with the ON Clause

SELECT FROM JOIN ON JOIN ON employee_id, city, department_name employees e departments d d.department_id = e.department_id locations l d.location_id = l.location_id;
Non-Equijoins
EMPLOYEES JOB_GRADES
Salary in the EMPLOYEES table must be between lowest salary and highest salary in the JOB_GRADES table.
Retrieving Records with Non-Equijoins

SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
Natural Joins
The NATURAL JOIN clause is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in all matched columns. The join can happen only on columns having the same names and data types in both the tables. If the columns have the same name, but different data types, then the NATURAL JOIN syntax causes an error.
Retrieving Records with Natural Joins

SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations ;
Cross Joins
The CROSS JOIN clause produces the cross-product of two tables. This is the same as a Cartesian product between the two tables.
Creating Cross Joins

SELECT last_name, department_name FROM employees CROSS JOIN departments ;
Outer Joins
DEPARTMENTS EMPLOYEES
There are no employees in department 190.
Outer Join
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).
LEFT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ;
RIGHT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ;
FULL OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id) ;
Additional Conditions
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149 ;
Self Join
Sometimes you need to join a table to itself. To find the name of each employees manager, you need to join the EMPLOYEES table to itself, or perform a self join. For example, to find the name of Whalens manager, you need to:
Find Whalen in the EMPLOYEES table by looking at the LAST_NAME column. Find the manager number for Whalen by looking at the MANAGER_ID column. Whalens manager number is 101. Find the name of the manager with EMPLOYEE_ID 101 by looking at the LAST_NAME column. Kochhars employee number is 101, so Kochhar is Whalens manager.
In this process, you look in the table twice. The first time you look in the table to find Whalen in the LAST_NAME column and MANAGER_ID value of 101. The second time you look in the EMPLOYEE_ID column to find 101 and the LAST_NAME column to find Kochhar.
Self Joins
EMPLOYEES (WORKER) EMPLOYEES (MANAGER)
MANAGER_ID in the WORKER table is equal to EMPLOYEE_ID in the MANAGER table.
Joining a Table to Itself

SELECT worker.last_name || ' works for ' || manager.last_name FROM employees worker JOIN employees manager ON (worker.manager_id = manager.employee_id) ;
Review
Obtaining Data from Multiple Tables Cartesian Product Types of Join
Inner Join Outer Join Self Join

SQL Part 2: Kal@ittelkom - Ac.id

Uploaded by

Copyright:

Available Formats

SQL Part 2: Kal@ittelkom - Ac.id

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Part 2: Kal@ittelkom - Ac.id

Uploaded by

Copyright:

Available Formats

SQL Part 2

Obtaining Data from Multiple Tables

Generating a Cartesian Product

Cartesian product: 20x8=160 rows

Retrieving Records with the USING Clause

Retrieving Records with the ON Clause

Joining More than Two Tables

To join n tables together, you need a minimum of n-1 join conditions.

Creating Three-Way Joins with the ON Clause

Retrieving Records with Non-Equijoins

Retrieving Records with Natural Joins

Creating Cross Joins

There are no employees in department 190.

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

MANAGER_ID in the WORKER table is equal to EMPLOYEE_ID in the MANAGER table.

Joining a Table to Itself

You might also like