Зарегистрируйтесь для доступа к 15+ бесплатным курсам по программированию с тренажером

Соединения (JOINS) Основы реляционных баз данных

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

В простых ситуациях данные извлекаются так:

-- Извлекаем все топики пользователя с id = 3
SELECT * FROM topics WHERE user_id = 3;

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

JOIN

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

  1. Извлечь из базы всех пользователей, которые создали хотя бы один топик:

    SELECT DISTINCT user_id FROM topics;
    
  2. Найти всех пользователей, у которых идентификаторы не совпадают со списком user_id, полученном на предыдущем этапе:

    SELECT * FROM users
      WHERE id NOT IN(<список идентификаторов, полученный предыдущим запросом>);
    

Задача будет решена, но есть одна проблема. Идентификаторов может быть очень много. Гонять такое количество записей из базы в код и обратно — не самая разумная идея.

INNER JOIN

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

Реляционная алгебра позволяет выполнить эту операцию с помощью соединения JOIN, используя ровно один запрос. Начнем знакомство с JOIN на таком примере:

-- В выборке участвуют не все поля только для того, чтобы уместить
-- ее на экран, а вообще здесь можно использовать `*`
SELECT first_name, title
  FROM users JOIN topics ON users.id = topics.user_id LIMIT 5;

 first_name |            title
------------+------------------------------
 Sean       | beatae voluptatem commodi
 Wyatt      | tempora accusamus nostrum
 Oleta      | eaque fugiat consequatur
 Brandon    | aut exercitationem expedita
 Domenica   | voluptatem soluta similique

Результатом такого запроса станет выборка, в которую попали поля обеих таблиц. Здесь соединяются две таблицы: users и topics по условию users.id = topics.user_id. Это важное условие для правильной работы.

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

SELECT * FROM table1 JOIN table2 ON table1.id_field_name = table2.id_field_name

На самом деле общая форма сложнее, потому что объединять можно произвольное число таблиц. Другими словами, условий соединения может быть много.

JOIN — это сокращенная версия соединения INNER JOIN, то есть внутреннего соединения.

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

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

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

SELECT id FROM users JOIN topics ON users.id = topics.user_id LIMIT 5;
ERROR:  column reference "id" is ambiguous
LINE 1: SELECT id FROM users JOIN topics ON users.id = topics.user_i...

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

SELECT users.id AS user_id, topics.id AS topic_id
  FROM users JOIN topics ON users.id = topics.user_id LIMIT 5;

 user_id | topic_id
---------+----------
       9 |        1
      33 |        2
      43 |        3
      49 |        4
      10 |        5
(5 rows)

LEFT JOIN

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

Left Join

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

SELECT first_name, title FROM users
  LEFT JOIN topics ON users.id = topics.user_id LIMIT 5;

first_name |            title
------------+------------------------------
 Sean       | beatae voluptatem commodi
 Wyatt      | tempora accusamus nostrum
 Mia        |
 Royal      |
 Enos       | et eos dicta

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

Этот запрос все еще не возвращает нам то, что мы хотели изначально — записи о пользователях, которые не оставили ни одного топика на Хекслете. Чтобы закончить решение, нужно добавить в выборку условие WHERE:

SELECT COUNT(*)
  FROM users
  LEFT JOIN topics ON users.id = topics.user_id
  WHERE title IS NULL;

 count
-------
    59
(1 row)

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

Выводы


Дополнительные материалы

  1. Официальная документация
  2. Визуализация соединений

Аватары экспертов Хекслета

Остались вопросы? Задайте их в разделе «Обсуждение»

Вам ответят команда поддержки Хекслета или другие студенты.

Для полного доступа к курсу нужен базовый план

Базовый план откроет полный доступ ко всем курсам, упражнениям и урокам Хекслета, проектам и пожизненный доступ к теории пройденных уроков. Подписку можно отменить в любой момент.

Получить доступ
1000
упражнений
2000+
часов теории
3200
тестов

Открыть доступ

Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно.

  • 130 курсов, 2000+ часов теории
  • 1000 практических заданий в браузере
  • 360 000 студентов
Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и соглашаетесь с «Условиями использования»

Наши выпускники работают в компаниях:

Логотип компании Альфа Банк
Логотип компании Aviasales
Логотип компании Yandex
Логотип компании Tinkoff
Рекомендуемые программы

С нуля до разработчика. Возвращаем деньги, если не удалось найти работу.

Иконка программы Python-разработчик
Профессия
с нуля
Разработка веб-приложений на Django
1 декабря 10 месяцев
Иконка программы Java-разработчик
Профессия
с нуля
Разработка приложений на языке Java
1 декабря 10 месяцев
Иконка программы PHP-разработчик
Профессия
с нуля
Разработка веб-приложений на Laravel
1 декабря 10 месяцев
Иконка программы Инженер по тестированию
Профессия
с нуля
Ручное тестирование веб-приложений
дата определяется 4 месяца
Иконка программы Node.js-разработчик
Профессия
с нуля
Разработка бэкенд-компонентов для веб-приложений
1 декабря 10 месяцев
Иконка программы Fullstack-разработчик
Профессия
с нуля
Разработка фронтенд- и бэкенд-компонентов для веб-приложений
1 декабря 16 месяцев

Используйте Хекслет по-максимуму!

  • Задавайте вопросы по уроку
  • Проверяйте знания в квизах
  • Проходите практику прямо в браузере
  • Отслеживайте свой прогресс

Зарегистрируйтесь или войдите в свой аккаунт

Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и соглашаетесь с «Условиями использования»