Соединение и объединение таблиц в SQL: операторы JOIN,UNION, INTERSECT и EXCEPT

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • левое внешнее соединение — в результирующий набор попадают все строки из таблицы с левой стороны оператора сравнения (независимо от того имеются ли совпадающие строки с правой стороны), а из таблицы с правой стороны — только строки с совпадающими значениями столбцов. При этом если для строки из левой таблицы нет соответствий в правой таблице, значениям строки в правой таблице будут присвоены NULL
  • правое внешнее соединение — аналогично левому внешнему соединению, но таблицы меняются местами
  • полное внешнее соединение — композиция левого и правого внешнего соединения: результирующий набор состоит из всех строк обеих таблиц. Если для строки одной из таблиц нет соответствующей строки в другой таблице, всем ячейкам строки второй таблицы присваивается значение NULL.

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

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

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

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

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

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

Оператор UNION

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

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

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

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

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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *