SQL
ПРИСОЕДИНИТЬСЯ
JOIN — это метод объединения (объединения) информации из двух таблиц. Результатом является сшитое множество столбцов из обеих таблиц, определяемое типом объединения (INNER / OUTER / CROSS и LEFT / RIGHT / FULL, объяснено ниже) и критерии присоединения (как связаны строки из обеих таблиц).
Таблица может быть присоединена к себе или к любой другой таблице. Если требуется получить доступ к информации из более чем двух таблиц, в предложении FROM можно указать несколько объединений.
Синтаксис
- [ < INNER | < < LEFT | RIGHT | FULL >[ OUTER ] > > ] JOIN
замечания
Соединения, как следует из их названия, являются способом одновременного запроса данных из нескольких таблиц, причем строки отображают столбцы, взятые из более чем одной таблицы.
Основное явное внутреннее соединение
Базовое соединение (также называемое «внутреннее соединение») запрашивает данные из двух таблиц с их отношением, определенным в предложении join .
В следующем примере будут выбраны имена сотрудников (FName) из таблицы Employees и название отдела, в котором они работают (имя) из таблицы Departments:
| Employees.FName | Departments.Name |
|---|---|
| Джеймс | HR |
| Джон | HR |
| Ричард | Продажи |
Неявное присоединение
Присоединяется также может быть выполнено при наличии нескольких таблиц в from пункта, разделенных запятыми , и определения отношений между ними в where п. Этот метод называется Implicit Join (поскольку он фактически не содержит предложение join ).
Все РСУБД поддерживают его, но синтаксис обычно не рекомендуется. Причинами плохого использования этого синтаксиса являются:
- Можно получить случайные кросс-соединения, которые затем возвращают неверные результаты, особенно если у вас много запросов в запросе.
- Если вы намеревались перекрестное соединение, то это не ясно из синтаксиса (вместо этого выпишите CROSS JOIN), и кто-то, вероятно, изменит его во время обслуживания.
В следующем примере будут отобраны имена сотрудников и имена отделов, в которых они работают:
| e.FName | d.Name |
|---|---|
| Джеймс | HR |
| Джон | HR |
| Ричард | Продажи |
Левая внешняя связь
Левая внешняя регистрация (также известная как «Левая регистрация» или «Внешняя связь») — это объединение, которое обеспечивает отображение всех строк из левой таблицы; если не существует подходящей строки из правой таблицы, соответствующие поля — NULL .
В следующем примере будут отобраны все отделы и первое имя сотрудников, работающих в этом отделе. Отделы без сотрудников все еще возвращаются в результатах, но будут иметь NULL для имени сотрудника:
| Departments.Name | Employees.FName |
|---|---|
| HR | Джеймс |
| HR | Джон |
| HR | Джонатон |
| Продажи | Майкл |
| Технология | НОЛЬ |
Так как же это работает?
В предложении FROM есть две таблицы:
| Я бы | FName | LName | Номер телефона | ManagerID | DepartmentID | Оплата труда | Дата приема на работу |
|---|---|---|---|---|---|---|---|
| 1 | Джеймс | кузнец | 1234567890 | НОЛЬ | 1 | 1000 | 01-01-2002 |
| 2 | Джон | Джонсон | 2468101214 | 1 | 1 | 400 | 23-03-2005 |
| 3 | Майкл | Williams | 1357911131 | 1 | 2 | 600 | 12-05-2009 |
| 4 | Джонатон | кузнец | 1212121212 | 2 | 1 | 500 | 24-07-2016 |
| Я бы | название |
|---|---|
| 1 | HR |
| 2 | Продажи |
| 3 | Технология |
Сначала из двух таблиц создается декартово произведение, дающее промежуточную таблицу.
Записи, соответствующие критериям соединения ( Departments.Id = Employees.DepartmentId ) выделены жирным шрифтом; они передаются на следующий этап запроса.
Поскольку это LEFT OUTER JOIN, все записи возвращаются со стороны LEFT соединения (Departments), в то время как любые записи на стороне RIGHT имеют маркер NULL, если они не соответствуют критериям соединения. В приведенной ниже таблице возвращается Tech с NULL
| Я бы | название | Я бы | FName | LName | Номер телефона | ManagerID | DepartmentID | Оплата труда | Дата приема на работу |
|---|---|---|---|---|---|---|---|---|---|
| 1 | HR | 1 | Джеймс | кузнец | 1234567890 | НОЛЬ | 1 | 1000 | 01-01-2002 |
| 1 | HR | 2 | Джон | Джонсон | 2468101214 | 1 | 1 | 400 | 23-03-2005 |
| 1 | HR | 3 | Майкл | Williams | 1357911131 | 1 | 2 | 600 | 12-05-2009 |
| 1 | HR | 4 | Джонатон | кузнец | 1212121212 | 2 | 1 | 500 | 24-07-2016 |
| 2 | Продажи | 1 | Джеймс | кузнец | 1234567890 | НОЛЬ | 1 | 1000 | 01-01-2002 |
| 2 | Продажи | 2 | Джон | Джонсон | 2468101214 | 1 | 1 | 400 | 23-03-2005 |
| 2 | Продажи | 3 | Майкл | Williams | 1357911131 | 1 | 2 | 600 | 12-05-2009 |
| 2 | Продажи | 4 | Джонатон | кузнец | 1212121212 | 2 | 1 | 500 | 24-07-2016 |
| 3 | Технология | 1 | Джеймс | кузнец | 1234567890 | НОЛЬ | 1 | 1000 | 01-01-2002 |
| 3 | Технология | 2 | Джон | Джонсон | 2468101214 | 1 | 1 | 400 | 23-03-2005 |
| 3 | Технология | 3 | Майкл | Williams | 1357911131 | 1 | 2 | 600 | 12-05-2009 |
| 3 | Технология | 4 | Джонатон | кузнец | 1212121212 | 2 | 1 | 500 | 24-07-2016 |
Наконец, каждое выражение, используемое в предложении SELECT , вычисляется для возврата нашей итоговой таблицы:
| Departments.Name | Employees.FName |
|---|---|
| HR | Джеймс |
| HR | Джон |
| Продажи | Ричард |
| Технология | НОЛЬ |
Self Join
Таблица может быть соединена с самим собой, с разными строками, соответствующими друг другу. В этом случае использования следует использовать псевдонимы для того, чтобы отличить два вхождения таблицы.
В приведенном ниже примере для каждого сотрудника в таблице базы данных примера базы данных возвращается запись, содержащая имя первого сотрудника вместе с соответствующим первым именем менеджера сотрудника. Поскольку менеджеры также являются сотрудниками, таблица объединяется с собой:
Этот запрос вернет следующие данные:
| Работник | Менеджер |
|---|---|
| Джон | Джеймс |
| Майкл | Джеймс |
| Джонатон | Джон |
Так как же это работает?
Исходная таблица содержит следующие записи:
| Я бы | FName | LName | Номер телефона | ManagerID | DepartmentID | Оплата труда | Дата приема на работу |
|---|---|---|---|---|---|---|---|
| 1 | Джеймс | кузнец | 1234567890 | НОЛЬ | 1 | 1000 | 01-01-2002 |
| 2 | Джон | Джонсон | 2468101214 | 1 | 1 | 400 | 23-03-2005 |
| 3 | Майкл | Williams | 1357911131 | 1 | 2 | 600 | 12-05-2009 |
| 4 | Джонатон | кузнец | 1212121212 | 2 | 1 | 500 | 24-07-2016 |
Первое действие — создать декартово произведение всех записей в таблицах, используемых в предложении FROM . В этом случае это таблица Employees дважды, поэтому промежуточная таблица будет выглядеть так (я удалил все поля, не используемые в этом примере):
| e.Id | e.FName | e.ManagerId | m.Id | m.FName | m.ManagerId |
|---|---|---|---|---|---|
| 1 | Джеймс | НОЛЬ | 1 | Джеймс | НОЛЬ |
| 1 | Джеймс | НОЛЬ | 2 | Джон | 1 |
| 1 | Джеймс | НОЛЬ | 3 | Майкл | 1 |
| 1 | Джеймс | НОЛЬ | 4 | Джонатон | 2 |
| 2 | Джон | 1 | 1 | Джеймс | НОЛЬ |
| 2 | Джон | 1 | 2 | Джон | 1 |
| 2 | Джон | 1 | 3 | Майкл | 1 |
| 2 | Джон | 1 | 4 | Джонатон | 2 |
| 3 | Майкл | 1 | 1 | Джеймс | НОЛЬ |
| 3 | Майкл | 1 | 2 | Джон | 1 |
| 3 | Майкл | 1 | 3 | Майкл | 1 |
| 3 | Майкл | 1 | 4 | Джонатон | 2 |
| 4 | Джонатон | 2 | 1 | Джеймс | НОЛЬ |
| 4 | Джонатон | 2 | 2 | Джон | 1 |
| 4 | Джонатон | 2 | 3 | Майкл | 1 |
| 4 | Джонатон | 2 | 4 | Джонатон | 2 |
Следующее действие заключается только в том, чтобы сохранить записи, соответствующие критериям JOIN , поэтому любые записи, где aliased e table ManagerId равен Id таблицы aliased m :
| e.Id | e.FName | e.ManagerId | m.Id | m.FName | m.ManagerId |
|---|---|---|---|---|---|
| 2 | Джон | 1 | 1 | Джеймс | НОЛЬ |
| 3 | Майкл | 1 | 1 | Джеймс | НОЛЬ |
| 4 | Джонатон | 2 | 2 | Джон | 1 |
Затем каждое выражение, используемое в предложении SELECT , вычисляется для возврата этой таблицы:
| e.FName | m.FName |
|---|---|
| Джон | Джеймс |
| Майкл | Джеймс |
| Джонатон | Джон |
Наконец, имена столбцов e.FName и m.FName заменяются именами их псевдонимов, назначенными оператору AS :
| Работник | Менеджер |
|---|---|
| Джон | Джеймс |
| Майкл | Джеймс |
| Джонатон | Джон |
ПЕРЕКРЕСТНЫЙ ПРИСОЕДИНЕНИЕ
Cross join делает декартово произведение двух членов. Декартово произведение означает, что каждая строка из одной таблицы объединяется с каждой строкой второй таблицы в соединении. Например, если TABLEA имеет 20 строк и TABLEB имеет 20 строк, результатом будет 20*20 = 400 строк вывода.
| d.Name | e.FName |
|---|---|
| HR | Джеймс |
| HR | Джон |
| HR | Майкл |
| HR | Джонатон |
| Продажи | Джеймс |
| Продажи | Джон |
| Продажи | Майкл |
| Продажи | Джонатон |
| Технология | Джеймс |
| Технология | Джон |
| Технология | Майкл |
| Технология | Джонатон |
Рекомендуется написать явный CROSS JOIN, если вы хотите сделать декартовое соединение, чтобы подчеркнуть, что это то, что вы хотите.
Присоединение к подзапросу
Объединение подзапроса часто используется, когда вы хотите получить агрегированные данные из таблицы child / details и отображать их вместе с записями из таблицы parent / header. Например, вы можете захотеть получить количество дочерних записей, среднее число числовых столбцов в дочерних записях или верхнюю или нижнюю строку на основе поля даты или числа. В этом примере используются псевдонимы, которые, возможно, упрощают чтение запросов при использовании нескольких таблиц. Вот как выглядит довольно типичное подзапрос. В этом случае мы извлекаем все строки из родительской таблицы «Заказы на поставку» и извлекаем только первую строку для каждой родительской записи дочерней таблицы PurchaseOrderLineItems.
КРЕСТ ПРИМЕНЯЕТСЯ И ПОСЛЕДНЕЕ СОЕДИНЕНИЕ
Очень интересным типом JOIN является LATERAL JOIN (новый в PostgreSQL 9.3+),
который также известен как CROSS APPLY / OUTER APPLY в SQL-Server & Oracle.
Основная идея заключается в том, что для каждой присоединяемой строки применяется табличная функция (или встроенный подзапрос).
Это позволяет, например, присоединить только первую совпадающую запись в другой таблице.
Разница между нормальным и боковым соединением заключается в том, что вы можете использовать столбец, который вы ранее вложили в подзапрос, который вы «CROSS APPLY».
левый | право | внутреннее соединение ЛАТЕРАЛЬНОГО
CROSS | ВНЕШНИЕ ПРИМЕНЕНИЯ
INNER JOIN LATERAL — это то же самое, что и CROSS APPLY
и LEFT JOIN LATERAL — это то же самое, что и OUTER APPLY
Пример использования (PostgreSQL 9.3+):
И для SQL-Server
ПОЛНОЕ СОЕДИНЕНИЕ
Один тип JOIN, который менее известен, — это ПОЛНЫЙ ПРИСОЕДИНЕНИЕ.
(Примечание: FULL JOIN не поддерживается MySQL в соответствии с 2016)
FULL OUTER JOIN возвращает все строки из левой таблицы и все строки из правой таблицы.
Если в левой таблице есть строки, которые не имеют совпадений в правой таблице, или если в правой таблице есть строки, которые не имеют совпадений в левой таблице, то эти строки также будут перечислены.
Обратите внимание: если вы используете soft-delete, вам нужно будет снова проверить статус soft-delete в предложении WHERE (потому что FULL JOIN ведет себя вроде как UNION);
Легко упустить этот маленький факт, поскольку вы добавляете AP_SoftDeleteStatus = 1 в предложение join.
Кроме того, если вы выполняете ПОЛНЫЙ ПРИСОЕДИНЯЙТЕСЬ, вам обычно нужно разрешить NULL в предложении WHERE; забыв позволить NULL по значению, будет иметь те же эффекты, что и INNER join, что вам не нужно, если вы выполняете ПОЛНЫЙ JOIN.
Рекурсивные СОБЫТИЯ
Рекурсивные объединения часто используются для получения данных родитель-ребенка. В SQL они реализованы с использованием рекурсивных общих табличных выражений , например:
Различия между внутренними / внешними соединениями
SQL имеет различные типы соединений, чтобы указать, включены ли в результат (не) совпадающие строки: INNER JOIN , LEFT OUTER JOIN , RIGHT OUTER JOIN и FULL OUTER JOIN (ключевые слова INNER и OUTER являются необязательными). На следующем рисунке показаны различия между этими типами объединений: синяя область представляет результаты, возвращаемые соединением, а белая область представляет результаты, которые соединение не будет возвращено.
Перекрестное знакомство с символической презентацией SQL ( ссылка ):
Ниже приведены примеры из этого ответа.
Например, существуют две таблицы:
Заметим, что (1,2) уникальны для A, (3,4) являются общими, и (5,6) являются единственными для B.
Внутреннее соединение
Внутреннее соединение, использующее любой из эквивалентных запросов, дает пересечение двух таблиц, то есть двух строк, которые они имеют вместе:
Левое внешнее соединение
Левое внешнее соединение даст все строки в A плюс любые общие строки в B:
Правое внешнее соединение
Точно так же правое внешнее соединение даст все строки в B плюс любые общие строки в A:
Полное внешнее соединение
Полное внешнее соединение даст вам объединение A и B, т. Е. Все строки в A и все строки в B. Если что-то в A не имеет соответствующей базы данных в B, то B-часть имеет значение NULL и наоборот.
JOIN Терминология: Внутренняя, Наружная, Полу, Анти .
Допустим, у нас есть две таблицы (A и B), и некоторые из их строк соответствуют (относительно заданного условия JOIN, что бы это ни было в конкретном случае):
Мы можем использовать различные типы соединений для включения или исключения совпадающих или несогласованных строк с любой стороны и правильно называть объединение, выбирая соответствующие термины из приведенной выше диаграммы.
В приведенных ниже примерах используются следующие тестовые данные:
Внутреннее соединение
Объединяет левую и правую строки, которые соответствуют.
Левая внешняя связь
Иногда сокращается до «left join». Объединяет левую и правую строки, которые соответствуют, и включает несогласованные левые строки.
Правостороннее соединение
Иногда сокращается «правое соединение». Объединяет левую и правую строки, которые соответствуют, и включает несогласованные правые строки.
Полная внешняя связь
Иногда сокращается до «полного соединения». Союз левого и правого внешнего соединения.
Левая полуось
Включает левые строки, соответствующие строкам справа.
Правая полупрофессионал
Включает правые строки, которые соответствуют левым строкам.
Как вы можете видеть, нет специального синтаксиса IN для левого и правого полусоединения — мы достигаем эффекта просто путем переключения позиций таблицы в тексте SQL.
Left Anti Semi Join
Включает левые строки, которые не соответствуют строкам справа.
ПРЕДУПРЕЖДЕНИЕ. Будьте осторожны, если вы используете NOT IN в столбце NULL! Подробнее здесь .
Right Anti Semi Join
Включает правые строки, которые не соответствуют левым строкам.
Как вы можете видеть, нет специального синтаксиса NOT IN для левого и правого анти-полусоединения — мы достигаем эффекта просто путем переключения позиций таблицы в тексте SQL.
Крест
Декартово произведение всех левых со всеми правыми строками.
Кросс-соединение эквивалентно внутреннему соединению с условием соединения, которое всегда совпадает, поэтому следующий запрос вернул бы тот же результат:
Автообъединение
Это просто означает, что таблица соединяется сама с собой. Самосоединением может быть любой из типов соединений, рассмотренных выше. Например, это внутреннее самосоединение:
Соединение и объединение таблиц в 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
Тета-соединение
Условие сравнения столбцов соединения не обязательно должно быть равенством, но может быть любым другим сравнением. Соединение, в котором используется общее условие сравнения столбцов соединения, называется тета-соединением:
Самосоединение
Самосоединение — это естественное соединение таблицы с самой собой. При этом один столбец таблицы сравнивается сам с собой. Сравнивание столбца с самим собой означает, что в предложении FROM инструкции SELECT имя таблицы употребляется дважды. Поэтому необходимо иметь возможность ссылаться на имя одной и той же таблицы дважды. Это можно осуществить, используя, по крайней мере, один псевдоним. То же самое относится и к именам столбцов в условии соединения в инструкции SELECT. Для того чтобы различить столбцы с одинаковыми именами, необходимо использовать уточненные имена.
Полусоединение
Полусоединение похоже на естественное соединение, но возвращает только набор всех строк из одной таблицы, для которой в другой таблице есть одно или несколько совпадений.
Оператор UNION
Оператор UNION объединяет результаты двух или более запросов в один результирующий набор, в который входят все строки, принадлежащие всем запросам в объединении: