Объединение таблиц (SQL)

1. Объединение таблиц

До
выпуска
стандарта
SQL:1999
Oracle
обладал
собственным
синтаксисом
объединения таблиц.
В
настоящее
время
поддерживаются
2
синтаксиса кодирования объединения таблиц.
Переход
на
использования
синтаксиса
SQL:1999
не
дает
выигрыша
в
1
производительности

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

Простое объединение таблиц (NATURAL JOIN):
• Условие USING;
• Условие ON.
Самообъединение (SELF-JOIN);
Объединение по равенству (EQUIJOINS);
Объединение по не равенству (NONEQUIJOIN);
Внешнее соединение (OUTER JOIN):
• LEFT OUTER;
• RIGHT OUTER;
• FULL OUTER.
Декартово пересечение таблиц (CROSS JOIN).
2

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

EMPLOYEES
DEPARTMENTS
…
…
3

4. Объединение таблиц Синтаксис 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];
CROSS JOIN возвращает декартовое пересечение таблиц.
NATURAL JOIN соединяет две таблицы по одноименным столбцам,
имеющим одинаковый тип данных .
JOIN table2 USING(column_name) объединяет таблицы по указанному
столбцу.
JOIN table2 ON (table1.column_name=table2.column_name) объединяет
таблицы по указанному условию.
LEFT | RIGHT | FULL OUTER JOIN выполняет внешние соединения
4
таблиц по указанному условию.

5. Использование одноименных столбцов в запросе

Для
одноименных
столбцов
таблиц
необходимо использовать префиксы (имена
таблиц или псевдонимы таблиц).
Для
повышения
производительности
используются префиксы для столбцов с
указанием таблицы или псевдонима.
Чем меньше SQL код, тем меньше места он
занимает в памяти и тем быстрее происходит
разбор SQL инструкции.
5

6. Простое объединение таблиц NATURAL JOIN

NATURAL JOIN основано на всех столбцах в
двух таблицах, которые имеют одинаковые
имена.
Результат: выбираются строки из двух
таблиц, для которых существуют одинаковые
значения,
для
всех
соответствующих
столбцов.
Если столбцы с одинаковыми именами имеют
разные типы данных, генерируется ошибка.
6

7. Получение записей с NATURAL JOIN

SELECT department_id, department_name,
location_id, city
FROM
departments
NATURAL JOIN locations ;
SELECT d.department_id, d.department_name,
l.location_id, l.city
FROM
departments d, locations l
where d.location_id=l.location_id;
7

8. Создание объединений с условием USING

Если несколько столбцов имеют одинаковые имена, но
типы данных не совпадают, простое объединение
может быть использовано с условием USING, которое
позволяет точно указывать столбцы для объединения,
даже если типы данных различны у этих столбцов.
Если в результате выбираются несколько столбцов,
существующие в двух таблицах, то эти столбцы
должны быть перечислены в USING.
Не используйте имя таблицы или псевдоним как
префикс в условиях USING.
NATURAL
JOINS
и
USING
являются
двумя
независящими друг от друга способами объединения.
8

9. Использование USING

EMPLOYEES
DEPARTMENTS
Первичный ключ
…
Внешний ключ
9

10. Получение результата с использованием USING

SELECT employee_id, last_name,
location_id, department_id
FROM
employees JOIN departments
USING (department_id) ;
Но
если
в
результате
несколько
одноименных
столбцов, их необходимо
перечислить в USING.
SELECT employee_id, last_name
FROM
employees JOIN emp
USING (employee_id, last_name);
…
10

11. Использование псевдонимов столбцов в условии USING

Использование псевдонимов столбцов в условии USING
запрещено.
Псевдоним может присутствовать в запросе в любом
другом предложении, но не в USING и не для столбца
объединения.
SELECT l.city, d.department_name
FROM
locations l JOIN departments d
USING (location_id)
WHERE d.location_id = 1400 –- вызывает ошибку
and d.department_name='IT'; -- работает
11

12. Создание объединений с условием ON

NATURAL JOIN объединяет
одноименным столбцам.
таблицы
по
всем
Если требуется объединить таблицы только по
некоторым столбцам или по условию, используется
условие ON.
Условие ON применяется:
Для указания столбцов
таблицы.
Синтаксически отделяет
таблиц от раздела поиска
Упрощает
код
для
удобочитаемым.
по которым следует объединять
раздел с условиями объединения
результата по условию WHERE.
понимания,
т.е.
делает
его
12

13. Получение результата с использованием 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);
… e.employee_id, e.last_name, e.department_id,
SELECT
d.department_id, d.location_id
FROM
employees e, departments d
WHERE e.department_id = d.department_id;
13

14. Соединение 3х таблиц по условию 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;
SELECT
FROM
WHERE
AND
e.employee_id, l.city, d.department_name
…
employees
e, departments d, locations l
d.department_id = e.department_id
d.location_id = l.location_id;
14

15. Использование нескольких условий для объединения

Для добавления дополнительных условий используется AND
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 ;
Для добавления дополнительных условий используется 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)
WHERE
e.manager_id = 149 ;
Альтернативный способ
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM
employees e, departments d
WHERE e.department_id = d.department_id
AND
e.manager_id = 149 ;
15

16. Самообъединение SELF-JOIN

Случай, когда в запросе используется одна и та же таблица
несколько раз, и данная таблица соединяется сама с собой,
называется самообъединением
EMPLOYEES (Работник)
…
EMPLOYEES (Начальник)
…
MANAGER_ID для Работника эквивалентно
EMPLOYEE_ID для Начальника
16

17. Самообъединение с условием ON

SELECT worker.last_name emp, manager.last_name mgr
FROM
employees worker JOIN employees manager
ON
(worker.manager_id = manager.employee_id);
…
SELECT worker.last_name emp, manager.last_name mgr
FROM
employees worker, employees manager
WHERE worker.manager_id = manager.employee_id;
17

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

EMPLOYEES
…
JOB_GRADES
Таблица JOB_GRADES определяет
диапазон значений LOWEST_SAL и
HIGHEST_SAL для каждой оценки
GRADE_LEVEL. Следовательно, столбец
GRADE_LEVEL может быть использован
для выставления оценки каждому
сотруднику в зависимости от зарплаты.
18

19. Получение записей с NONEQUIJOIN

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;
SELECT e.last_name,
e.salary, j.grade_level
…
FROM
employees e, job_grades j
19
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

20. Получение записей при отсутствии значений в одной из таблиц

DEPARTMENTS
EMPLOYEES
…
Нет сотрудников в отделе
190.
20

21. Внутреннее соединение и внешнее соединение

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

22. Левое внешнее соединение 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) ;
…
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
where e.department_id = d.department_id(+);
Знак (+) устанавливается после имени столбца таблицы, в
которой есть недостаток строк.
22

23. Правое внешнее соединение 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) ;
…
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
where e.department_id(+) = d.department_id;
23

24. Полное внешнее соединение 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) ;
…
Для полного внешнего соединения не существует
эквивалентного синтаксиса Oracle.
24

25. Декартово пересечение

Декартово пересечение образуется когда:
Условие объединения отсутствует;
Условие объединения ошибочно;
Необходимо все строки из первой таблицы
объединить со всеми строками из второй
таблицы.
Чтобы
избежать
неправильных
пересечений таблиц, необходимо
условия объединения.
декартовых
использовать
25

26. Декартово пересечение. Формирование.

EMPLOYEES (20 строк)
DEPARTMENTS (8 строк)
…
Декартово
пересечение
20 x 8 = 160
строк
…
26

27. Создание CROSS JOIN

CROSS JOIN применяют для создания декартова
пересечения двух таблиц.
SELECT last_name, department_name
FROM
employees
CROSS JOIN departments ;
…
SELECT last_name, department_name
FROM
employees, departments ;
27

28. Агрегация данных и групповые функции

28

29. Групповые функции

Групповые функции позволяют обрабатывать
набор строк для формирования одного
результата.
EMPLOYEES
…
Обрабатываемый набор строк
может включать все строки таблицы
или таблицу, разделенную на
группы.
Значение
максимальной
зарплаты в
таблице
EMPLOYEES
29

30. Типы групповых функций

Групповые
функции
Функция
Описание
AVG
Среднее значение, игнорируя значения NULL
COUNT
Вычисление количества всех выбранных строк в запросе, включая
дубликаты
MAX
Максимальное значение из группы, игнорируя значения NULL
MIN
Минимальное значение из группы, игнорируя значения NULL
STDDEV
Стандартное отклонение, игнорируя значения NULL
SUM
Сумма значений, игнорируя значения NULL
VARIANCE
Вариация, игнорируя значения NULL
30

31. Групповые функции можно использовать в операторе SELECT. Допустимо использовать несколько групповых функций, разделенных запятыми.

Групповые функции
Синтаксис
Групповые функции можно использовать в операторе SELECT.
Допустимо использовать несколько групповых функций, разделенных
запятыми. SELECT
group_function(column), ...
FROM
[WHERE
[ORDER BY
table
condition]
column];
Для обработки только уникальных значений столбца, используя
групповые функции, необходимо в скобках указать служебное слово
DISTINCT;
Для обработки всех значений столбца, включая дубликаты, можно
использовать служебное слово ALL. Значение по умолчанию всегда
ALL, поэтому его можно не указывать.
В групповых функциях используются типы данных CHAR, VARCHAR2,
NUMBER или DATE.
Все групповые функции игнорируют null-значения. Чтобы заменить
NULL значения, необходимо использовать вложенные функции NVL,
31
NVL2, COALESCE, CASE или DECODE.

32. Использование функций AVG и SUM

AVG([DISTINCT|ALL]n)
Среднее значение по столбцу n, игнорируя
значения NULL.
SUM([DISTINCT|ALL]n)
Сумма значений по столбцу n, игнорируя
значения NULL.
Функции AVG и SUM можно применять только к
столбцам, которые хранят числовые данные.
SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM
employees
WHERE job_id LIKE '%REP%';
32

33. Использование функций MIN и MAX

MAX([DISTINCT|ALL]expr)
MIN([DISTINCT|ALL]expr)
Максимальное значение столбца expr,
игнорируя значения NULL.
Минимальное значение столбца expr,
игнорируя значения NULL.
Можно использовать функции MAX и
числовых, символьных типов данных и дат.
MIN
для
SELECT MIN(hire_date), MAX(hire_date)
FROM
employees;
33

34. Использование функции COUNT

Три формата COUNT:
COUNT(*) возвращает число строк в таблице, которые удовлетворяют
критерию оператора SELECT, включая дублирующие строки и строки,
содержащие NULL-значения в любом из столбцов.
SELECT COUNT(*)
1 FROM employees
WHERE department_id = 50;
COUNT(expr) возвращает число строк в таблице, которые не имеют
NULL значения в столбце expr.
SELECT COUNT(commission_pct)
2 FROM employees
WHERE department_id=80;
COUNT(DISTINCT expr) возвращает число строк в таблице, которые не
имеют NULL значения в столбце expr и значение столбца уникально.
3
SELECT COUNT(DISTINCT department_id)
FROM
employees
34

35. NULL значения в групповых функциях

Строки, содержащие NULL значения в столбцах, для которых
применяются групповые функции, не участвуют в формировании
результата групповых функций.
1
SELECT AVG(commission_pct)
FROM
employees;
Для учета строк, содержащих NULL значения, при формировании
результата групповой функции необходимо применять вложенные
функции преобразования NULL значений (NVL, NVL2, COALESCE, CASE,
DECODE).
2
SELECT AVG(NVL(commission_pct, 0))
FROM
employees;
35

36. Создание групп данных

В некоторых задачах необходимо применять групповые функции не
ко всем строкам таблицы, а к строкам, входящим в состав
некоторых условных групп.
EMPLOYEES
4400
9500
Средняя заработная плата
сотрудников по отделам
3500
6400
10033
…
36

37. Создание групп данных Условие GROUP BY

Для деления всех строк таблицы на группы используется условие
GROUP BY.
SELECT
column1, group_function(column2)
FROM
table
[WHERE
condition]
[GROUP BY group_by_expression]
[ORDER BY column1];
Правила применения GROUP BY:
Если в операторе SELECT используется групповая функция для столбца
column2, то невозможно получить результаты столбцов, к которым не
применяются групповые функции (column1), если эти столбцы не
перечислены в условии GROUP BY.
Условие WHERE позволяет исключить строки из результата до
формирования групп.
В условии GROUP BY невозможно использовать псевдонимы.
37

38. Использование GROUP BY в операторе SELECT

При использовании условия GROUP BY необходимо удостовериться,
что все столбцы из предложении SELECT, к которым не применяется
групповая функция, перечислены в предложении GROUP BY.
SELECT
department_id, AVG(salary)
FROM
employees
GROUP BY department_id ;
Условие GROUP BY определяет способ группировки строк. В примере
строки группируются по номеру отдела, поэтому функция AVG, которая
применяется к столбцу зарплаты, вычисляет среднюю зарплату для
каждого отдела.
38

39. Использование GROUP BY в операторе SELECT

Столбец, включенный в условие GROUP
присутствовать в предложении SELECT.
BY,
может
не
SELECT
AVG(salary)
FROM
employees
GROUP BY department_id ;
Групповую функцию можно использовать в предложении ORDER BY.
SELECT
FROM
GROUP BY
ORDER BY
department_id, AVG(salary)
employees
department_id
AVG(salary);
39

40. Группировка данных из нескольких столбцов

Некоторые задачи требуют применения групповой функции для
групп записей внутри выбранной группы.
EMPLOYEES
Суммарная заработная плата
сотрудников по занимаемым
должностям внутри отдела
…
40

41. Использование условия GROUP BY для нескольких столбцов

В предложении GROUP BY последовательно задаются столбцы:
сначала столбец формирующий группу, затем столбец по которому
формируются подгруппы в рамках этой группы и т.д.
SELECT
FROM
GROUP BY
ORDER BY
department_id dept_id, job_id, SUM(salary)
employees
department_id, job_id
department_id;
41

42. Ошибки при использовании групповых функций

SELECT department_id, COUNT(last_name)
FROM
employees;
Условие GROUP BY должно быть
добавлено для подсчета количества
сотрудников в каждом отделе.
(GROUP BY department_id)
SELECT department_id, job_id, COUNT(last_name)
FROM
employees
GROUP BY department_id;
Для устранения ошибки
необходимо либо добавить столбец
job_id в условие GROUP BY, либо
удалить столбец job_id из
42
предложения SELECT.

43. Ошибки при использовании групповых функций

В предложение WHERE недопустимо использовать групповые
функции для формирования ограничений на группу
выбираемых данных.
Для этих целей используется условие HAVING
SELECT
FROM
WHERE
GROUP BY
department_id, AVG(salary)
employees
AVG(salary) > 8000
department_id;
43

44. Установка ограничений на группу выбираемых данных

EMPLOYEES
Вывод №-ов отделов и
максимальной заработной платы по
отделу, если максимальная
заработная плата в отделе
превышает 10 000
…
44

45. Условие HAVING Синтаксис

Условие HAVING используется для установки ограничений на
выборку результирующих строк, полученных при работе
групповых функций.
При наличии условия HAVING в запросе, СУБД Oracle
выполняет следующие действия:
Строки группируются по условию из GROUP BY.
Групповая функция применяется к сформированным наборам
строк.
Из результата отображаются только те строки, которые
соответствуют условию HAVING.
SELECT
column, group_function
FROM
table
[WHERE
condition]
[GROUP BY group_by_expression]
[HAVING
group_condition]
[ORDER BY column];
Предложения HAVING может предшествовать предложению GROUP BY,
но его рекомендуется размещать после GROUP BY.
45

46. Использование условия HAVING

SELECT
FROM
GROUP BY
HAVING
department_id, MAX(salary)
employees
department_id
MAX(salary)>10000 ;
46

47. Использование условия HAVING и ORDER BY

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
job_id, SUM(salary) PAYROLL
employees
job_id NOT LIKE '%REP%'
job_id
SUM(salary) > 13000
SUM(salary);
47

48. Вложенные групповые функции

Групповые функции могут быть вложенными
Результатом
работы
запроса
является
максимальное
значение из средних заработных плат, рассчитанных по
департаментам.
SELECT
MAX(AVG(salary))
FROM
employees
GROUP BY department_id;
48