Объяснение работы SQL JOIN на примере диаграмм Венна Left, Right, Full, Cross, multiple joins

Сразу хочу сказать, что здесь никакой воды про sql join, и только нужная информация. Для того чтобы лучше понимать что такое sql join, multiple joins, join mysql , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.

SQL JOIN - это операция объединения данных из двух или более таблиц в базе данных, основываясь на общих полях (колонках) между ними.

Проще говоря, JOIN позволяет соединить данные из разных таблиц, чтобы получить более полную информацию о них. Для этого используется условие сравнения, где указываются поля, которые совпадают в двух таблицах.

Представьте, что у вас есть две таблицы с информацией, которую вы хотите объединить. Например, одна таблица содержит список пользователей, а другая таблица - список заказов, которые они сделали. Для того чтобы узнать, какие заказы были сделаны какими пользователями, вам нужно объединить эти две таблицы.

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

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

Соединение таблиц SQL с обяснениями на схемах, диаграммах и примерах

Использование синтаксиса SQL JOINS при работе с Базами данных достаточно популярно, без них не обходится любой серьезный SQL запрос. Использование диаграмм Венна для объяснения их работы, вполне естественно и наглядно. Однако, комментируя ее статью, я обнаружил, что ее диаграммы Венна не вполне соответствовали синтаксису SQL join.Оператор языка SQL JOIN предназначен для соединения двух или более таблиц базы данных по совпадающему условию. Этот оператор существует только в реляционных базах данных. Именно благодаря JOIN реляционные базы данных обладают такой мощной функциональностью, которая позволяет вести не только хранение данных, но и их, хотя бы простейший, анализ с помощью запросов. Разберем основные нюансы написания SQL-запросов с оператором JOIN, которые являются общими для всех СУБД (систем управления базами данных).

существует четыре основных типа объединений. В первую очередь кратко опишем их с помощью иллюстраций диаграмм Венна :

• Внутреннее соединение Inner join возвращает строки, соответствующие в обеих таблицах

  

• Левое соединение Left join возвращает все строки из левой таблицы

  

• Right join Правое соединение возвращает все строки из правой таблицы

  

• Full join Полное соединение возвращает целые строки из обеих таблиц

  

Допустим, у нас есть две таблицы (A и B), и некоторые из их строк совпадают (относительно данного условия JOIN, каким бы оно ни было в конкретном случае):

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

Я решил это разъяснить на примерах ниже. Предположим, что у нас есть две следующие таблицы. СлеваТаблица A, и таблица B справа. Поместим в каждой из них по 4 записи (строки).

id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja

Давайте соединим эти таблицы с помощью SQL join по столбцу "name" несколькими способами и посмотрим как это будет выглядеть на диаграммах Венна.

 Left outer join (левое внешнее соединение) производит выбор всех строк таблицы 
А с доступными строками таблицы В. Если строки таблицы В не найдены, 
то подставляется пустой результат (null).

SELECT * FROM TableA
CROSS JOIN TableB

SELECT class_num, class_name
    FROM class
    WHERE class_num NOT IN
        (SELECT class_num FROM roster);

SELECT class_num, class_name
    FROM class
    WHERE class_num IN
        (SELECT class_num FROM roster);

SELECT class_num, class_name
    FROM class
    WHERE EXISTS
        (SELECT * FROM roster WHERE class.class_num = roster.class_num);

Объяснение SQL объединений JOIN: LEFT/RIGHT/INNER/OUTER

Разберем пример. Имеем две таблицы: пользователи и отделы.

U) users D) departments
id name d_id id name
-- ---- ---- -- ----
1 Владимир 1 1 Сейлз
2 Антон 2 2 Поддержка
3 Александр 6 3 Финансы
4 Борис 2 4 Логистика
5 Юрий 4

SELECT u.id, u.name, d.name AS d_name
FROM users u
INNER JOIN departments d ON u.d_id = d.id

Запрос вернет объединенные данные, которые пересекаются по условию, указанному в INNER JOIN ON <..>.
В нашем случае условие <таблица_пользователей>.<идентификатор_отдела> должен совпадать с <таблица_отделов>.<идентификатор>

В результате отсутствуют:

- пользователь Александр (отдел 6 - не существует)
- отдел Финансы (нет пользователей)

id name d_name
-- -------- ---------
1 Владимир Сейлз
2 Антон Поддержка
4 Борис Поддержка
3 Юрий Логистика

рис. Inner join

Внутреннее объединение INNER JOIN (синоним JOIN, ключевое слово INNER можно опустить).

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

внешнее объединение - OUTER JOIN.

Такое объединение вернет данные из обеих таблиц совпадающими по одному из условий.



рис. Left join

Существует два типа внешнего объединения OUTER JOIN - LEFT OUTER JOIN и RIGHT OUTER JOIN.

Работают они одинаково, разница заключается в том что LEFT - указывает что "внешней" таблицей будет находящаяся слева (в нашем примере это таблица users).
Ключевое слово OUTER можно опустить. Запись LEFT JOIN идентична LEFT OUTER JOIN.

SELECT u.id, u.name, d.name AS d_name
FROM users u
LEFT OUTER JOIN departments d ON u.d_id = d.id

Получаем полный список пользователей и сопоставленные департаменты.

id name d_name
-- -------- ---------
1 Владимир Сейлз
2 Антон Поддержка
3 Александр NULL
4 Борис Поддержка
5 Юрий Логистика

Добавив условие

WHERE d.id IS NULL

в выборке останется только 3#Александр, так как у него не назначен департамент.



рис. Left outer join с фильтрацией по полю


RIGHT OUTER JOIN вернет полный список департаментов (правая таблица) и сопоставленных пользователей.

SELECT u.id, u.name, d.name AS d_name
FROM users u
RIGHT OUTER JOIN departments d ON u.d_id = d.id

id name d_name
-- -------- ---------
1 Владимир Сейлз
2 Антон Поддержка
4 Борис Поддержка
NULL NULL Финансы
5 Юрий Логистика

Дополнительно можно отфильтровать данные, проверяя их на NULL.

SELECT d.id, d.name
FROM users u
RIGHT OUTER JOIN departments d ON u.d_id = d.id
WHERE u.id IS NULL

В нашем примере указав WHERE u.id IS null, мы выберем департаменты, в которых не числятся пользователи. (3#Финансы)

JOIN MYSQL

Все примеры вы можете протестировать здесь:

SQLFiddle


Self joins

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

Имеем: набор фильтров для информации, значения которых сохраняются в табличке filts_data.
Необходимо: фильтровать продукты по дате, артикулу и имеющимся фильтрам

CREATE TABLE filts_data
(
id serial NOT NULL,
fid integer NOT NULL, -- product_item.id
value integer NOT NULL, -- значение фильтра filts_items.id
pid integer NOT NULL -- фильтр filts.id
)

Есть таблица условных товаров product_item

CREATE TABLE product_item
(
id serial NOT NULL,
section_id integer,
date timestamp,
art text,
title text
)

Пример: выбрать записи, добавленные после 17/01/2009 и с установленными фильтрами 3=14 и 4=15 и 6=19.
Логика подскажет нам такой запрос (нерабочий):

SELECT p1.title FROM products_item p1
INNER JOIN filts_data p2 ON p1.id = p2.fid
WHERE p1.date > '17.01.2009'
AND (p2.pid = 3 AND p2.value = 14)
AND (p2.pid = 4 AND p2.value = 15)
AND (p2.pid = 6 AND p2.value = 19)

Этот запрос не найдет элементов в таблице.
Перепишем запрос, используя join на себя:

SELECT p1.* FROM product_item p1
INNER JOIN filts_data p2 ON p1.id = p2.fid
INNER JOIN filts_data p3 ON p1.id = p3.fid
INNER JOIN filts_data p4 ON p1.id = p4.fid
WHERE p1.date > '17.01.2009'
AND (p2.pid = 3 AND p2.value = 14)
AND (p3.pid = 4 AND p3.value = 15)
AND (p4.pid = 6 AND p4.value = 19)

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

подитожим

JOIN: возвращаются строки, когда есть хотя бы одно совпадение в обеих таблицах.
LEFT JOIN: возвращаются все строки из левой таблицы, даже если нет совпадений в таблице справа
RIGHT JOIN: возвращаются все строки из правой таблицы, даже если нет совпадений в таблице слева
FULL JOIN: возвращаются строки, когда есть совпадение в одной из таблиц
Еще есть CROSS JOIN - декартово произведение двух таблиц, но он используется крайне редко.

Что такое множественные соединения(multiple joins/ multy joins) SQL?

Множественные объединения можно описать следующим образом; множественное соединение - это запрос, содержащий одинаковые или разные типы соединений, которые используются более одного раза . Таким образом, мы получаем возможность комбинировать несколько таблиц данных для решения проблем с реляционными базами данных.

Пример сценария

Компания Интеллект запустила новую кампанию к Новому году и сделала различные предложения своим онлайн-клиентам. В результате своей кампании им удалось преобразовать некоторые предложения в продажи. В следующих примерах мы раскроем детали данных новогодней кампании компании Интеллект .

Компания хранит эти детали данных кампании в следующих таблицах. Теперь мы создадим эти таблицы с помощью следующего запроса и заполним их некоторыми фиктивными данными:

Бизнес-задача : какие клиенты были заинтересованы в этой новогодней акции?

Чтобы ответить на этот вопрос, нам нужно найти совпадающие строки для всех таблиц, потому что некоторые клиенты не получили предложение по электронной почте, а некоторые предложения не могли быть преобразованы в продажу. Следующая диаграмма Венна поможет нам определить нужные нам совпадающие строки. Короче говоря, результатом этого запроса должны быть пересекающиеся строки всех таблиц в запросе. Область серого цвета определяет эти строки на диаграмме Венна:

Подход с несколькими объединениями SQL поможет нам объединить онлайн-клиентов , заказы и таблицы продаж . Как показано на диаграмме Венна, нам нужно сопоставить строки всех таблиц. По этой причине мы объединим все таблицы с помощью предложения внутреннего соединения . Следующий запрос вернет желаемый набор результатов и ответит на вопрос:

Сначала разберем запрос. Предложение внутреннего соединения , которое находится между таблицами onlinecustomers и orders, извлекает совпадающие строки между этими двумя таблицами. Второе предложение внутреннего соединения, которое объединяет таблицу продаж, извлекает совпадающие строки из предыдущего набора результатов. Следующая цветная иллюстрация таблиц поможет нам понять соответствие данных объединенных таблиц в запросе. Строки желтого цвета указывают совпадающие данные между онлайн-клиентами и заказами. С другой стороны, в таблицах продаж существуют только строки синего цвета, поэтому результатом запроса будут строки синего цвета:

Результат запроса будет таким:

Использование различных типов соединений в множественных соединениях SQL

Бизнес-проблема : какие предложения нельзя было конвертировать в продажи?

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

На первом этапе мы должны объединить таблицы onlinecustomers и orders с помощью предложения внутреннего соединения , поскольку внутреннее соединение возвращает все совпадающие строки между таблицами onlinecustomers и orders . На втором этапе мы объединим таблицу заказов с таблицей продаж через левое соединение, а затем отфильтруем нулевые значения, потому что нам нужно удалить строки, которые хранятся в таблице продаж :

Результат запроса будет таким:

Контрольный вопрос

Вопрос: Пожалуйста, сгенерируйте правильный запрос в соответствии с приведенной ниже диаграммой Венна.

Ответ: Как мы узнали, полное соединение позволяет нам возвращать все строки из объединенных таблиц. Ответ на запрос будет таким:

Особенности в применении диаграммы Венна для описания однородных и не однородных соединений

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

В конце концов, реляционная алгебра и SQL являются теориями и языками, ориентированными на множество, поэтому имеет смысл только иллюстрировать операции над множествами, такие как JOIN, с использованием диаграмм Венна. Правильно?

Google, кажется, говорит так:

Каждый использует диаграммы Венна для объяснения JOIN. Но это…

ПОНИМАНИЕ НЕПРАВИЛЬНОЕ!

Диаграммы Венна идеально подходят для иллюстрации ... фактических операций над множествами!

SQL знает три из них:

• UNION
• INTERSECT
• EXCEPT

И их можно объяснить так:

UNION Операция является наиболее известным среди этих множественных операций

. Это часто также называют «конкатенацией» двух наборов кортежей, где результатом является конкатенация набора B с набором A.

В следующем примере мы увидим, что нас могут интересовать разные люди из нашей базы данных, учитывая их имена и фамилии, независимо от того, являются они customer(заказчик) или staff(персонал):

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

Эта EXCEPT операция полезна, когда вы хотите сохранить только те кортежи, которые присутствуют в одном наборе, но не в другом:

Большинство из вас используют UNION . INTERSECT и EXCEPT более экзотичны, но время от времени пригодятся .

Суть здесь в том, что эти операции над множествами работают с наборами элементов (кортежей), которые имеют одинаковый тип . Как и в приведенных выше примерах, все элементы - это люди с именами и фамилиями. Это также почему INTERSECT и EXCEPT более экзотично, потому что они обычно не очень полезны. JOIN гораздо полезнее. Например, вы хотите объединить набор актеров с их соответствующим набором фильмов.

A JOIN- это действительно декартово произведение (также перекрестное произведение) с фильтром. Вот хорошая иллюстрация декартового произведения:

Итак, как лучше проиллюстрировать операции JOIN?

JOIN Диаграммы! Давайте CROSS JOIN сначала посмотрим , потому что все другие JOIN типы могут быть получены из CROSS JOIN:

Помните, что в перекрестном соединении (в SQL, также исторически написанном со списком таблиц, разделенных запятыми) просто берется каждый элемент с левой стороны и объединяется с каждым элементом с правой стороны. Если у вас CROSS JOIN таблица из 3 строк с таблицей из 4 строк, вы получите 3 × 4 = 12 строк результатов. Видите, я использую символ "х", чтобы написать умножение. Т.е. «крестик».

INNER JOIN

Все другие объединения по-прежнему основаны на перекрестных соединениях, но с дополнительными фильтрами и, возможно, объединениями.

Вот объяснение каждого отдельного JOIN типа.

В простом тексте an INNER JOIN- это CROSS JOIN-та, в которой сохраняются только те комбинации, которые удовлетворяют данному предикату.

Например:

OUTER JOIN

OUTER JOIN Типы помогают, когда мы хотим сохранить эти строки либо со LEFT стороны, либо со стороны RIGHT обоих ( FULL), для которых не было подходящей строки, в которой предикат дал значение true.

А LEFT OUTER JOINв реляционной алгебре определяется так:

Или более подробно в SQL:

Это приведет ко всем авторам и их книгам, но если у автора нет какой-либо книги, мы все равно хотим, чтобы автор использовал NULL в качестве единственной стоимости книги. Итак, это то же самое, что написать:

Но никто не хочет писать так много SQL, поэтому OUTER JOIN был реализован.

Вывод: Скажи НЕТ диаграммам Венна или нужно быть очень внимательным и правильно их понимать

JOIN Относительно легко понять интуитивно.

И их относительно легко объяснить с помощью диаграмм Венна.

Но всякий раз, когда вы делаете это, помните, что вы делаете неправильную аналогию

. A JOIN не является строго заданной операцией, которую можно описать с помощью диаграмм Венна.

A JOIN всегда является перекрестным продуктом с предикатом и, возможно, UNION добавляет дополнительные строки к OUTER JOIN результату.

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

Они более точные и визуально более полезные.

Вау!! 😲 Ты еще не читал? Это зря!

• производительность sql exists и in и join ,
• реляционная алгебра ,
• реляционные соединения , операции над отношениями , реляционная алгебра ,
• реляционная алгебра , объединение таблиц , пересечение таблиц , разность таблиц ,

А как ты думаешь, при улучшении sql join, будет лучше нам? Надеюсь, что теперь ты понял что такое sql join, multiple joins, join mysql и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL