SQL операторы JOIN, UNION, INTERSECT и EXCEPT

Содержание

Естественное соединение
Декартово произведение (перекрестное соединение)
Внешнее соединение
Тета-соединение
Самосоединение
Полусоединение
Оператор UNION

Соединение таблиц в запросе SELECT выполняется с помощью оператора JOIN.

Возможно также выполнить соединение и без оператора JOIN с помощью инструкции WHERE используя столбцы соединения, но этот синтаксис считается неявным и устаревшим.

Выделяют следующие виды соединения, каждому из которых соответствует своя форма оператора JOIN:

CROSS JOIN — перекрестное или декартово соединение
[INNER] JOIN — естественное или внутреннее соединение
LEFT [OUTER] JOIN — левое внешнее соединение
RIGHT [OUTER] JOIN — правое внешнее соединение
FULL [OUTER] JOIN — полное внешнее соединение

Существует также тета-соединение, самосоединение и полусоединение.

Естественное соединение

Естественное соединение — внутреннее соединение или соединение по эквивалентности.

SELECT employee.*, department.*
FROM employee INNER JOIN department
ON employee.dept_no = department.dept_no;

SELECT employee.*, department.*

FROM employee INNER JOIN department

ON employee.dept_no = department.dept_no;

Здесь предложение FROM определяет соединяемые таблицы и в нем явно указывается тип соединения — INNER JOIN. Предложение ON является частью предложения FROM и указывает соединяемые столбцы. Выражение employee.dept_no = department.dept_no определяет условие соединения.

Эквивалентный запрос с применением неявного синтаксиса:

SELECT employee.*, department.*
FROM employee, department
WHERE employee.dept_no = department.dept_no;

SELECT employee.*, department.*

FROM employee, department

WHERE employee.dept_no = department.dept_no;

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

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

В инструкции SELECT объединить можно до 64 таблиц (ограничение MS SQL), при этом один оператор JOIN соединяет только две таблицы:

SELECT emp_fname, emp_lname
FROM works_on 
JOIN employee ON works_on.emp_no=employee.emp_no
JOIN department ON employee.dept_no=department.dept_no

SELECT emp_fname, emp_lname

FROM works_on

JOIN employee ON works_on.emp_no=employee.emp_no

JOIN department ON employee.dept_no=department.dept_no

Декартово произведение (перекрестное соединение)

Декартово произведение (перекрестное соединение) соединяет каждую строку первой таблицы с каждой строкой второй. Результатом декартово произведения первой таблицы с n строками и второй таблицы с m строками будет таблица с n × m строками.

SELECT employee.*, department.*
FROM employee CROSS JOIN department;

1 2	SELECT employee., department. FROM employee CROSS JOIN department;

Внешнее соединение

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

Выделяют три вида внешних соединений:

левое внешнее соединение — в результирующий набор попадают все строки из таблицы с левой стороны оператора сравнения (независимо от того имеются ли совпадающие строки с правой стороны), а из таблицы с правой стороны — только строки с совпадающими значениями столбцов. При этом если для строки из левой таблицы нет соответствий в правой таблице, значениям строки в правой таблице будут присвоены NULL
Transact-SQL
SELECT employee_enh.*, department.location FROM employee_enh LEFT OUTER JOIN department ON domicile = location;
1
2
3
SELECT employee_enh.*, department.location
FROM employee_enh LEFT OUTER JOIN department
ON domicile = location;
правое внешнее соединение — аналогично левому внешнему соединению, но таблицы меняются местами
Transact-SQL
SELECT employee_enh.domicile, department.* FROM employee_enh RIGHT OUTER JOIN department ON domicile =location;
1
2
3
SELECT employee_enh.domicile, department.*
FROM employee_enh RIGHT OUTER JOIN department
ON domicile =location;
полное внешнее соединение — композиция левого и правого внешнего соединения: результирующий набор состоит из всех строк обеих таблиц. Если для строки одной из таблиц нет соответствующей строки в другой таблице, всем ячейкам строки второй таблицы присваивается значение NULL.

Тета-соединение

Условие сравнения столбцов соединения не обязательно должно быть равенством, но может быть любым другим сравнением. Соединение, в котором используется общее условие сравнения столбцов соединения, называется тета-соединением:

SELECT emp_fname, emp_lname, domicile, location
FROM employee_enh JOIN department
ON domicile < location;

SELECT emp_fname, emp_lname, domicile, location

FROM employee_enh JOIN department

ON domicile < location;

Самосоединение

Самосоединение — это естественное соединение таблицы с самой собой. При этом один столбец таблицы сравнивается сам с собой. Сравнивание столбца с самим собой означает, что в предложении FROM инструкции SELECT имя таблицы употребляется дважды. Поэтому необходимо иметь возможность ссылаться на имя одной и той же таблицы дважды. Это можно осуществить, используя, по крайней мере, один псевдоним. То же самое относится и к именам столбцов в условии соединения в инструкции SELECT. Для того чтобы различить столбцы с одинаковыми именами, необходимо использовать уточненные имена.

Полусоединение

Полусоединение похоже на естественное соединение, но возвращает только набор всех строк из одной таблицы, для которой в другой таблице есть одно или несколько совпадений.

Оператор UNION

Оператор UNION объединяет результаты двух или более запросов в один результирующий набор, в который входят все строки, принадлежащие всем запросам в объединении:

select_1 UNION [ALL] select_2 {[UNION [ALL] select_3]}...

1	select_1 UNION [ALL] select_2 {[UNION [ALL] select_3]}...

Параметры select_1, select_2, … представляют собой инструкции SELECT, которые создают объединение. Если используется параметр ALL, отображаются все строки, включая дубликаты. По умолчанию дубликаты удаляются.

Объединять с помощью инструкции UNION можно только совместимые таблицы. Под совместимыми таблицами имеется в виду, что оба списка столбцов выборки должны содержать одинаковое число столбцов, а соответствующие столбцы должны иметь совместимые типы данных. Результат объединения можно упорядочить, только используя предложение ORDER BY в последней инструкции SELECT. Предложения GROUP BY и HAVING можно применять с отдельными инструкциями SELECT, но не в самом объединении.

Два других оператора для работы с наборами:

INTERSECT — пересечение — набор строк, которые принадлежат к обеим таблицам
EXCEPT — разность двух таблиц — все значения, которые принадлежат к первой таблице и не присутствуют во второй