Что такое join в SQL и как с ним работать

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

В статье мы разберем, как работает JOIN, зачем он нужен и кому пригодится.

Что такое оператор JOIN в SQL

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

Например, есть две таблицы.

Заказы — Orders:

Клиенты — Customers:

Обе таблицы связаны по столбцу CustomerID.

Чтобы объединить данные, напишите запрос:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

Присоедините таблицу «Заказы» к таблице «Клиенты», где CustomerID совпадает. В итоге получите номер заказа, название клиента и дату.

CustomerID = 34 не попал в список, потому что такого клиента нет в таблице Customers.

Можете соединить несколько таблиц.

Например, добавьте данные о продавцах.

Продавцы — Managers:

Таблица связывает Orders через OrderID.

Пропишите в запросе:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate, Managers.ManagerName
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
JOIN Managers ON Orders.OrderID = Managers.OrderID;

Результат:

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

Освойте базу SQL на курсе «Аналитик данных». Научитесь работать с таблицами, писать запросы, объединять данные из разных источников и использовать полезные инструменты для анализа. Курсы построены на практике — сразу начнете работать над реальными проектами. Преподаватели, действующие специалисты, которые работают в крупных компаниях: «Хоум Банке», «Т-Банке».

Чем отличается JOIN от UNION

JOIN и UNION — это две разные команды в SQL. Обе работают с несколькими данными, но делают это по-разному.

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

Пример

Клиенты из Москвы:

Клиенты из Петербурга:

Чтобы получить один общий список всех клиентов, используйте UNION:

SELECT CustomerName, City FROM MoscowClients
UNION
SELECT CustomerName, City FROM SpbClients;

В итоге получите:

UNION просто складывает строки друг под другом.

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

Чтобы использовать JOIN, важно понимать, как правильно написать запрос:

SELECT
t1.column_name,
t2.column_name
FROM
table_name1 AS t1
[JOIN_TYPE] JOIN table_name2 AS t2 ON join_condition;

1. SELECT t1.column_name, t2.column_name — укажите, какие именно столбцы хотите получить в результате. Можно выбрать столбцы из обеих таблиц, через запятую, в формате: имя_таблицы.имя_столбца. Например, SELECT Orders.OrderID, Customers.CustomerName.
2. FROM table_name1 AS t1 — отметьте первую таблицу, которую будете использовать. AS t1 — это короткое имя (псевдоним), чтобы потом не писать длинное название. Например, FROM Orders AS o
3. [JOIN_TYPE] JOIN table_name2 AS t2 — выберите тип объединения: INNER, LEFT, RIGHT или другой. И отметьте вторую таблицу, с которой будете соединять. Например, INNER JOIN Customers AS c
4. ON join_condition — это условие, которое соединяет таблицы. Обычно сравнивают столбцы, которые есть в обеих таблицах. Например, ON o.CustomerID = c.CustomerID

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

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

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

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

Оператор JOIN пригодится всем, кто работает с базами данных.

• Аналитикам данных. Специалисты часто собирают отчеты, отслеживают, сколько заказов сделал каждый клиент или какие товары покупают чаще всего. JOIN помогает собрать нужную информацию из разных таблиц.
• Разработчикам. Программисты создают сайты, приложения, системы управления. Им нужно получать и отображать связанные данные. Например, список заказов с именами клиентов и товарами.
• Специалистам по BI и отчетности. Сотрудники составляют отчеты для руководства, анализируют продажи, прибыль, работу сотрудников.
• Менеджерам и бизнес-пользователям с доступом к SQL. Специалисты смотрят отчеты, анализируют эффективность, отслеживают заказы, продажи, работу сотрудников.
  Без JOIN вы получаете только кусочки данных, а с оператором видите картину целиком.

Внутреннее соединение INNER JOIN

Если использовать INNER JOIN, в результат попадут только те строки, которые есть в обеих таблицах и где есть условие объединения.

Общий синтаксис запроса INNER JOIN выглядит так:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Источник: sky.pro

Слово INNER можно не писать — SQL всё равно поймет, что это обычный JOIN. Тогда запрос будет короче.

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

После учебы на курсе «Аналитик данных» получите диплом о профессиональной переподготовке и готовое портфолио с вашими работами. Знаний и навыков хватит, чтобы пройти собеседование и устроиться на первую работу.

Внешние соединения OUTER JOIN

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

LEFT OUTER JOIN / LEFT JOIN

Это соединение показывает все строки из левой таблицы и добавляет к ним данные из второй таблицы, если они совпадают. Если совпадения нет, в столбцах второй таблицы будет пусто (null).

Источник: sky.pro

Пример

Orders (Заказы):

Customers (Клиенты):

Запрос выглядит так:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

В результате получите:

Все заказы из таблицы Orders попали в результат. Для заказа с CustomerID = 34 в таблице Customers нет клиента, поэтому там стоит null.

RIGHT OUTER JOIN / RIGHT JOIN

Соединение показывает все строки из правой таблицы и добавляет к ним данные из первой таблицы, если они совпадают. Если совпадений нет — в столбцах первой таблицы будет null.

Источник: sky.pro

В качестве примера — те же две таблицы.

Orders (Заказы):

Customers (Клиенты):

Пишете запрос:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

В итоге получаете:

Все клиенты из таблицы Customers попали в результат, потому что это правая таблица. Для клиента с CustomerID = 23 в таблице заказов данных нет, поэтому стоит null.

FULL OUTER JOIN / FULL JOIN

FULL JOIN показывает все строки из обеих таблиц, даже если совпадений нет. Если пары не нашлось, то в части данных будет null.

Источник: sky.pro

Пример

Orders (Заказы):

Customers (Клиенты):

Запрос:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
FULL JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Получаете итог:

FULL JOIN полезен, когда нужно увидеть всё: совпадения и строки, которые остались без пары.

Пройдите курс «Программирование для анализа данных» от Skypro. В программе есть всё что нужно: основы математики, логики, бизнес-процессов, программирование и даже навыки общения. А еще вас будут поддерживать опытные преподаватели и наставники — всегда подскажут и помогут.

Перекрестное соединение CROSS JOIN

CROSS JOIN — это соединение всех со всеми. Здесь не нужны условия объединения. Просто берете каждую строку из первой таблицы и соединяете со всеми строками второй таблицы.

Пример

Orders (Заказы):

Customers (Клиенты):

Запрос:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
CROSS JOIN Customers;

Итог:

Каждая строка из Orders объединилась со всеми строками из Customers. В результате получили все возможные комбинации заказов и клиентов.

Соединение SELF JOIN

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

В SQL нет специальной команды SELF JOIN. Вместо этого используют обычный JOIN, но дают таблице два временных имени (алиаса), чтобы отличать две копии.

Синтаксис с JOIN:

SELECT ...
FROM table_name AS a1
JOIN table_name AS a2
ON a1.column_name = a2.column_name;

Синтаксис с WHERE:

SELECT ...
FROM table_name AS a1
WHERE table_name AS a2

Например, нужно найти студентов, которые учатся на нескольких курсах.

Список Students:

Вариант с WHERE:

SELECT s1.StudentID, s1.Name
FROM Students AS s1, Students AS s2
WHERE s1.StudentID = s2.StudentID
AND s1.CourseID <> s2.CourseID;

Видно студентов, которые ходят на несколько разных курсов.

Вариант с JOIN:

SELECT s1.StudentID, s1.Name
FROM Students s1
JOIN Students s2
ON s1.StudentID = s2.StudentID
AND s1.CourseID <> s2.CourseID
GROUP BY s1.StudentID, s1.Name;

То же самое, но через JOIN и GROUP BY — убрали повторы.

В результате получаете:

Не забывайте про временные имена, чтобы SQL понимал, где «первая» таблица, а где «вторая».

Главное о JOIN в SQL

• Оператор JOIN объединяет данные из разных таблиц.
• JOIN соединяет таблицы по общим столбцам — показывает связанные данные вместе. UNION объединяет результаты нескольких запросов. Складывает один под другим и получает общий список.
• Оператор нужен всем, кто работает с несколькими таблицами, делает отчеты, аналитику, запросы или интерфейсы и хочет видеть все данные сразу.
• Если нужны строки, где есть совпадения в обеих таблицах, используют INNER JOIN. Чтобы получить полный список из первой таблицы, даже без совпадений во второй, применяют LEFT JOIN.
• Если наоборот — полный список из второй таблицы, без совпадений с первой — RIGHT JOIN. Посмотреть все данные из обеих таблиц — берут FULL JOIN.
• Чтобы получить все возможные комбинации строк из двух таблиц, используют CROSS JOIN. А если нужно соединить таблицу с самой собой, подходит SELF JOIN.