SQL JOIN и различные типы JOINов

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: Это соединение не объединяет/сливает, вместо этого оно выполняет декартово произведение.

![введите описание изображения здесь][1] Примечание: Self-JOIN может быть достигнуто с помощью INNER-JOIN, OUTER-JOIN и CROSS-JOIN в зависимости от требований, но таблица должна соединяться сама с собой.

[Дополнительная информация:][2].

Примеры:

1.1: INNER-JOIN: реализация Equi-join.

SELECT  *
FROM Table1 A 
 INNER JOIN Table2 B ON A. =B.;

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

dev ツ

Редактировал ответ 9-го августа 2019 в 11:55

66

0

Комментарии (2)

Lukas Eder

Источник Неподдерживаемая версия AMP Редактировать

Интересно отметить, что большинство других ответов страдают от этих двух проблем:

Они сосредоточены на основных форм присоединиться только
Они (АБ)использовать диаграммы Венна, которые неточный инструмент для визуализации соединений (они'повторно гораздо лучше для профсоюзов). Я'вэ недавно написал статью на эту тему: а, вероятно, неполные, с различными способами соединения таблиц в SQL, которые я'МР обобщить здесь.
Первое и самое главное: соединения декартовым

Именно поэтому диаграммы Венна объяснить их так неточно, потому что объединение создает декартово произведение между двух соединяемых таблиц. В Википедии иллюстрирует это красиво: Синтаксис 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 

-- Cumbersome, equivalent syntax:
SELECT a.*, b.*
FROM a JOIN b ON 
UNION ALL
SELECT a.*, NULL, NULL, ..., NULL
FROM a
WHERE NOT EXISTS (
  SELECT * FROM b WHERE 
)

Никто не хочет написать, как последние, так и запишем внешнее (который, как правило, лучше оптимизирован для баз данных). Как внутренний, ключевое слово "внешние" не является обязательным, здесь. Внешнее объединение в трех вариантах:

Осталось [ внешний ] присоединиться`: слева таблица "присоединиться" выражение добавляется к союзу, как показано выше.
Да, [ внешний ] присоединяйтесь: право таблица "присоединиться" выражение добавляется к союзу, как показано выше.
Полное [ внешнее ] соединение: обе таблицы "присоединиться" выражение добавляются в Союз, как показано выше. Все они могут быть объединены с сайта с помощью() или природных (я'вэ на самом деле был реальный случай на природные полный присоединись к недавно)
Альтернативные синтаксисы

Есть некоторые исторические, поддерживается в 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 = 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

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

38

0

Комментарии (2)

Gisway

Источник Неподдерживаемая версия AMP Редактировать

Я создал иллюстрацию, которая объясняет лучше, чем слова, на мой взгляд:

TylerH

Редактировал ответ 7-го марта 2018 в 7:27

9

0

Комментарии (2)

peufeu

Источник Неподдерживаемая версия AMP Редактировать

Я'м собираюсь давить на мое больное место: с помощью ключевых слов.

Если обе таблицы на обеих сторонах соединения имеют свои внешние ключи правильно назвали (т. е. то же имя, не просто и"ИД), то это может быть использовано:

SELECT ...
FROM customers JOIN orders USING (customer_id)

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

TylerH

Редактировал ответ 7-го марта 2018 в 7:27

-3

0

Комментарии (1)

M-D · Accepted Answer · 2013-07-30T11:48:12+00:00

Что такое `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 мы получаем все записи, которые соответствуют условию в обеих таблицах, а записи в обеих таблицах, которые не соответствуют условию, не сообщаются.

Другими словами, 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:

Это декартово произведение двух таблиц. Результат CROSS JOIN не будет иметь смысла в большинстве ситуаций. Более того, он нам вообще не понадобится (или понадобится в наименьшей степени, если быть точным).

SELF JOIN:

Это не другая форма JOIN, скорее это JOIN (INNER, OUTER и т.д.) таблицы к самой себе.

JOINs based on Operators

В зависимости от оператора, используемого в предложении JOIN, может быть два типа JOIN. Это

Экви JOIN
Тета СОЕДИНЕНИЕ
Equi JOIN:

Для любого типа JOIN (INNER, OUTER и т.д.), если мы используем ТОЛЬКО оператор равенства (=), то мы говорим, что что JOIN является EQUI JOIN.

Тета JOIN:

Это то же самое, что и EQUI JOIN, но позволяет использовать все другие операторы, такие как >, = и т.д.

Многие считают и EQUI JOIN и Theta JOIN похожими на INNER, OUTER и т.п. JOIN. Но я твердо убежден, что это ошибка и делает идеи расплывчатыми. Потому что INNER JOIN, OUTER JOIN и т.д. все связаны с таблицами и их данными, в то время как EQUI JOIN и THETA JOIN связаны только > с операторами. связаны только с операторами, которые мы используем в первом случае.

Опять же, есть много тех, кто считает NATURAL JOIN чем-то вроде "своеобразный" EQUI JOIN. На самом деле, это действительно так, поскольку первое условия, которое я упомянул для NATURAL JOIN. Однако, мы не обязаны ограничивать это только NATURAL JOIN. ВНУТРЕННИЕ СОЕДИНЕНИЯ, ВНЕШНИЕ СОЕДИНЕНИЯ и т.д. тоже могут быть EQUI JOIN.

SQL JOIN и различные типы JOINов

Что такое SQL JOIN?

Чем отличаются SQL JOIN?

JOIN или INNER JOIN:

ВНЕШНЕЕ СОЕДИНЕНИЕ:

НАТУРАЛЬНОЕ СОЕДИНЕНИЕ:

CROSS JOIN:

SELF JOIN:

JOINs based on Operators

Equi JOIN:

Тета JOIN:

Типы JOINS:

Примеры:

Первое и самое главное: соединения декартовым

Внутреннее соединение (тэта-соединение)

ОБОР ПРИСОЕДИНИТЬСЯ

ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ

ВНЕШНЕЕ СОЕДИНЕНИЕ

Альтернативные синтаксисы

Секционированных внешнее объединение

ПОЛУСОЕДИНЕНИЯ

АНТИ ПРИСОЕДИНИТЬСЯ

ПРИСОЕДИНЯЙТЕСЬ К ЛАТЕРАЛЬНОЙ

МУЛЬТИМНОЖЕСТВО

Что такое `SQL JOIN`?

Чем отличаются `SQL JOIN`?

Секционированных `внешнее объединение`