sql 2[join]

7/30/2019 SQL 2[join]

1/28

SQL Part 2

[email protected]

7/30/2019 SQL 2[join]

2/28

Outline

Obtaining Data from Multiple Tables

Cartesian Product

Types of Join Inner Join

Outer Join

Self Join

7/30/2019 SQL 2[join]

3/28


EMPLOYEES DEPARTMENTS

7/30/2019 SQL 2[join]

4/28

Cartesian Products

When a join condition is invalid or omittedcompletely, 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.

7/30/2019 SQL 2[join]

5/28

Generating a Cartesian Product

Cartesian

product:

20x8=160 rows

EMPLOYEES (20 rows) DEPARTMENTS (8 rows)

7/30/2019 SQL 2[join]

6/28

Types of Join

Inner Join

Equijoin

Natural Join

Cross Join

Outer Join Left Outer Join

Right Outer Join Full Outer Join

Self Join

7/30/2019 SQL 2[join]

7/28

What is an Equijoin?EMPLOYEES DEPARTMENTS

Foreign key Primary key

7/30/2019 SQL 2[join]

8/28

EQUIJOIN

To determine an employees departmentname, you compare the value in theDEPARTMENT_ID column in the EMPLOYEEStable with the DEPARTMENT_ID values in theDEPARTMENTS table.

The relationship between the EMPLOYEESand DEPARTMENTS tables is an equijointhatis, values in the DEPARTMENT_ID column onboth tables must be equal.

Note:Equijoins are also called simple joinsor inner joins.

7/30/2019 SQL 2[join]

9/28

SELECT e.employee_id, e.last_name, d.location_id

FROM employees e JOIN departments d

USING (department_id) ;

Retrieving Records with the

USING Clause

7/30/2019 SQL 2[join]

10/28

SELECT e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id


ON (e.department_id = d.department_id);

Retrieving Records with the ON

Clause

7/30/2019 SQL 2[join]

11/28

Joining More than Two TablesEMPLOYEES LOCATIONSDEPARTMENTS

To join n tables together, you need a

minimum of n-1 join conditions.

7/30/2019 SQL 2[join]

12/28

Creating Three-Way Joins with

the ON ClauseSELECT employee_id, city, department_nameFROM employees eJOIN departments d

ON d.department_id = e.department_id

JOIN locations l

ON d.location_id = l.location_id;

7/30/2019 SQL 2[join]

13/28

Non-EquijoinsEMPLOYEES JOB_GRADES

Salary in the EMPLOYEES

table must be between

lowest salary and highest

salary in the JOB_GRADES

table.

7/30/2019 SQL 2[join]

14/28

Retrieving Records

with Non-EquijoinsSELECT 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;

7/30/2019 SQL 2[join]

15/28

Natural Joins

The NATURAL JOIN clause is based on allcolumns in the two tables that have the samename.

It selects rows from the two tables that have

equal values in all matched columns. The join can happen only on columns havingthe same names and data types in both thetables.

If the columns have the same name, butdifferent data types, then the NATURAL JOINsyntax causes an error.

7/30/2019 SQL 2[join]

16/28

SELECT department_id, department_name,

location_id, city

FROM departments

NATURAL JOIN locations ;

Retrieving Records with Natural

Joins

7/30/2019 SQL 2[join]

17/28

Cross Joins

The CROSS JOIN clause produces thecross-product of two tables.

This is the same as a Cartesian product

between the two tables.

7/30/2019 SQL 2[join]

18/28

Creating Cross Joins

SELECT last_name, department_name

FROM employees

CROSS JOIN departments ;

7/30/2019 SQL 2[join]

19/28

Outer Joins

EMPLOYEESDEPARTMENTS

There are no employees indepartment 190.

7/30/2019 SQL 2[join]

20/28

Outer Join

An outer join does not require eachrecord in the two joined tables to have amatching record.

The joined table retains each recordeven if no other matching record exists.

Outer joins subdivide further into left

outer joins, right outer joins, and fullouter joins, depending on which table(s)one retains the rows from (left, right, orboth).

7/30/2019 SQL 2[join]

21/28

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) ;

LEFT OUTER JOIN

7/30/2019 SQL 2[join]

22/28


FROM employees e

RIGHT OUTER JOIN departments d


RIGHT OUTER JOIN

7/30/2019 SQL 2[join]

23/28


FROM employees e

FULL OUTER JOIN departments d


FULL OUTER JOIN

7/30/2019 SQL 2[join]

24/28

SELECT e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id


ON (e.department_id = d.department_id)

AND e.manager_id = 149 ;

Additional Conditions

7/30/2019 SQL 2[join]

25/28

Self Join

Sometimes you need to join a table to itself. To findthe name of each employees manager, you need tojoin the EMPLOYEES table to itself, or perform a selfjoin.

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 theMANAGER_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, soKochhar is Whalens manager.

In this process, you look in the table twice. The first time youlook in the table to find Whalen in the LAST_NAME column andMANAGER_ID value of 101. The second time you look in theEMPLOYEE_ID column to find 101 and the LAST_NAME column tofind Kochhar.

7/30/2019 SQL 2[join]

26/28

Self Joins

EMPLOYEES (WORKER) EMPLOYEES (MANAGER)

MANAGER_ID in theWORKERtable is equal toEMPLOYEE_ID in theMANAGERtable.

7/30/2019 SQL 2[join]

27/28

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) ;

7/30/2019 SQL 2[join]

28/28

Review


Cartesian Product

Types of Join Inner Join

Outer Join

Self Join

sql 2[join]

Documents