Лучшая иллюстрация к теории

Определение:

JOINS - это способ запроса данных, которые объединены вместе из нескольких таблиц одновременно.

Типы СОЕДИНЕНИЙ:

В отношении СУРБД существует 5 типов соединений:

• Equi-Join: Объединяет общие записи из двух таблиц на основе условия равенства. Технически, Join, используя равенство-operator (=) для сравнения значений PrimaryKey одной таблицы и значений Foriegn Key для таблицы antoher, поэтому набор результатов включает общие (согласованные) записи из обеих таблиц. Для реализации см. INNER-JOIN.

• Natural-Join: Это расширенная версия Equi-Join, в которой SELECT операция пропускает дублирующийся столбец. Для реализации см. INNER-JOIN

• Non-Equi-Join: Это обратное выражение Equi-join, в котором условие объединения используется иначе, чем равный оператор (=), например,! =, < =, >= > , < или BETWEEN и т.д. Для реализации см. INNER-JOIN.

• Self-Join:: настроенное поведение соединения, где таблица объединена с самим собой; Это обычно необходимо для запроса таблиц саморегуляции (или сущности унитарного отношения). Для реализации см. INNER-JOINs.

• Декартовы продукты:. Этот крест объединяет все записи обеих таблиц без каких-либо условий. Технически он возвращает результирующий набор запроса без WHERE-clause.

По соображениям SQL и продвижению, существуют 3 типа соединений, и все объединения RDBMS могут быть достигнуты с использованием этих типов объединений.

• INNER-JOIN: Он объединяет (или комбинирует) согласованные строки из двух таблиц. Согласование выполняется на основе общих столбцов таблиц и их операции сравнения. Если условие, основанное на равенстве, тогда выполняется: EQUI-JOIN, иначе не-EQUI-Join.

• ** OUTER-JOIN: ** Он объединяет (или комбинирует) согласованные строки из двух таблиц и несравнимых строк со значениями NULL. Тем не менее, можно настроить выбор не совпадающих строк, например, выбрав непревзойденную строку из первой таблицы или второй таблицы подтипами: LEFT OUTER JOIN и RIGHT OUTER JOIN.

  2,1. LEFT Outer JOIN (a.k.a, LEFT-JOIN): Возвращает согласованные строки, которые формируют две таблицы и удаляются из таблицы LEFT (т.е. первая таблица).

  2,2. RIGHT Outer JOIN (a.k.a, RIGHT-JOIN): Возвращает согласованные строки из двух таблиц и не имеет аналогов только из таблицы RIGHT.

  2,3. FULL OUTER JOIN (a.k.a OUTER JOIN): Возвращает совпадающие и несогласные из обеих таблиц.

• CROSS-JOIN: Это объединение не объединяется/объединяет, а выполняет карциническое произведение.

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

Для получения дополнительной информации:

Примеры:

1.1: INNER-JOIN: Выполнение равноудаления

SELECT  *
FROM Table1 A 
 INNER JOIN Table2 B ON A.<PrimaryKey> =B.<ForeignKey>;

1.2: INNER-JOIN: реализация Natural-JOIN

Select A.*, B.Col1, B.Col2          --But no B.ForiengKyeColumn in Select
 FROM Table1 A
 INNER JOIN Table2 B On A.Pk = B.Fk;

1.3: INNER-JOIN с реализацией N-Eqijoin

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: ВНЕШНЯЯ СОЕДИНЕННОСТЬ (полное внешнее соединение)

Select *
 FROM Table1 A FULL OUTER JOIN Table2 B On A.Pk = B.Fk;

2.2: LEFT JOIN

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: CROSS JOIN

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;

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

• Они сосредоточены только на базовых формах объединения
• Они (ab) используют диаграммы Венна, которые являются неточным инструментом для визуализации объединений (они намного лучше для союзов).

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

В первую очередь: JOIN являются декартовыми продуктами

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

Синтаксис SQL для декартовых произведений CROSS JOIN. Например:

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     |
+--------+------------+

Если мы просто напишем разделенный запятыми список таблиц, мы получим то же самое:

-- CROSS JOINing two tables:
SELECT * FROM table1, table2

INNER JOIN (Theta-JOIN)

An INNER JOIN является только фильтрованным CROSS JOIN, где предикат фильтра называется Theta в реляционной алгебре.

Например:

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

Обратите внимание, что ключевое слово INNER является необязательным (кроме MS Access).

(просмотрите статью для примеров результатов)

EQUI JOIN

Специальный тип Theta-JOIN - equi JOIN, который мы используем больше всего. Предикат соединяет первичный ключ одной таблицы с внешним ключом другой таблицы. Если мы используем базу данных 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)

Синтаксис USING() позволяет указать столбец, который должен присутствовать по обе стороны от таблиц операций JOIN, и создает предикат равенства для этих двух столбцов.

ПРИРОДНЫЙ ПРИСОЕДИНЯЙТЕСЬ

Другие ответы перечислили этот тип JOIN отдельно, но это не имеет смысла. Это просто синтаксическая форма сахара для equi JOIN, которая является частным случаем Theta-JOIN или INNER JOIN. NATURAL JOIN просто собирает все столбцы, которые являются общими для обеих соединяемых таблиц, и объединяет USING() эти столбцы. Что вряд ли когда-либо полезно из-за случайных совпадений (например, столбцов LAST_UPDATE в база данных Sakila).

Вот синтаксис:

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

ВЗАИМОДЕЙСТВИЕ

Теперь OUTER JOIN немного отличается от INNER JOIN, поскольку он создает UNION нескольких декартовых произведений. Мы можем написать:

-- 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>
)

Никто не хочет писать последнее, поэтому мы пишем OUTER JOIN (который обычно лучше оптимизируется базами данных).

Как INNER, ключевое слово OUTER является необязательным.

OUTER JOIN поставляется в трех вариантах:

• LEFT [ OUTER ] JOIN: левая таблица выражения JOIN добавляется к объединению, как показано выше.
• RIGHT [ OUTER ] JOIN: правая таблица выражения JOIN добавляется к объединению, как показано выше.
• FULL [ OUTER ] JOIN: Обе таблицы выражения JOIN добавляются в объединение, как показано выше.

Все они могут быть объединены с ключевым словом USING() или с NATURAL (У меня на самом деле был реальный случай использования для NATURAL FULL JOIN недавно)

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

В Oracle и SQL Server существуют некоторые исторические, устаревшие синтаксисы, которые поддерживали OUTER JOIN уже до того, как для стандарта 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

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

Разделенный OUTER JOIN

Немногие знают об этом, но стандарт SQL указывает секционированный OUTER JOIN (и 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

Дело в том, что все строки из секционированной стороны соединения будут завершаться в результате независимо от того, соответствует ли JOIN что-либо на "другой стороне JOIN". Короче говоря: это заполнение разреженных данных в отчетах. Очень полезно!

SEMI JOIN

Серьезно? Ни один другой ответ не получил этого? Конечно, нет, потому что у него нет собственного синтаксиса в SQL, к сожалению (как и ANTI JOIN ниже). Но мы можем использовать IN() и EXISTS(), например. найти всех актеров, которые играли в фильмах:

SELECT *
FROM actor a
WHERE EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Предикат WHERE a.actor_id = fa.actor_id действует как предикат полусоединения. Если вы этого не верите, проверьте планы выполнения, например. в Oracle. Вы увидите, что база данных выполняет операцию SEMI JOIN, а не предикат EXISTS().

ANTI JOIN

Это как раз противоположно SEMI JOIN (будьте осторожны, чтобы не использовать NOT IN хотя, так как у него есть важное предостережение)

Вот все актеры без фильмов:

SELECT *
FROM actor a
WHERE NOT EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Некоторые люди (особенно люди MySQL) также пишут ANTI JOIN следующим образом:

SELECT *
FROM actor a
LEFT JOIN film_actor fa
USING (actor_id)
WHERE film_id IS NULL

Я думаю, что историческая причина - это производительность.

БОЛЬШОЕ СОЕДИНЕНИЕ

OMG, это слишком круто. Я единственный, кто это упоминает? Вот классный запрос:

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 фильмов, производящих доходы, для каждого актера. Каждый раз, когда вам нужен запрос TOP-N-per-something, LATERAL JOIN будет вашим другом. Если вы являетесь человеком SQL Server, то вы знаете этот тип JOIN под именем APPLY

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

ОК, возможно, это обман, потому что выражение LATERAL JOIN или APPLY - это действительно "коррелированный подзапрос", который создает несколько строк. Но если мы допустим "коррелированные подзапросы", мы также можем поговорить о...

MULTISET

Это реально реализовано Oracle и Informix (насколько мне известно), но его можно эмулировать в PostgreSQL с использованием массивов и/или XML и SQL Server с использованием XML.

MULTISET создает коррелированный подзапрос и вставляет результирующий набор строк во внешний запрос. В следующем запросе выбираются все участники и каждый актер собирает свои фильмы во вложенной коллекции:

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

Как вы видели, существует больше типов JOIN, чем просто "скучные" INNER, OUTER и CROSS JOIN, которые обычно упоминаются. Подробнее в моей статье. И, пожалуйста, прекратите использовать диаграммы Венна, чтобы проиллюстрировать их.

В SQL-сервере существуют разные типы JOINS.

• CROSS JOIN
• INNER JOIN
• ВСТУПЛЕНИЕ ВМЕСТЕ

Внешние соединения снова разделены на 3 типа

• Влево или влево.
• Присоединение к правой или правой стороне
• Полная регистрация или полное подключение

ПРИСОЕДИНЯЙТЕСЬ/ВНУТРЕННЕЕ ПРИСОЕДИНЕНИЕ

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
INNER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

OR

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

LEFT JOIN или LEFT OUTER JOIN

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
LEFT OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

OR

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
LEFT JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

ПРАВОЕ СОЕДИНЕНИЕ ИЛИ ПРАВИЛЬНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
RIGHT OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

OR

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
RIGHT JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

ПОЛНАЯ ВСТУПЛЕНИЕ ИЛИ ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
FULL OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

OR

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
FULL JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

Ничего сказать словами кроме этого:

Отличные ответы и сообщения! Я собираюсь подтолкнуть своего питомца: ключевое слово USING.

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

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

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