Базы данных
JOIN
INNER JOIN
INNER JOIN
INNER JOIN
INNER JOIN
OUTER JOIN
OUTER JOIN
CROSS JOIN
63.13K
Категория: Базы данныхБазы данных

Язык SQL. Соединение таблиц. (Лекция 9)

1. Базы данных

Лекция 9
Язык SQL.
Соединение таблиц

2. JOIN

• INNER
• OUTER
– LEFT OUTER
– RIGHT OUTER
– FULL OUTER
• CROSS
18.11.2017
Горбунов О.Е.
2

3. INNER JOIN

SELECT table1.column, table2.column, …
FROM table1
[INNER] JOIN table2
ON (<condition>);
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);
SELECT worker.last_name emp,
manager.last_name mgr
FROM employees worker JOIN employees manager
ON (worker.manager_id =
manager.employee_id);
18.11.2017
Горбунов О.Е.
3

4. INNER JOIN

SELECT employee_id, city,
department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;
18.11.2017
Горбунов О.Е.
4

5. INNER JOIN

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);
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)
WHERE e.manager_id = 149;
18.11.2017
Горбунов О.Е.
5

6. INNER JOIN

SELECT e.last_name, e.salary,
j.grade_level
FROM employees e JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND
j.highest_sal;
18.11.2017
Горбунов О.Е.
6

7. OUTER JOIN

SELECT table1.column, table2.column, …
FROM table1
LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name =
table2.column_name);
18.11.2017
Горбунов О.Е.
7

8. 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);
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);
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);
18.11.2017
Горбунов О.Е.
8

9. CROSS JOIN

SELECT table1.column, table2.column, …
FROM table1
CROSS JOIN table2;
SELECT last_name, department_name
FROM employees
CROSS JOIN departments;
SELECT last_name, department_name
FROM employees, departments;
18.11.2017
Горбунов О.Е.
9
English     Русский Правила