Отображение данных из нескольких таблиц

1. Отображение данных из нескольких таблиц

© Oracle, 2007. Все права защищены.

2. Цели

Изучив материалы этого занятия, вы освоите следующие
темы:
• Создание инструкций SELECT для получения данных
из нескольких таблиц с помощью объединения по
равенству и по неравенству
• Объединение таблицы с самой собой с помощью
самообъединения
• Просмотр данных, в целом не соответствующих условию
объединения, с помощью внешних объединений
• Создание декартова произведения всех строк из
нескольких таблиц
6-2
© Oracle, 2007. Все права защищены.

3. План занятия

• Типы объединений JOINS и их синтаксис
• Естественное объединение:
– предложение USING
– предложение ON
• Самообъединение
• Объединения по неравенству
• Внешнее объединение OUTER:
– левое внешнее объединение LEFT OUTER
– правое внешнее объединение RIGHT OUTER
– полное внешнее объединение FULL OUTER
• Декартово произведение
– перекрестное объединение
6-3
© Oracle, 2007. Все права защищены.

4. Получение данных из нескольких таблиц

EMPLOYEES
DEPARTMENTS
…
…
6-4
© Oracle, 2007. Все права защищены.

5. Типы объединений

Со стандартом SQL:1999 совместимы следующие
объединения:
• естественные объединения:
– предложение NATURAL JOIN
– предложение USING
– предложение ON
• внешние объединения:
– левое внешнее объединение LEFT OUTER JOIN
– правое внешнее объединение RIGHT OUTER JOIN
– полное внешнее объединение FULL OUTER JOIN
• перекрестные объединения
6-5
© Oracle, 2007. Все права защищены.

6. Объединение таблиц с помощью синтаксиса SQL:1999

Объединение используется для запроса данных из
нескольких таблиц:
SELECT
table1.column, table2.column
FROM
table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON (table1.column_name = table2.column_name)]|
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)]|
[CROSS JOIN table2];
6-6
© Oracle, 2007. Все права защищены.

7. Определение неоднозначных имен столбцов

• Для точного определения имен столбцов, повторяющихся в нескольких таблицах, необходимо использовать
префиксы с именами таблиц.
• Префиксы с именами таблиц позволяют повысить
производительность.
• В префиксах вместо полного имени таблицы можно
использовать псевдонимы.
• Псевдоним позволяет сократить имя таблицы:
– Сокращается код SQL, используется меньше памяти.
• Псевдонимы столбцов используются для различения
столбцов с одинаковыми именами, находящимися
в разных таблицах.
6-7
© Oracle, 2007. Все права защищены.

8. План занятия

• Типы объединений JOINS и их синтаксис
• Естественное объединение:
– предложение USING
– предложение ON
• Самообъединение
• Объединения по неравенству
• Внешнее объединение OUTER:
– левое внешнее объединение LEFT OUTER
– правое внешнее объединение RIGHT OUTER
– полное внешнее объединение FULL OUTER
• Декартово произведение
– Перекрестное объединение
6-8
© Oracle, 2007. Все права защищены.

9. Создание естественных объединений

• Предложение NATURAL JOIN при объединении двух
таблиц учитывает все столбцы с совпадающими
именами.
• Оно выбирает из двух таблиц строки, которые во всех
соответствующих столбцах имеют равные значения.
• Если столбцы с совпадающими именами отличаются
типом данных, возникает ошибка.
6-9
© Oracle, 2007. Все права защищены.

10. Извлечение записей с помощью естественных объединений

SELECT department_id, department_name,
location_id, city
FROM
departments
NATURAL JOIN locations ;
6 - 10
© Oracle, 2007. Все права защищены.

11. Создание объединений с предложением USING

• Если у нескольких столбцов совпадают имена, но
отличаются типы данных, естественное объединение
можно применить, использовав предложение USING
для указания столбцов, которые должны участвовать
в объединении по равенству.
• Предложение USING следует использовать, если
при объединении нужно сопоставить только одну
из нескольких соответствующих пар столбцов.
• Предложения NATURAL JOIN и USING являются
взаимоисключающими.
6 - 11
© Oracle, 2007. Все права защищены.

12. Объединение по именам столбцов

DEPARTMENTS
EMPLOYEES
Первичный ключ
…
Внешний ключ
6 - 12
© Oracle, 2007. Все права защищены.

13. Извлечение записей с помощью предложения USING

SELECT employee_id, last_name,
location_id, department_id
FROM
employees JOIN departments
USING (department_id) ;
…
6 - 13
© Oracle, 2007. Все права защищены.

14. Использование псевдонимов таблиц с предложением USING

• Столбец, используемый в предложении USING, уточнять
не нужно.
• Если этот же столбец упоминается и в другом месте
инструкции SQL, использовать его псевдоним не следует.
SELECT l.city, d.department_name
FROM
locations l JOIN departments d
USING (location_id)
WHERE d.location_id = 1400;
6 - 14
© Oracle, 2007. Все права защищены.

15. Создание объединений с предложением ON

• Условие объединения для естественных объединений –
это в основном объединение по равенству для всех
столбцов с одинаковыми именами.
• Для указания произвольных условий или столбцов,
участвующих в объединении, следует использовать
предложение ON.
• Условие объединения отделено от других условий поиска.
• Предложение ON облегчает понимание кода.
6 - 15
© Oracle, 2007. Все права защищены.

16. Извлечение записей с помощью предложения ON

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);
…
6 - 16
© Oracle, 2007. Все права защищены.

17. Создание трехсторонних объединений с предложением ON

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;
…
6 - 17
© Oracle, 2007. Все права защищены.

18. Применение к объединению дополнительных условий

Для применения дополнительных условий используйте
предложения AND или WHERE:
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 ;
6 - 18
© Oracle, 2007. Все права защищены.

19. План занятия

• Типы объединений JOINS и их синтаксис
• Естественное объединение:
– предложение USING
– предложение ON
• Самообъединение
• Объединения по неравенству
• Внешнее объединение OUTER:
– левое внешнее объединение LEFT OUTER
– правое внешнее объединение RIGHT OUTER
– полное внешнее объединение FULL OUTER
• Декартово произведение
– перекрестное объединение
6 - 19
© Oracle, 2007. Все права защищены.

20. Объединение таблицы с самой собой

EMPLOYEES (WORKER)
…
EMPLOYEES (MANAGER)
…
MANAGER_ID в таблице WORKER равен
EMPLOYEE_ID в таблице MANAGER.
6 - 20
© Oracle, 2007. Все права защищены.

21. Самообъединения с использованием предложения ON

SELECT worker.last_name emp, manager.last_name mgr
FROM
employees worker JOIN employees manager
ON
(worker.manager_id = manager.employee_id);
…
6 - 21
© Oracle, 2007. Все права защищены.

22. План занятия

• Типы объединений JOINS и их синтаксис
• Естественное объединение:
– предложение USING
– предложение ON
• Самообъединение
• Объединения по неравенству
• Внешнее объединение OUTER:
– левое внешнее объединение LEFT OUTER
– правое внешнее объединение RIGHT OUTER
– полное внешнее объединение FULL OUTER
• Декартово произведение
– перекрестное объединение
6 - 22
© Oracle, 2007. Все права защищены.

23. Объединения по неравенству

EMPLOYEES
…
6 - 23
JOB_GRADES
Таблица JOB_GRADES определяет диапазон окладов (значения LOWEST_SAL
и HIGHEST_SAL) для каждого уровня
разрядов (GRADE_LEVEL). Поэтому столбец
GRADE_LEVEL можно использовать для
назначения разрядов каждому работнику.
© Oracle, 2007. Все права защищены.

24. Извлечение записей с помощью объединений по неравенству

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;
…
6 - 24
© Oracle, 2007. Все права защищены.

25. План занятия

• Типы объединений JOINS и их синтаксис
• Естественное объединение:
– предложение USING
– предложение ON
• Самообъединение
• Объединения по неравенству
• Внешнее объединение OUTER:
– левое внешнее объединение LEFT OUTER
– правое внешнее объединение RIGHT OUTER
– полное внешнее объединение FULL OUTER
• Декартово произведение
– перекрестное объединение
6 - 25
© Oracle, 2007. Все права защищены.

26. Возвращение записей, не имеющих полного совпадения, с помощью внешних объединений

DEPARTMENTS
EMPLOYEES
…
В отделе 190 работники
отсутствуют.
6 - 26
© Oracle, 2007. Все права защищены.

27. Сравнение внутренних (INNER) и внешних (OUTER) объединений

• В стандарте SQL:1999 внутренним называется
объединение двух таблиц, возвращающее только
соответствующие друг другу строки.
• Объединение двух таблиц, возвращающее как
результаты внутреннего объединения, так и строки без
соответствия из левой (правой) таблицы, называется
левым (правым) внешним объединением.
• Объединение двух таблиц, вместе с результатами
внутреннего объединения возвращающее результаты
левого и правого объединений, называется полным
внешним объединением.
6 - 27
© Oracle, 2007. Все права защищены.

28. Левое внешнее объединение (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) ;
…
6 - 28
© Oracle, 2007. Все права защищены.

29. Правое внешнее объединение (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);
…
6 - 29
© Oracle, 2007. Все права защищены.

30. Полное внешнее объединение (FULL OUTER JOIN)

SELECT e.last_name, d.department_id, d.department_name
FROM
employees e FULL OUTER JOIN departments d
ON
(e.department_id = d.department_id);
…
6 - 30
© Oracle, 2007. Все права защищены.

31. План занятия

• Типы объединений JOINS и их синтаксис
• Естественное объединение:
– предложение USING
– предложение ON
• Самообъединение
• Объединения по неравенству
• Внешнее объединение OUTER:
– левое внешнее объединение LEFT OUTER
– правое внешнее объединение RIGHT OUTER
– полное внешнее объединение FULL OUTER
• Декартово произведение
– перекрестное объединение
6 - 31
© Oracle, 2007. Все права защищены.

32. Декартово произведение

• Декартово произведение образуется в следующих
случаях:
– условие объединения не указано
– условие объединения недопустимо
– все строки в первой таблице объединяются со всеми
строками во второй таблице
• Чтобы избежать образования декартова произведения,
необходимо всегда указывать допустимое условие
объединения.
6 - 32
© Oracle, 2007. Все права защищены.

33. Создание декартова произведения

EMPLOYEES (20 строк)
DEPARTMENTS (8 строк)
…
Декартово
произведение:
20 x 8 = 160 строк
…
6 - 33
© Oracle, 2007. Все права защищены.

34. Создание перекрестных объединений

• При помощи предложения CROSS JOIN создается
перекрестное произведение двух таблиц.
• Оно также называется декартовым произведением
двух таблиц.
SELECT last_name, department_name
FROM
employees
CROSS JOIN departments ;
…
6 - 34
© Oracle, 2007. Все права защищены.

35. Заключение

На этом занятии было рассмотрено, как применять
объединения для отображения данных из нескольких
таблиц и использовать для этого:
• Объединения по равенству
• Объединения по неравенству
• Внешние объединения
• Самообъединения
• Перекрестные объединения
• Естественные объединения
• Полные (или двусторонние) внешние объединения
6 - 35
© Oracle, 2007. Все права защищены.

36. Упражнение 6: обзор

Это упражнение охватывает следующие темы:
• объединение таблиц по равенству
• выполнение внешнего и самообъединения
• добавление условий
6 - 36
© Oracle, 2007. Все права защищены.