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

Переход на использования синтаксиса SQL:1999 не дает выигрыша в производительности

Объединение таблиц Синтаксис 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];

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

…

SELECT employee_id, last_name
FROM employees JOIN emp
USING (employee_id, last_name);

Но если в результате несколько одноименных столбцов, их необходимо перечислить в USING.

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

Использование псевдонимов столбцов в условии USING запрещено.
Псевдоним может присутствовать в запросе в любом другом предложении, но не в USING и не для столбца объединения.

Получение результата с использованием 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;

Соединение 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;

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

Для добавления дополнительных условий используется 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

Альтернативный способ

…

SELECT worker.last_name emp, manager.last_name mgr
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id;

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

…

Левое внешнее соединение 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(+);

Знак (+) устанавливается после имени столбца таблицы, в которой есть недостаток строк.

Правое внешнее соединение 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;

Полное внешнее соединение FULL OUTER JOIN

…

Для полного внешнего соединения не существует эквивалентного синтаксиса Oracle.

DEPARTMENTS (8 строк)

…

…

…

SELECT last_name, department_name
FROM employees, departments ;

…

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

Групповые функции Синтаксис

Для обработки только уникальных значений столбца, используя групповые функции, необходимо в скобках указать служебное слово DISTINCT;
Для обработки всех значений столбца, включая дубликаты, можно использовать служебное слово ALL. Значение по умолчанию всегда ALL, поэтому его можно не указывать.
В групповых функциях используются типы данных CHAR, VARCHAR2, NUMBER или DATE.
Все групповые функции игнорируют null-значения. Чтобы заменить NULL значения, необходимо использовать вложенные функции NVL, NVL2, COALESCE, CASE или DECODE.

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

Функции AVG и SUM можно применять только к столбцам, которые хранят числовые данные.

Среднее значение по столбцу n, игнорируя значения NULL.

AVG([DISTINCT|ALL]n)

SUM([DISTINCT|ALL]n)

Сумма значений по столбцу n, игнорируя значения NULL.

Максимальное значение столбца expr, игнорируя значения NULL.

MAX([DISTINCT|ALL]expr)

MIN([DISTINCT|ALL]expr)

Минимальное значение столбца expr, игнорируя значения NULL.

1

2

COUNT(*) возвращает число строк в таблице, которые удовлетворяют критерию оператора SELECT, включая дублирующие строки и строки, содержащие NULL-значения в любом из столбцов.

Три формата COUNT:

COUNT(expr) возвращает число строк в таблице, которые не имеют NULL значения в столбце expr.

COUNT(DISTINCT expr) возвращает число строк в таблице, которые не имеют NULL значения в столбце expr и значение столбца уникально.

SELECT COUNT(DISTINCT department_id)
FROM employees

3

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

1

Для учета строк, содержащих NULL значения, при формировании результата групповой функции необходимо применять вложенные функции преобразования NULL значений (NVL, NVL2, COALESCE, CASE, DECODE).

2

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

При использовании условия GROUP BY необходимо удостовериться, что все столбцы из предложении SELECT, к которым не применяется групповая функция, перечислены в предложении GROUP BY.

Условие GROUP BY определяет способ группировки строк. В примере строки группируются по номеру отдела, поэтому функция AVG, которая применяется к столбцу зарплаты, вычисляет среднюю зарплату для каждого отдела.

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);

…

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

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

В предложении GROUP BY последовательно задаются столбцы: сначала столбец формирующий группу, затем столбец по которому формируются подгруппы в рамках этой группы и т.д.

Условие GROUP BY должно быть добавлено для подсчета количества сотрудников в каждом отделе. (GROUP BY department_id)

Для устранения ошибки необходимо либо добавить столбец job_id в условие GROUP BY, либо удалить столбец job_id из предложения SELECT.

SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;

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

Условие HAVING используется для установки ограничений на выборку результирующих строк, полученных при работе групповых функций.
При наличии условия HAVING в запросе, СУБД Oracle выполняет следующие действия:
Строки группируются по условию из GROUP BY.
Групповая функция применяется к сформированным наборам строк.
Из результата отображаются только те строки, которые соответствуют условию HAVING.

Предложения HAVING может предшествовать предложению GROUP BY, но его рекомендуется размещать после GROUP BY.

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

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

Результатом работы запроса является максимальное значение из средних заработных плат, рассчитанных по департаментам.