Особенности JOIN в SQL и примеры использования

Работать с одной таблицей — казалось бы, проще простого. Извлекаешь строки при помощи SELECT c условием и получаешь нужную информацию. Но что делать, когда таблицы две или три. На помощь приходит оператор JOIN в SQL. Он упрощает сбор и обработку данных и повышает эффективность запросов. Рассмотрим принцип работы JOIN и его основные типы в примерах, чтобы вы без труда могли применять эту команду для решения повседневных задач.

Немного о JOIN в SQL

Для специалистов, которые работают с базами данных (БД), JOIN — не просто оператор объединения таблиц SQL. Это незаменимый помощник, который позволяет эффективно выбирать данные из разных таблиц и объединять их в один результирующий набор.

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

Синтаксис оператора JOIN

Для работы с JOIN важно понимать, как выглядит его синтаксис:

SELECT    t1.column_name,    t2.column_name FROM    table_name1 as t1    [join_type] JOIN table_name2 as t2 ON join_condition;

В первой части команды: SELECT table_nameN.column_name, ... на месте многоточия можно указать любое количество столбцов формата table_nameN.column_name, которое вам необходимо задействовать.

Чтобы запрос был успешно выполнен, во второй части

FROM    table_name1 as t1    [join_type] JOIN table_name2 as t2 ON join_condition;

необходимо учесть следующие элементы:
 

• имя первой таблицы для объединения;

• тип используемого JOIN;

• имя второй таблицы для объединения;

• правило объединения — логическое выражение или ключ.

Когда оператор производит запрос, он сравнивает таблицы на наличие заданного ключа или выражения. JOIN сверяет значения в столбцах обеих таблиц и делает выборку из тех строк, которые совпадают.

Для чего нужен JOIN в SQL

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

2. Выявить связи между таблицами по ключам и использовать их для извлечения данных.

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

4. Упростить процесс поиска и анализа данных, распределенных по многочисленным таблицам.

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

JOIN — своего рода фильтр поиска, который сокращает количество запросов к БД и повышает производительность при обработке больших объемов информации. Такой же фильтр, как в каком-нибудь интернет-магазине. Когда, например, мы ищем одежду и задаем в параметрах поиска определенный размер или цвет. В выдаче результатов мы увидим товары, соответствующие выбранным критериям.

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

 

• 1:1 (один к одному) — подразумевает под собой связь между данными из двух таблиц, допустим, A и B, где строка из таблицы A будет иметь только одно совпадающее значение в B.

• 1:m (один к многим) — этот вид считают наиболее распространенным, он предполагает, что строка из таблицы A может иметь несколько совпадающих строк в B. При этом у строки из таблицы B будет только одно совпадающее значение в А.

• m:m (многие ко многим) — при таком виде связи строка из таблицы A будет иметь несколько совпадающих строк в таблице B. И наоборот.

В базах данных JOIN ориентируется на взаимосвязь между элементами таблиц. Приведем пример JOIN в SQL-запросах.

Для наглядности возьмем три таблицы: Student (данные студентов), StudentCourse (выбранные студентами курсы) и Course (описание курсов).

Таблица 1: Student

Таблица 2: StudentCourse

Таблица 3: Course

У этих таблиц есть общий ключ — столбец ROLL_NO и COURSE_ID. Чтобы узнать, на каком курсе учатся студенты, потребуется такой SQL-запрос:

SELECT    s.ROLL_NO,    s.NAME,    s.ADDRESS,    s.PHONE,    s.AGE,    sc.COURSE_ID,    c.NAME as COURSE_NAME, FROM    Student s    JOIN StudentCourse sc ON s.roll_no = sc.roll_no    JOIN Course c ON c.course_id = sc.course_id;

И вот какой результат мы получим в итоге:

Кому пригодится оператор JOIN

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

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

Однако JOIN принесет пользу и другим специалистам: сотрудникам техподдержки, контент-менеджерам, SEO-специалистам и разработчикам. С ним можно облегчить процесс обработки и анализа данных.

Виды JOIN в SQL

На первый взгляд работа с JOIN кажется простой, но у оператора есть большое количество разновидностей, каждый из которых выполняет свою функцию. И в них нужно тщательно разбираться. 

Как только вы определили цель запроса, важно подобрать соответствующий вариант оператора. Выделяют четыре основных типа JOIN: INNER, OUTER (LEFT, RIGHT и FULL), CROSS и SELF. Разберемся, как устроен каждый из них.

INNER JOIN

Рисунок 1. INNER JOIN

Этот тип называют «прямым соединением». Его используют чаще остальных. Если вы, к примеру, не укажете тип JOIN в своем запросе, то он будет использован автоматически.

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

Рассмотрим вариант синтаксиса INNER JOIN:

SELECT    a.id as address_id,    u.full_name,    u.enabled as user_enabled,    u.last_login,    u.id as user_id,    a.street,    a.city FROM    users as u    INNER JOIN addresses as a ON u.id = a.user_id

Попробуем объединить с использованием INNER JOIN таблицы Users и Addresses.

Таблица 1: Users

Таблица 2: Addresses

И вот что получим после выполнения команды INNER JOIN:

В случае с INNER JOIN стоит помнить, что не соответствующие строки не отобразятся в результате запроса.

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

OUTER JOIN

OUTER JOIN в SQL — его еще называют внешним соединением. Оно возвращает строки из нескольких таблиц в различных вариациях в зависимости от типа. В OUTER JOIN возможны три варианта объединений: LEFT JOIN, RIGHT JOIN и FULL JOIN.

Рисунок 2. LEFT JOIN

LEFT JOIN возвращает все строки из левой таблицы, указанной в условии — ON, и только те строки из правой таблицы, для которых выполняется условие соединения. При отсутствии совпадений запрос вернет значения NULL для этих столбцов. 

Рассмотрим принцип работы на примере выборки из таблиц Users и Addresses, что приводили выше.

Таблица 1: Users

Таблица 2: Addresses

Запрос в SQL с использованием LEFT JOIN выглядит следующим образом:

SELECT    u.id as user_id,    u.full_name,    u.enabled as user_enabled,    u.last_login,    a.id as address_id,    a.street,    a.city FROM    users as u    LEFT JOIN addresses as a ON user.id = addresses.user_id;

Таким образом, главная таблица будет располагаться слева. И вот результат:

Так как адреса пользователя Ольги Семеновой нет в таблице Addresses, на выходе запрос вернул значения NULL.

В запросе можно прописывать как LEFT JOIN, так и LEFT OUTER JOIN — результат от этого не изменится. Для удобства использования команды OUTER часть опускают.

Рисунок 3. RIGHT JOIN

RIGHT JOIN работает по аналогии с LEFT. Только возвращает все строки из правой таблицы, которая указана в условии — ON, и те строки из левой таблицы, для которых выполнено условие соединения. Если в левой таблице не окажется совпадающих значений, то в результате они отобразятся как NULL.

Рассмотрим пример RIGHT JOIN на тех же таблицах.

Таблица 1: Users

Таблица 2: Addresses

Вот как будет выглядеть запрос в SQL с RIGHT JOIN:

SELECT    u.id as user_id,    u.full_name,    u.enabled as user_enabled,    u.last_login,    a.id as address_id,    a.street,    a.city FROM    addresses a    RIGHT JOIN users u ON u.id = a.user_id;

В данном случае главная таблица в результате будет справа.

Рисунок 4. FULL JOIN

FULL JOIN — комбинация LEFT и RIGHT JOIN. Этот тип возвращает все строки из обеих таблиц, независимо от того, есть ли соответствующие значения. При отсутствии совпадений в обеих таблицах, оператор вернет значения NULL для этих столбцов. Другими словами, это объединение всех столбцов двух таблиц. Если условие объединения выполнено, строки объединяются, как и в предыдущих примерах, которые мы разбирали.

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

CROSS JOIN

Рисунок 5. CROSS JOIN (Источник: javatpoint.com)

CROSS — возвращает все возможные комбинации строк из таблиц, которые объединяются. Это единственный тип JOIN, который не нуждается в условии соединения (ON). 
CROSS JOIN не так часто используют. Этот оператор будет полезен в нескольких случаях:
 

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

2. Когда нужно произвести вычисления или отфильтровать данные на основе всех возможных комбинаций значений.

Рассмотрим принцип работы CROSS JOIN на примере таблиц, которые мы использовали ранее — Users и Addresses. Синтаксис CROSS JOIN будет таким:

SELECT    u.id as user_id,    u.full_name,    u.enabled as user_enabled,    u.last_login,    a.id as address_id,    a.street,    a.city FROM    users u,    addresses a;

Возможен и второй вариант синтаксиса CROSS JOIN:

SELECT    u.id as user_id,    u.full_name,    u.enabled as user_enabled,    u.last_login,    a.id as address_id,    a.street,    a.city FROM    users u    CROSS JOIN addresses a;

А в результате SQL-запроса получится подобная таблица:

SELF JOIN

Рисунок 6: SELF JOIN ( Источник: stratascratch.com)

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

Подведем итоги

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

В основе JOIN лежит концепция связей между таблицами. В зависимости от того, какую задачу необходимо выполнить, можно использовать различные виды JOIN-операторов: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN или SELF JOIN. Каждый из них обладает определенным способом соединения, о которых рассказали подробно выше. Если после прочтения материала у вас остались вопросы о принципах работы JOIN в SQL, можете подтянуть текущие знания или освоить SQL с нуля на курсе от ProductStar — SQL для анализа данных. С его помощью вы научитесь работать с базами данных, повысите уровень своих компетенций и станете конкурентоспособным специалистом на рынке.

Статья написана при участии эксперта — Виктора Гладких, тимлида backend-разработки РБК Pro