Типы JOIN. Основы, комбинации, практика21.10.2024 08:00

Привет, Хабр! Меня зовут Татьяна Ошуркова,  я разработчик и системный аналитик. В этой статье я затрону базовую теорию по SQL — оператор JOIN. На примерах мы разберем, как использовать каждый из видов соединений в отдельности, а также посмотрим, как можно комбинировать несколько типов соединений в одном запросе и какой результат мы можем получить.

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

Подписывайтесь на канал в Telegram, где можно найти больше полезных материалов и вебинаров.

Недавно стартовала запись в мою Школу системного анализа, где за 10 недель вы освоите все необходимые навыки на практике с реальными кейсами и получите карьерную консультацию. А до 1 декабря можно получить бонусный курс «Разработка чат-бота на Python».

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

INNER JOIN (или просто JOIN)

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

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

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id

У нас есть две таблицы: сотрудники и их департаменты. В таблице сотрудники (employees) есть поле department_id (идентификатор департамента). Запрос вернет список сотрудников с наименованиями департаментов. В результате мы получим только тех сотрудников, у кого был указан департамент —  не пустое поле department_id.

LEFT OUTER JOIN (или LEFT JOIN)

Тип соединения, который позволяет объединять строки таблиц на основе условия соединения, возвращая все строки из левой таблицы и только соответствующие строки из правой таблицы. Если в правой таблице нет соответствующей строки, в результирующем наборе для этих строк будут возвращены NULL-значения. Рассмотрим предыдущий пример, только теперь используем LEFT JOIN.

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id

Если в первом случае в результирующую выборку попали только сотрудники, у которых был указан department_id, то теперь мы получим список абсолютно всех сотрудников, но у тех, у кого был не указан department_id, в результате будет не заполнено наименование департамента.

Используемые таблицы

Результат:

RIGHT OUTER JOIN (или RIGHT JOIN)

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

Если выполнить предыдущий запрос, заменив LEFT JOIN на RIGHT JOIN, в результирующей выборке мы получим сотрудников, у кого был заполнен department_id, а также департаменты, где отсутствуют сотрудники — ни у одного сотрудника нет соответствующего department_id.

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id

Предположим, что добавился дополнительный департамент «Marketing».

Используемые таблицы

Результат:

FULL OUTER JOIN

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

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id

Можно сказать, что результат данного соединения является объединением результата использования LEFT и RIGHT JOIN.

Если взять таблицы, используемые в предыдущем запросе, то получим следующий результат:

CROSS JOIN

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

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

Далее поговорим об использовании сочетаний различных соединений.

LEFT JOIN + LEFT JOIN

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

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

SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    p.project_name,
    pd.detail AS project_detail
FROM 
    employees e
LEFT JOIN 
    departments d ON e.department_id = d.department_id
LEFT JOIN 
    projects p ON d.department_id = p.department_id
LEFT JOIN 
    project_details pd ON p.project_id = pd.project_id

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

Используемые таблицы

Результат:

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

RIGHT JOIN + RIGHT JOIN

Рассмотрим использование нескольких соединений RIGHT JOIN только с первой таблицей. Предположим, нам необходима выборка всех сотрудников. Также нужно дополнить выборку информацией о проектах сотрудников. Но необходимы только те проекты, которые привязаны к департаменту.

SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    p.project_name
FROM 
    departments d
RIGHT JOIN 
    projects p ON d.department_id = p.department_id
RIGHT JOIN 
    employees e ON d.department_id = e.department_id

Используемые таблицы

Результат:

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

SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    p.project_name
FROM 
    employees e
RIGHT JOIN 
    departments d ON e.department_id = d.department_id
RIGHT JOIN 
    projects p ON d.department_id = p.department_id

Результат:

Нужно отметить, что в итоговой выборке мы видим все проекты, так как это была самая «правая» таблица, как таблица сотрудников в предыдущей выборке. Также в последней выборке были взяты департаменты, к которым привязаны проект. В предыдущей выборке мы брали департаменты, где есть и проекты и сотрудники

LEFT JOIN + RIGHT JOIN

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

SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    p.project_name
FROM 
    employees e
LEFT JOIN 
    departments d ON e.department_id = d.department_id
RIGHT JOIN 
    projects p ON d.department_id = p.department_id

Используемые таблицы

Результат:

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

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

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

Также делюсь скриптом на Python, с помощью которого можно попрактиковаться в запросах SQL без установки дополнительных программ и сложного развертывания базы данных.

Удачи в использовании оператора JOIN на практике!

© Habrahabr.ru