Соединение таблиц - INNER, OUTER, CROSS, SELF JOIN

INNER JOIN - Внутреннее Соединение Таблиц

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

Представь, что у тебя есть две таблицы: таблица студентов и таблица курсы. Каждая таблица содержит информацию о студентах и их успехах на курсах. Таблица студентов содержит столбцы, такие как студент_ид (идентификатор студента) и имя. Таблица курсы содержит столбцы, такие как курс_ид (идентификатор курса), название курса и успех_на_курсе (успех студента на данном курсе).
Теперь представим, что ты хочешь получить информацию о студентах и их успехе на курсах. Для этого нужно объединить две таблицы по общим столбцам, например, студент_ид. Внутреннее соединение по студент_ид будет создать пары студентов и их успехи на курсах на основе совпадений в столбце студент_ид.

Оператор INNER JOIN имеет следующий синтаксис:

SELECT [columns]-- это список столбцов, которые вы хотите 
                --получить в результате запроса.
FROM [table1] -- это имя первой таблицы, с которой вы соединяетесь.
INNER JOIN [table2] ON [table1].[column] = [table2].[column];
--[table2] - это имя второй таблицы, с которой вы соединяетесь.
--[column] - это имя столбца, по которому вы соединяетесь. 
--Это могут быть столбцы, имеющие общие названия или ключи, 
--которые связывают две таблицы.

Чтобы получить возможность выбирать данные из двух таблиц, можно сделать такой запрос:

SELECT a.* -- a - алиас таблицы album
    FROM album as a 
INNER JOIN band as b -- b - алиас таблицы band 
        ON a.band_id = b.band_id -- условия соединения
  -- колонка band_id из album должна соответствовать такой же из второй

и тд

    SELECT a.album_id, a.name, a.band_id, b.name as band_name
      FROM album as a
INNER JOIN band as b
        ON a.band_id = b.band_id

и тд

Алиасы таблиц

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

SELECT *
FROM album as a
INNER JOIN band as b 
ON a.band_id = b.band_id

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

Алиасы можно не указывать, тогда чтобы указать из какой таблицы берется та или иная колонка нужно указывать название самой таблицы.

SELECT *
FROM album
INNER JOIN band
ON album.band_id = band.band_id

ПРИ УКАЗАНИИ КОЛОНОК В SELECT НЕОБХОДИМО ВСЕГДА УКАЗЫВАТЬ ОТКУДА БЕРУТЬСЯ ЭТИ КОЛОНКИ

--ВОТ ТАК НЕ НАДО:--
SELECT comment
FROM album as a
INNER JOIN band as b 
ON a.band_id = b.band_id

--НАДО ВОТ ТАК:--
SELECT b.comment
FROM album as a
INNER JOIN band as b 
ON a.band_id = b.band_id

ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ INNER JOIN

Далее на примерах посмотрим, как выполняются сопоставление строк и как формируется результат запроса:

До этого мы делали соединение по принципу М:1 (Многие к одному).

Теперь соединим таблицы в другом порядке: 1:М - теперь выберем музыкальные группы и для каждой из них найдем список музыкальных альбомов.

SELECT b.band_id, b.name as band_name,
       a.album_id, a.name as album_name
FROM band as b
INNER JOIN album as a
ON a.band_id = b.band_id
ORDER BY band_name

и тд

Сравним результаты запроса М:1 и 1:М:

Выходит, что результат одинаковый. Получается, что для INNER JOIN неважно в каком порядке идут таблицы.

Соединение Нескольких Таблиц

В этой лекции будет соединение трех и более таблиц. Если раньше соединялись 2 таблицы, то теперь будем соединять 3 и больше.

Будем соединять между собой таблицы band и person. Но так как они имеют отношение М:М, будем соединять их через таблицу band_person. Чтобы отношение М:М изменить на 1:М - М:1.

ШАГ 1

Объединяем таблицу band и band_person

SELECT *
FROM band as b 
INNER JOIN band_person as b_p
ON b.band_id = b_p.band_id

и тд

ШАГ 2

Объединяем получившуюся таблицу с таблицей person. Просто добавляем еще один INNER JOIN

SELECT *
FROM band as b 
INNER JOIN band_person as b_p
ON b.band_id = b_p.band_id
-- то, что ниже - добавили
INNER JOIN person as p
ON p.person_id = b_p.person_id

и тд

И дальше с этой таблицей можно делать какие-либо операции

SELECT b.band_id, b.name as band_name, b.year,
       p.person_id, p.name as artist_name, p.year as birthyear
FROM band as b 
INNER JOIN band_person as b_p
ON b.band_id = b_p.band_id
— то, что ниже - добавили
INNER JOIN person as p
ON p.person_id = b_p.person_id
WHERE b.name = 'Король и Шут'
ORDER BY artist_name

Либо можно сделать такой запрос и узнать в каких коллективах участвовал тот или иной исполнитель

SELECT *
FROM band as b 
INNER JOIN band_person as b_p
ON b.band_id = b_p.band_id
— то, что ниже - добавили
INNER JOIN person as p
ON p.person_id = b_p.person_id
WHERE p.name = 'Андрей «Князь» Князев'

Так как некоторые колонки носят одинаковые имена (например, name в band - это название группы, а name в person - это имя музыканта) нужно давать колонки алиасы:

SELECT b.band_id, b.name as band_name, p.person_id, p.name as person_name
FROM band as b 
INNER JOIN band_person as b_p
ON b.band_id = b_p.band_id
— то, что ниже - добавили
INNER JOIN person as p
ON p.person_id = b_p.person_id
WHERE p.name = 'Андрей «Князь» Князев'

Дубликаты Строк, Нехватка Строк

Дубликаты

Бывает такое, что первичный ключ не объявлен и появились дублирующие строки:

Вот это называется дублированием строк.

Нехватка строк

Кроме дублирования строк еще может быть нехватка строк. Например, в таблице album встретилось значение, которого нет в таблице band.

Для первых двух альбомов строки нашлись (Metallica) и они попали в итоговую таблицу. А для трех остальных строк не нашлось альбомов - поэтому они не попали и на выводе только 2 строки.

Решение проблемы дубликатов и нехватки строк

Если ключи не объявлены, то как можно понять, есть ли в таблице строки, которые нарушают связь 1:М между таблицами? То есть проверить, соблюдаются ли требования первичного и внешнего ключа.

SELECT COUNT(*), COUNT(DISTINCT band_id) FROM band;
-- 82928, 82928

Этот запрос проверяет условие первичного ключа - считает количество строк и количество уникальных значений так же проверяет отсутствие значений NULL если бы где то был NULL, то COUNT(DICTINCT) вернул бы меньшее значение

SELECT COUNT(*)
FROM album
WHERE band_id NOT IN (
    SELECT band_id FROM band WHERE band_id IS NOT NULL)
OR band_id IS NULL
-- 0

Проверяет условия внешнего ключа для album. Считает количество строк в album для которых значение band_id не нашлось в таблице band.

LEFT OUTER JOIN - Левое Внешнее Соединение

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

LEFT OUTER JOIN (также известный как LEFT JOIN): возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если в правой таблице нет соответствующих строк, NULL значения будут возвращены для столбцов из правой таблицы.

SELECT a.album_id, a.name,
       a.band_id, b.name as band_name
FROM album as a
LEFT OUTER JOIN band as b 
ON a.band_id = b.band_id

В секции FROM пишем album, а дальше LEFT OUTER JOIN band. LEFT OUTER JOIN указывает, что таблица album является основной и строки из нее не должны потеряться при соединении таблиц (FROM album LEFT OUTER JOIN band - то есть album стоит слева).

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

Мы хотим выбрать музыкальные группы, и для каждой группы список альбомов. Для этого берем таблицу band и соединяем ее с таблицей album. Может быть такое, что у некоторых групп нет ни одного альбома. Если нужно, чтобы в результат запроса попали и эти группы тоже, то используется OUTER JOIN.

SELECT b.band_id, b.name as band_name,
       a.album_id, a.name as album_name
FROM band as b
LEFT OUTER JOIN album as a
ON a.band_id = b.band_id
ORDER BY album_name DESC

Различные варианты синтаксиса

Вариант соединения, который мы уже знаем

SELECT *
FROM album as a
INNER JOIN band as b
ON a.band_id = b.band_id
WHERE b.name = 'Metallica'

И еще один вариант без использования JOIN

SELECT *
FROM album as a,
     band as b 
WHERE a.band_id = b.band_id
AND b.name = 'Metallica'

Так же запрос

SELECT * 
FROM album as a 
INNER JOIN band as b 
ON a.band_id = b.band_id

то же самое, что

SELECT * 
FROM album as a 
JOIN band as b 
ON a.band_id = b.band_id

То есть INNER JOIN можно заменить на JOIN.

Так же LEFT OUTER JOIN можно заменить на LEFT JOIN.

RIGHT OUTER JOIN- Правое Внешнее Соединение

RIGHT OUTER JOIN (также известный как RIGHT JOIN): возвращает все строки из правой таблицы и соответствующие строки из левой таблицы. Если в левой таблице нет соответствующих строк, NULL значения будут возвращены для столбцов из левой таблицы.

В чем различие между LEFT OUTER JOIN и RIGHT OUTER JOIN:

SELECT *
FROM album as a
LEFT OUTER JOIN band as b 
ON a.band_id = b.band_id
-- тут основная таблица находится слева от JOIN 
-- то есть это таблица album

SELECT *
FROM album as a
RIGHT OUTER JOIN band as b 
ON a.band_id = b.band_id
-- а тут основная таблица - эта та, которая находится справа от JOIN 
-- то есть таблица band

На самом деле можно всегда использовать только LEFT OUTER JOIN.

Можно изменить порядок, поменяв таблицы местами.

То есть:

SELECT a.album_id, a.name,
       b.band_id, b.name as band_name
FROM band as b 
RIGHT OUTER JOIN album as a
ON a.band_id = b.band_id

Тоже самое, что:

SELECT a.album_id, a.name,
       b.band_id, b.name as band_name
FROM album as a
LEFT OUTER JOIN band as b 
ON a.band_id = b.band_id

FULL OUTER JOIN - Полное внешнее соединение

FULL OUTER JOIN (также известный как FULL JOIN): возвращает все строки из обеих таблиц, включая строки, которые не имеют соответствующих строк в другой таблице. Если одна из таблиц не содержит соответствующих строк, NULL значения будут возвращены для столбцов из таблицы, которая не содержит соответствующих строк.

SELECT a.album_id, a.name,
       a.band_id as b_id1,
	   b.band_id as b_id2,
	   b.name as band_name
FROM album as a
FULL OUTER JOIN band as b
ON a.band_id = b.band_id

SELF JOIN - Соединение таблицы самой с собой

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

И, предположим, нам нужно, чтобы вместо parant_id в таблице music_instrument были сами названия родительских музыкальных инструментов.

Это можно сделать с помощью этой команды:

SELECT a.id, a.name, a.parent_id, b.name as parent_name
FROM music_instrument as a
LEFT JOIN music_instrument as b 
ON b.id = a.parent_id

А теперь шагнем еще дальше - для каждого музыкального инструмента найдем не только родительские инструменты (parent_id и parent_name), но и названия инструмента на один уровень выше (gp_id и gp_name). Это будет движение вверх по иерархии.

SELECT a.*, b.name as parent_name, 
       c.id as gp_id, c.name as gp_name
FROM music_instrument as a
LEFT JOIN music_instrument as b ON b.id = a.parent_id
LEFT JOIN music_instrument as c ON c.id = b.parent_id

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

Из левой таблицы получить правую:

SELECT a.id, a.name, b.id as child_id,
       b.name as chil_name
FROM music_instrument as a
LEFT JOIN music_instrument as b 
ON b.parent_id = a.id
WHERE a.parent_id IS NULL
ORDER BY a.id

Можно сделать второй шаг вниз по иерархии...

SELECT a.id, a.name, b.id as child_id,
       b.name as chil_name, c.id as gc_id,
	   c.name as gc_name
FROM music_instrument as a
LEFT JOIN music_instrument as b ON b.parent_id = a.id
LEFT JOIN music_instrument as c ON c.parent_id = b.id
WHERE a.parent_id IS NULL

CROSS JOIN - Декартово Произведение Таблиц

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

Когда соединяем 2 таблицы, но не указываем условие соединения таблиц.

SELECT a.album_id, a.name, a.band_id as b_id1,
       b.band_id as b_id2, b.name as band_name
FROM album as a
CROSS JOIN band as b