Как пользоваться функцией SQL JOIN

Даём подробное описание оператора SQL JOIN с примерами — синтаксис функции для соединения таблиц и других операций. Остановимся подробнее на INNER JOIN, LEFT и RIGHT JOIN, FULL JOIN и SELF-соединении.

Язык SQL

SQL — Simple Query Language, что переводится как «простой язык запросов». Он предназначен для различных операций с реляционными базами данных (БД), хранящими чётко структурированное содержимое. Реляционные БД имеют логическую структуру и представляют собой строки и колонки, объединённые в таблицы. Две или несколько таблиц могут быть связаны одинаковыми столбцами, и когда нужно соединить между собой данные из разных БД, строки связываются на основе общих значений из одинаковых столбцов (полей). Для такого объединения или фильтрации данных и нужен оператор JOIN.

Оператор SQL JOIN

Оператор присоединения нужен в следующих случаях:

• фильтрация и поиск нужной информации или отсечение лишних данных в таблицах одной БД;
• объединение двух, трёх и более баз данных в одну (создание новой таблицы, содержащей данные из источников);
• выборочное выделение данных из двух или нескольких БД по определённому условию и внесение их в новую.

Как это работает

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

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

То есть можно объединить список музыкальных композиций концерта «Setlist»:

и список зарегистрированных участников «Competitors»:

Столбец с идентификатором участника (CompetitorID) присутствует в обоих списках и может быть использован как ключ при соединении. Чтобы связать данные участников и сет-лист концерта отправляют запрос:

SELECT Setlist.TrackID, Competitors.CompetitorName, Setlist.TrackName
FROM Setlist
JOIN Competitors
ON Setlist.TrackID=Competitors.TrackID

В приведённом примере соединение также можно производить по полю TrackName.

Будет выведена итоговая таблица, содержащая только совпадающие по TrackID строки:

Соединение трёх и более объектов

Чтобы соединить три и более объекта, к запросу добавляют дополнительные команды JOIN. Например, кроме двух вышеприведённых списков есть данные о конкурсных номинациях («Nominations») с полем TrackName — таким же, как в «Setlist» и «Сompetitors»:

Во всех трёх присутствуют одинаковые поля TrackName, что можно использовать при соединении. Чтобы связать данные, отправляют запрос:

SELECT Setlist.TrackID, Competitors.CompetitorName, Setlist.TrackName, Nominations.Nomination
FROM Setlist
JOIN Competitors
ON Setlist.TrackID=Competitors.TrackID
JOIN Nominations
ON Setlist.TrackName=Nominations.TrackName

Итог будет выглядеть так:

SQL INNER JOIN

Эта команда выделяет в новую БД только совпадающие, пересекающиеся по ключу записи, которые есть в обоих таблицах. INNER в команде можно опускать.

Применяемый синтаксис:

SELECT col_name
FROM table01
INNER JOIN table02
ON table01.col_name = table02.col_name

SQL OUTER JOIN

Оператор «внешнее соединение» при создании новой БД берёт все записи из одной и совпадающие по ключу записи из обеих таблиц. Это главное различие между командами INNER и OUTER. При OUTER-запросе важно указать направление соединения — ту базу данных, которая будет взята целиком, без фильтрации. Оператор имеет несколько разновидностей: LEFT, RIGHT и FULL OUTER JОIN.

SQL LEFT JOIN

В результативную таблицу LEFT-соединения будут включены все записи объекта, переданного первым, и оператор присоединит к ним совпадения из второго переданного объекта. Результат будет сформирован, даже если ни одно значение не будет совпадать по указанному ключу. Формирование происходит по следующему алгоритму: сначала производится INNER-соединение, и затем к полученному результату добавляются записи первого объекта, не вошедшие в таблицу ранее. При этом пустые ячейки заполняются псевдозначением null.

Синтаксис:

SELECT col_name
FROM table01
LEFT JOIN table02
ON table01.col_name = table02.col_name

SQL RIGHT JOIN

RIGHT OUTER JOIN выполняет действие, противоположное LEFT-соединению. Итоговая таблица будет содержать все данные из указанной второй (правой) таблицы и записи из левой, совпадающие по ключу. Результат также будет сформирован при отсутствии совпадений.

Синтаксис:

SELECT col_name
FROM table01
RIGHT JOIN table02
ON table01.col_name = table02.col_name;

SQL FULL OUTER JOIN

Формирует новую БД из всех строк источников, независимо от того, выполнено условие объединения или нет.

Общий синтаксис команды:

SELECT col_name
FROM table01
FULL JOIN table02
ON table01.col_name = table02.col_name;

При отсутствии нужного значения для данного столбца в ячейке будет установлено null.

SQL CROSS JOIN

При перекрестном соединении записи в итоговой сводной базе данных являются списком всех возможных комбинаций попарного соединения строк одной таблицы со строками другой (это действие называют также декартово произведение). При этом не требуется условие для объединения — не нужно задавать связующее поле (ON table01.col_name = table02.col_name).

Синтаксис SQL оператора:

SELECT col_name(s)
FROM table01
ROSS JOIN table02;

SQL SELF JOIN

SELF JOIN сравнивает данные внутри одной таблицы. Технически она при этом объединяется со своей же копией, однако в один JОIN-запрос запрещено два раза передавать один и тот же объект — запрос в таком случае возвращает ошибку. Поэтому таблица временно переименовывается (используется псевдоним). Прямо в команде ей присваиваются два псевдонима или алиаса (от лат. alias — иначе). В примере «alias1» и «alias2» — это присвоенные псевдонимы, которые будут использоваться далее в запросе.

Общий синтаксис:

SELECT col_name(s)
FROM table01 AS alias1
JOIN table01 AS alias2
ON alias1.col_name = alias2.col_name;

При SELF-соединении можно применять все варианты JOIN: LEFT JOIN, RIGHT JOIN, INNER JOIN, FULL  JOIN.

Подытожим:

• Функция SQL JOIN предназначена для соединения данных из разных БД.
• Если требуется выделить в отдельную базу строки, совпадающие по ключу, и отсечь лишнее, то используют оператор INNER JOIN или JOIN.
• Если итоговая таблица должна включать все строки одной таблицы и удовлетворяющие условию строки из обеих, то пользуются LEFT или RIGHT JOIN, исходя из направления соединения.
• Если нужно полное соединение, включающее все данные источников, независимо от наличия или отсутствия совпадений, то используют оператор FULL JOIN.
• Если нужен список всех возможных комбинаций строк одной таблицы со строками другой (декартово произведение), применяют оператор CROSS JОIN, то есть перекрёстное соединение.
• Для внутреннего объединения строк одной таблицы пользуются синтаксисом SELF JOIN.

Теперь вы знаете, как использовать функцию JOIN в работе с базами данных.

Читайте в блоге:

• Ошибка аnother MySQL daemon already running with the same unix socket
• Как включить удаленный доступ к MySQL базе данных