Transcript JOIN - Information & Database Systems

CSED421
Database Systems Lab
Join
Human Resources (HR) schema
Cross Join (Cartesian Product)
departments
employees
27 rows selected.
107 rows selected.
SELECT e.last_name,
d.department_name
FROM employees e,
departments d;
…
107×27=2889 rows selected.
Inner Join
DEPARTMENTS
EMPLOYEES
27 rows selected.
107 rows selected.
…
SELECT e.last_name,
d.department_name
FROM employees e,
departments d
WHERE
e.department_id = d.department_id;
106 rows selected.
Inner Join

Use a join to query data from more than one table.

The join condition in the WHERE clause.


JOIN ~ ON ~


mysql> SELECT table1.column1, table2.column2
–> FROM table1, table2
–> WHERE table1.column3 = table2.column3
->
AND table1.column4 = table2.column4;
mysql> SELECT table1.column1, table2.column2
–> FROM table1 JOIN table2
–> ON table1.column3 = table2.column3
->
AND table1.column4 = table2.column4;
JOIN ~ USING ~

mysql> SELECT table1.column1, table2.column2
–> FROM table1 JOIN table2
–> USING (column3, column4);
Equijoin
EMPLOYEES
DEPARTMENTS
27 rows selected.
(Primary key)
107 rows selected.
(Foreign key)
SELECT e.employee_id,
d.department_id,
d.department_name
FROM employees e,
departments d
WHERE e.department_id = d.department_id;
Non-Equijoin
EMPLOYEES
JOB_GRADES
6 rows selected.
SELECT last_name,
salary,
grade_level
FROM employees,
job_grades
WHERE salary
BETWEEN lowest_sal
AND highest_sal;
107 rows selected.
…
(WHERE salary >= lowest_sal
AND salary <= highest_sal)
107 rows selected.
Joining More than Two Tables
EMPLOYEES
DEPARTMENTS
LOCATIONS
23 rows selected.
27 rows selected.
107 rows selected.
SELECT e.employee_id,
d.department_id,
l.city
FROM employees e,
departments d,
locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
Outer Join
EMPLOYEES
DEPARTMENTS
27 rows selected.
107 rows selected.
Outer Join

Left join



All rows from the left table, even if there are no matches in
the right table.
mysql> SELECT table1.column1, table2.column2
–> FROM table1 LEFT JOIN table2
–> ON table1.column3 = table2.column3;
Right join


All rows from the right table, even if there are no matches in
the left table.
mysql> SELECT table1.column1, table2.column2
–> FROM table1 RIGHT JOIN table2
–> ON table1.column3 = table2.column3;
Outer Join
EMPLOYEES
DEPARTMENTS
EMP ⋈ DEPT
27 rows selected.
107 rows selected.
SELECT employee_id,
department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id
= d.department_id;
107 rows selected.
Outer Join
EMPLOYEES
DEPARTMENTS
EMP ⋈ DEPT
27 rows selected.
107 rows selected.
SELECT employee_id,
department_name
FROM employees e
JOIN departments d
ON e.department_id
= d.department_id;
106 rows selected.
Self Join
EMPLOYEES (WORKER)
EMPLOYEES (MANAGER)
8 rows selected.
SELECT e1.last_name,
e2.last_name
FROM employees e1,
employees e2
WHERE e1.employee_id
= e2.manager_id;
107 rows selected.
106 rows selected.
Joining Tables Using SQL: 1999 Syntax

SELECT
table1.column, table2.column
FROM
table1
[ CROSS JOIN table2 ] |
[ NATURAL JOIN table2 ] |
[ JOIN table2 USING (column_name)] |
[ JOIN table2
ON (table1.column1 = table2.column2) ] |
[ LEFT | RIGHT | FULL OUTER JOIN table2
ON (table1.column1 = table2.column2) ];
Human Resources (HR) schema

Source hr.sql
http://ids.postech.ac.kr/dblab/2014/hr.sql
lab3 DB schema
Practice
1.
특정 부서에 소속되어 있는 직원들의 이름(last_name)과 각 직원
의 소속부서의 이름(department_name)을 출력하기.
…
106 rows selected.
Practice
2.
부서 이름(department_name)과 해당 부서가 위치한 국가 이름
(country_name), 지역 이름(region_name)을 출력하기.
27 rows selected.
Practice
3.
‘Seattle’ 도시(city)에서 근무하는 직원들의 부서명
(department_name)과 이름(last_name)을 출력하기.
Practice
4.
모든 부서이름(department_name)과 각 부서에 소속된 직원의 수
출력하기.
27 rows selected.
Practice
5.
자신의 상사(manager)보다 오래 일한 직원들의 이름(last_name)
을 출력하기.