Объединение таблиц (SQL) презентация

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

Получение данных из нескольких таблиц
EMPLOYEES
DEPARTMENTS
…
…

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

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

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

SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations ;
Получение записей с NATURAL JOIN
SELECT d.department_id, d.department_name,
l.location_id, l.city
FROM departments d, locations l
where d.location_id=l.location_id;

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

Использование USING
EMPLOYEES
DEPARTMENTS
Внешний ключ
Первичный ключ
…

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);
Но если в результате несколько одноименных столбцов, их необходимо перечислить в 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'; -- работает
Использование псевдонимов столбцов в условии USING
Использование псевдонимов столбцов в условии USING запрещено.
Псевдоним может присутствовать в запросе в любом другом предложении, но не в USING и не для столбца объединения.

Создание объединений с условием ON
NATURAL JOIN объединяет таблицы по всем одноименным столбцам.
Если требуется объединить таблицы только по некоторым столбцам или по условию, используется условие ON.
Условие ON применяется:
Для указания столбцов по которым следует объединять таблицы.
Синтаксически отделяет раздел с условиями объединения таблиц от раздела поиска результата по условию 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);
Получение результата с использованием ON
…
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;

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

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 ;
Использование нескольких условий для объединения
Для добавления дополнительных условий используется AND
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 ;
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 ;
Для добавления дополнительных условий используется WHERE
Альтернативный способ

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

Самообъединение с условием 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;

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

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;
Получение записей с NONEQUIJOIN
…
SELECT 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;

Получение записей при отсутствии значений в одной из таблиц
EMPLOYEES
DEPARTMENTS
Нет сотрудников в отделе 190.
…

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

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

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

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

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

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

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

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

Типы групповых функций
Групповые
функции

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

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

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

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

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

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

Создание групп данных Условие 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 невозможно использовать псевдонимы.

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

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

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

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

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

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

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

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

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
Использование условия HAVING

SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);
Использование условия HAVING и ORDER BY