Что такое SQL JOIN
и каковы его типы?
SQL JOIN
?SQL JOIN
- это метод получения данных из двух или более таблиц базы данных.
SQL JOIN
?Всего существует пять JOIN
. К ним относятся:
1. JOIN or INNER JOIN
2. OUTER JOIN
2.1 LEFT OUTER JOIN or LEFT JOIN
2.2 RIGHT OUTER JOIN or RIGHT JOIN
2.3 FULL OUTER JOIN or FULL JOIN
3. NATURAL JOIN
4. CROSS JOIN
5. SELF JOIN
В этом типе JOIN
мы получаем все записи, которые соответствуют условию в обеих таблицах, а записи в обеих таблицах, которые не соответствуют условию, не сообщаются.
Другими словами, INNER JOIN
основан на единственном факте, что: ТОЛЬКО совпадающие записи в обеих таблицах ДОЛЖНЫ быть перечислены.
Обратите внимание, что JOIN
без каких-либо других ключевых слов JOIN
(таких как INNER
, OUTER
, LEFT
и т.д.) является INNER JOIN
. Другими словами, JOIN
является
синтаксический сахар для INNER JOIN
(см.: https://stackoverflow.com/questions/565620/difference-between-join-and-inner-join).
OUTER JOIN
извлекает
Либо, совпадающие строки из одной таблицы и все строки в другой таблице либо, все строки во всех таблицах (не имеет значения, есть ли совпадение или нет).
Существует три вида внешнего соединения:
2.1 LEFT OUTER JOIN или LEFT JOIN.
Это соединение возвращает все строки из левой таблицы в сочетании с соответствующими строками из
правой таблицы. Если в правой таблице нет совпадающих столбцов, возвращаются значения NULL
.
2.2 ПРЯМОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ или ПРЯМОЕ СОЕДИНЕНИЕ.
Этот JOIN
возвращает все строки из правой таблицы в сочетании с совпадающими строками из
левой таблицы. Если в левой таблице нет совпадающих столбцов, возвращаются значения NULL
.
2.3 FULL OUTER JOIN или ПОЛНЫЙ ДЖОИН.
Этот JOIN
объединяет LEFT OUTER JOIN
и RIGHT OUTER JOIN
. Он возвращает строки из любой таблицы при выполнении условий и возвращает значение NULL
при отсутствии совпадений.
Другими словами, OUTER JOIN
основан на том, что: В списке должны быть только совпадающие записи в ОДНОЙ из таблиц (ПРЯМОЙ или ЛЕВОЙ) или ОБОИХ таблицах (ПОЛНОСТЬЮ).
Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.
Оно основано на двух условиях:
JOIN
делается на всех столбцах с одинаковым именем для равенства.Это кажется скорее теоретическим, и в результате (вероятно) большинство СУБД даже не пытаются поддерживать это.
Это декартово произведение двух таблиц. Результат CROSS JOIN
не будет иметь смысла
в большинстве ситуаций. Более того, он нам вообще не понадобится (или понадобится в наименьшей степени, если быть точным).
Это не другая форма JOIN
, скорее это JOIN
(INNER
, OUTER
и т.д.) таблицы к самой себе.
В зависимости от оператора, используемого в предложении JOIN
, может быть два типа JOIN
. Это
Экви JOIN
Тета СОЕДИНЕНИЕ
Для любого типа JOIN
(INNER
, OUTER
и т.д.), если мы используем ТОЛЬКО оператор равенства (=), то мы говорим, что
что JOIN
является EQUI JOIN
.
Это то же самое, что и EQUI JOIN
, но позволяет использовать все другие операторы, такие как >, <, >= и т.д.
Многие считают и
EQUI JOIN
и ThetaJOIN
похожими наINNER
,OUTER
и т.п.JOIN
. Но я твердо убежден, что это ошибка и делает идеи расплывчатыми. Потому чтоINNER JOIN
,OUTER JOIN
и т.д. все связаны с таблицами и их данными, в то время какEQUI JOIN
иTHETA JOIN
связаны только > с операторами. связаны только с операторами, которые мы используем в первом случае.Опять же, есть много тех, кто считает
NATURAL JOIN
чем-то вроде "своеобразный"EQUI JOIN
. На самом деле, это действительно так, поскольку первое условия, которое я упомянул дляNATURAL JOIN
. Однако, мы не обязаны ограничивать это толькоNATURAL JOIN
.ВНУТРЕННИЕ СОЕДИНЕНИЯ
,ВНЕШНИЕ СОЕДИНЕНИЯ
и т.д. тоже могут бытьEQUI JOIN
.
Определение:
JOINS - это способ запросить данные, объединенные из нескольких таблиц одновременно.
В РСУБД существует 5 типов объединений:
Equi-Join: Объединяет общие записи из двух таблиц на основе условия равенства. Технически, объединение выполняется с помощью оператора равенства (=) для сравнения значений первичного ключа одной таблицы и значений внешнего ключа другой таблицы, поэтому набор результатов включает общие (совпадающие) записи из обеих таблиц. О реализации см. в разделе INNER-JOIN.
Natural-Join: Это улучшенная версия Equi-Join, в которой операция SELECT исключает дублирующие столбцы. Для реализации см. раздел INNER-JOIN
Non-Equi-Join: Это обратная версия Equi-join, где условие соединения использует другие операторы, чем оператор равенства (=), например, !=, <=, >=, >, < или BETWEEN и т.д. О реализации см. в разделе INNER-JOIN.
Self-Join:: Настроенное поведение объединения, когда таблица объединяется сама с собой; обычно это необходимо для запросов к таблицам с самоссылками (или сущностям с унарными отношениями). Для реализации см. раздел INNER-JOINs.
Cartesian Product: Это перекрестное объединение всех записей обеих таблиц без каких-либо условий. Технически, он возвращает набор результатов запроса без WHERE-клаузулы.
Согласно концепции и развитию SQL, существует 3 типа соединений, и все соединения в СУБД могут быть достигнуты с помощью этих типов соединений.
INNER-JOIN: Это слияние (или объединение) совпадающих строк из двух таблиц. Совмещение выполняется на основе общих столбцов таблиц и операции их сравнения. Если условие основано на равенстве, то: выполняется EQUI-JOIN, в противном случае - Non-EQUI-Join.
OUTER-JOIN: Он объединяет (или комбинирует) совпадающие строки из двух таблиц и несовпадающие строки с NULL значениями. Однако, можно настроить выбор несовпадающих строк, например, выбрать несовпадающую строку из первой или второй таблицы по подтипам: LEFT OUTER JOIN и RIGHT OUTER JOIN.
2.1. ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ (оно же LEFT-JOIN): Возвращает совпадающие строки из двух таблиц и несовпадающие только из левой таблицы (т.е. первой таблицы).
2.2. ПРАВОЕ внешнее объединение (оно же RIGHT-JOIN): Возвращает совпадающие строки из двух таблиц и несмежные только из ПРАВОЙ таблицы.
2.3. ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ (оно же ВНЕШНЕЕ СОЕДИНЕНИЕ): Возвращает совпадающие и несовпадающие строки из обеих таблиц.
CROSS-JOIN: Это соединение не объединяет/сливает, вместо этого оно выполняет декартово произведение.
Примечание: Self-JOIN может быть достигнуто с помощью INNER-JOIN, OUTER-JOIN и CROSS-JOIN в зависимости от требований, но таблица должна соединяться сама с собой.
1.1: INNER-JOIN: реализация Equi-join.
SELECT *
FROM Table1 A
INNER JOIN Table2 B ON A.<Primary-Key> =B.<Foreign-Key>;
1.2: INNER-JOIN: реализация Natural-JOIN
Select A.*, B.Col1, B.Col2 --But no B.ForeignKeyColumn in Select
FROM Table1 A
INNER JOIN Table2 B On A.Pk = B.Fk;
1.3: INNER-JOIN с реализацией NON-Equi-join.
Select *
FROM Table1 A INNER JOIN Table2 B On A.Pk <= B.Fk;
1.4: INNER-JOIN с SELF-JOIN.
Select *
FROM Table1 A1 INNER JOIN Table1 A2 On A1.Pk = A2.Fk;
2.1: OUTER JOIN (полное внешнее соединение).
Select *
FROM Table1 A FULL OUTER JOIN Table2 B On A.Pk = B.Fk;
2.2: ЛЕВОЕ СОЕДИНЕНИЕ
Select *
FROM Table1 A LEFT OUTER JOIN Table2 B On A.Pk = B.Fk;
2.3: ПРЯМОЕ СОЕДИНЕНИЕ
Select *
FROM Table1 A RIGHT OUTER JOIN Table2 B On A.Pk = B.Fk;
3.1: ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ
Select *
FROM TableA CROSS JOIN TableB;
3.2: CROSS JOIN-Self JOIN
Select *
FROM Table1 A1 CROSS JOIN Table1 A2;
//OR//
Select *
FROM Table1 A1,Table1 A2;
Интересно отметить, что большинство других ответов страдают от этих двух проблем:
Именно поэтому диаграммы Венна объяснить их так неточно, потому что объединение создает декартово произведение между двух соединяемых таблиц. В Википедии иллюстрирует это красиво: Синтаксис SQL для декартовым является перекрестное соединение`. Например:
SELECT *
-- This just generates all the days in January 2017
FROM generate_series(
'2017-01-01'::TIMESTAMP,
'2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
INTERVAL '1 day'
) AS days(day)
-- Here, we're combining all days with all departments
CROSS JOIN departments
Которая сочетает в себе все строки из одной таблицы все строки из другой таблицы: Источник: в
+--------+ +------------+
| day | | department |
+--------+ +------------+
| Jan 01 | | Dept 1 |
| Jan 02 | | Dept 2 |
| ... | | Dept 3 |
| Jan 30 | +------------+
| Jan 31 |
+--------+
Результат: в
+--------+------------+
| day | department |
+--------+------------+
| Jan 01 | Dept 1 |
| Jan 01 | Dept 2 |
| Jan 01 | Dept 3 |
| Jan 02 | Dept 1 |
| Jan 02 | Dept 2 |
| Jan 02 | Dept 3 |
| ... | ... |
| Jan 31 | Dept 1 |
| Jan 31 | Dept 2 |
| Jan 31 | Dept 3 |
+--------+------------+
Если мы просто пишем их через запятую таблицы, мы'll получить то же:
-- CROSS JOINing two tables:
SELECT * FROM table1, table2
Для себя внутреннее соединение-это просто отфильтрован перекрестное соединение, где предикат фильтра называется тета
в реляционной алгебре.
Например:
SELECT *
-- Same as before
FROM generate_series(
'2017-01-01'::TIMESTAMP,
'2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
INTERVAL '1 day'
) AS days(day)
-- Now, exclude all days/departments combinations for
-- days before the department was created
JOIN departments AS d ON day >= d.created_at
Обратите внимание, что ключевое слово "внешнее" является обязательным (за исключением в MS доступ). (посмотреть на статью приводить примеры)
Особый вид тэта-соединение обор присоединиться, которые мы используем наиболее. Сказуемое присоединяется к первичному ключу одной таблицы с внешним ключом другой таблицы. Если мы используем базы данных "sakila" для иллюстрации, мы можем написать:
SELECT *
FROM actor AS a
JOIN film_actor AS fa ON a.actor_id = fa.actor_id
JOIN film AS f ON f.film_id = fa.film_id
Это объединяет всех участников с их фильмами. Или же, в некоторых базах данных:
SELECT *
FROM actor
JOIN film_actor USING (actor_id)
JOIN film USING (film_id)
`С помощью (синтаксис) предназначена для указания столбцов, которые должны присутствовать на каждой стороне операции Join'таблиц S и создает предикат равенства этих двух столбцов.
Другие ответы перечислили это на "либо" по отдельности, но это вовсе'т смысл. Это's просто синтаксис сахара форма для обор соединение, которое является частным случаем тета-соединения или внутреннее соединение. Естественное соединение просто собирает все столбцы, которые являются общими для обоих таблицах присоединился и присоединяется с помощью()эти столбцы. Что вряд ли когда-нибудь пригодятся, из-за случайного совпадения (как
LAST_UPDATE` столбцов в базы данных "sakila").
Здесь'ы синтаксис:
SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film
Теперь, внешнее объединение
немного отличается от внутреннее соединение
как создается Союз
из нескольких декартовым. Мы можем написать:
-- Convenient syntax:
SELECT *
FROM a LEFT JOIN b ON <predicate>
-- Cumbersome, equivalent syntax:
SELECT a.*, b.*
FROM a JOIN b ON <predicate>
UNION ALL
SELECT a.*, NULL, NULL, ..., NULL
FROM a
WHERE NOT EXISTS (
SELECT * FROM b WHERE <predicate>
)
Никто не хочет написать, как последние, так и запишем внешнее
(который, как правило, лучше оптимизирован для баз данных).
Как внутренний
, ключевое слово "внешние" не является обязательным, здесь.
Внешнее объединение
в трех вариантах:
: обе таблицы "присоединиться" выражение добавляются в Союз, как показано выше. Все они могут быть объединены с сайта с помощью()
или природных
(я'вэ на самом деле был реальный случай на природные полный присоединись к недавно) Есть некоторые исторические, поддерживается в Oracle и SQL Server, который поддерживает внешнее
уже стандарт SQL был синтаксис синтаксис:
-- Oracle
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id = fa.actor_id(+)
AND fa.film_id = f.film_id(+)
-- SQL Server
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id *= fa.actor_id
AND fa.film_id *= f.film_id
Сказав так, Дон'т использовать этот синтаксис. Я просто перечислю здесь, так что вы можете признать его от старого блога / устаревший код.
внешнее объединение
Мало кто знает, но стандарт SQL определяет секционированных внешнее
(и Oracle реализует его). Вы можете писать такие вещи:
WITH
-- Using CONNECT BY to generate all dates in January
days(day) AS (
SELECT DATE '2017-01-01' + LEVEL - 1
FROM dual
CONNECT BY LEVEL <= 31
),
-- Our departments
departments(department, created_at) AS (
SELECT 'Dept 1', DATE '2017-01-10' FROM dual UNION ALL
SELECT 'Dept 2', DATE '2017-01-11' FROM dual UNION ALL
SELECT 'Dept 3', DATE '2017-01-12' FROM dual UNION ALL
SELECT 'Dept 4', DATE '2017-04-01' FROM dual UNION ALL
SELECT 'Dept 5', DATE '2017-04-02' FROM dual
)
SELECT *
FROM days
LEFT JOIN departments
PARTITION BY (department) -- This is where the magic happens
ON day >= created_at
Частей в результате: в
+--------+------------+------------+
| day | department | created_at |
+--------+------------+------------+
| Jan 01 | Dept 1 | | -- Didn't match, but still get row
| Jan 02 | Dept 1 | | -- Didn't match, but still get row
| ... | Dept 1 | | -- Didn't match, but still get row
| Jan 09 | Dept 1 | | -- Didn't match, but still get row
| Jan 10 | Dept 1 | Jan 10 | -- Matches, so get join result
| Jan 11 | Dept 1 | Jan 10 | -- Matches, so get join result
| Jan 12 | Dept 1 | Jan 10 | -- Matches, so get join result
| ... | Dept 1 | Jan 10 | -- Matches, so get join result
| Jan 31 | Dept 1 | Jan 10 | -- Matches, so get join result
Дело в том, что все строки из секции соединения окажемся в конечном итоге независимо от того, присоединиться
ничего не соответствуют о "другую сторону вступить на". Длинная короткая история: это заполнить разреженные данные в отчетах. Очень полезно!
Серьезно? Никакой другой ответ получил? Конечно, нет, потому что это не'т иметь собственный синтаксис в SQL, к сожалению]7 (так же, как анти-присоединение ниже). Но мы можем использовать в () и существует()
, например, чтобы найти всех актеров, которые играли в фильмах:
SELECT *
FROM actor a
WHERE EXISTS (
SELECT * FROM film_actor fa
WHERE a.actor_id = fa.actor_id
)
В где.actor_id = ФА.actor_id
предикат выступает как предикат полусоединения. Если вы Don'т поверить, проверить планы выполнения, например, в Oracle. Вы'увидите, что база данных выполняет операции полусоединения, не существует()` предикат.
Это прямо противоположно полусоединения (будьте осторожны, не используйте не в
если, а это важный нюанс)
Здесь представлены все актеры без пленки:
SELECT *
FROM actor a
WHERE NOT EXISTS (
SELECT * FROM film_actor fa
WHERE a.actor_id = fa.actor_id
)
Некоторые люди (в частности MySQL) также писать против присоединиться, как это:
SELECT *
FROM actor a
LEFT JOIN film_actor fa
USING (actor_id)
WHERE film_id IS NULL
Я думаю, что историческая причина-это производительность.
ОМГ, это слишком круто. Я'м только один, чтобы упомянуть его? Здесь'ы прохладном запроса:
SELECT a.first_name, a.last_name, f.*
FROM actor AS a
LEFT OUTER JOIN LATERAL (
SELECT f.title, SUM(amount) AS revenue
FROM film AS f
JOIN film_actor AS fa USING (film_id)
JOIN inventory AS i USING (film_id)
JOIN rental AS r USING (inventory_id)
JOIN payment AS p USING (rental_id)
WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
GROUP BY f.film_id
ORDER BY revenue DESC
LIMIT 5
) AS f
ON true
Он найдет ТОП-5 компания по производству фильмов одного актера. Каждый раз, когда вам нужен топ-Н-в-то запрос, боковое присоединиться
будет вашим другом. Если вы'вновь на SQL сервере человека, тогда вы знаете, этого "присоединиться" типа под название "применить"
SELECT a.first_name, a.last_name, f.*
FROM actor AS a
OUTER APPLY (
SELECT f.title, SUM(amount) AS revenue
FROM film AS f
JOIN film_actor AS fa ON f.film_id = fa.film_id
JOIN inventory AS i ON f.film_id = i.film_id
JOIN rental AS r ON i.inventory_id = r.inventory_id
JOIN payment AS p ON r.rental_id = p.rental_id
WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
GROUP BY f.film_id
ORDER BY revenue DESC
LIMIT 5
) AS f
ОК, возможно, что'ы обман, потому что боковое присоединиться
или применить
выражение действительно на "коррелированный подзапрос", которую производит несколько рядов. Но если мы допускаем, что "коррелированные подзапросы" мы можем также говорить о...
Это только действительно реализованный в Oracle и Informix (насколько мне известно), но его можно эмулировать в PostgreSQL с использованием массивов и/или XML и SQL-сервером с помощью XML.
МУЛЬТИНАБОР
производит коррелированный подзапрос и гнезда результирующий набор строки во внешнем запросе. Приведенный ниже запрос выбирает все актеры и каждый актер собирает свои фильмы во вложенной коллекции:
SELECT a.*, MULTISET (
SELECT f.*
FROM film AS f
JOIN film_actor AS fa USING (film_id)
WHERE a.actor_id = fa.actor_id
) AS films
FROM actor
Как вы видели, есть несколько типов присоединиться, чем просто в "скучно" и внутренний
, внешний
и `перекрестного соединения, которые обычно упоминаются. Более подробно в моей статье. И, пожалуйста, прекратите использование диаграммы Венна для иллюстрации их.
Я'м собираюсь давить на мое больное место: с помощью ключевых слов.
Если обе таблицы на обеих сторонах соединения имеют свои внешние ключи правильно назвали (т. е. то же имя, не просто и"ИД), то это может быть использовано:
SELECT ...
FROM customers JOIN orders USING (customer_id)
Я нахожу это очень практичным, читабельным и не используется достаточно часто.