Это все твое! Бесплатный урок по курсу «Python Advanced. Продвинутый курс», а также:

  • Курс по Git и Github с нуля
  • Создание ботов на Python
  • Скидка 2 200 ₽ на покупку курса

    • Готово! Отправили доступы на вашу электронную почту

    Разработка
    Обзоры
    21.09.2024
    6 минут
    img

    Соединения в SQL

    Соединение (JOIN) — одна из самых важных операций, выполняемых реляционными системами управления базами данных. В этой статье мы рассмотрим разные типы соединений: перекрестное соединение (Cross Join), полное внешнее соединение (Full Outer Join), внутреннее соединение (Inner Join), левое (Left Join) и правое соединение (Right Join).

    Что такое соединение?

    Соединение - это операция, которая объединяет две строки в одну.

    Обычно эти строки берутся из двух разных таблиц, но это не обязательно так.

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

    Возьмем, к примеру, систему, хранящую информацию о пользователях и их адресах.

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

    id |     name     |        email        | age
----+--------------+---------------------+-----
  1 | John Smith   | johnsmith@gmail.com |  25
  2 | Jane Doe     | janedoe@Gmail.com   |  28
  3 | Xavier Wills | xavier@wills.io     |  3
...
(7 rows)

    Строки из таблицы, хранящей адресную информацию, могут выглядеть следующим образом:

    id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

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

    Именно это и позволяет сделать объединение!

    Мы скоро рассмотрим, как писать такие соединения, но если бы мы соединили информацию о пользователях с информацией об адресах, то получили бы такой результат:

    id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | johnsmith@gmail.com |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | janedoe@Gmail.com   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | xavier@wills.io     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

    Здесь мы видим всех наших пользователей и их адреса в одном красивом наборе результатов.

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

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

    Теперь, когда мы знаем назначение соединений, давайте приступим к их написанию!

    Настройка базы данных

    Прежде чем писать запросы, необходимо настроить базу данных.

    Для этих примеров мы будем использовать PostgreSQL, но запросы и концепции, показанные здесь, можно легко перенести на любую другую современную систему баз данных (например, MySQL, SQL Server и т. д.).

    Для работы с базой данных PostgreSQL мы можем использовать psql - интерактивную программу командной строки PostgreSQL. Если у вас есть другой клиент базы данных, с которым вам нравится работать, это тоже хорошо.

    Для начала давайте создадим нашу базу данных. Если PostgreSQL уже установлен, мы можем выполнить команду createdb <database-name> в терминале, чтобы создать новую базу данных. Я назвал свою базу данных fcc:

    $ createdb fcc

    Далее запустим интерактивную консоль командой psql и подключимся к базе данных, которую мы только что создали, используя \c <database-name> :

    $ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

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

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

    Теперь перейдем к соединениям!

    CROSS JOIN — перекрестное соединение 

    Самый простой вид соединения — это CROSS JOIN или «декартово произведение».

    Это соединение берет каждую строку из одной таблицы и соединяет ее с каждой строкой другой таблицы.

    Если бы у нас было два списка, один из которых содержал 1, 2, 3, а другой - A, B, C, то декартово произведение этих двух списков было бы таким:

    1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

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

    Давайте запишем этот же пример в виде SQL-запроса.

    Сначала создадим две очень простые таблицы и вставим в них некоторые данные:

    CREATE TABLE letters(
  letter TEXT
);

INSERT INTO letters(letter) VALUES ('A'), ('B'), ('C');

CREATE TABLE numbers(
  number TEXT
);

INSERT INTO numbers(number) VALUES (1), (2), (3);

    Наши две таблицы, буквы и цифры, имеют только один столбец: простое текстовое поле.

    Теперь давайте соединим их вместе с помощью CROSS JOIN:

    SELECT *
FROM letters
CROSS JOIN numbers;
     
    letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

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

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

    CROSS JOIN с диапазонами дат

    Один из хороших вариантов использования CROSS JOIN - взять каждую строку из таблицы и применить ее к каждому дню в диапазоне дат.

    Допустим, вы создаете приложение, которое отслеживает ежедневные задачи - например, чистку зубов, завтрак или принятие душа.

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

    Чтобы создать этот диапазон дат, мы можем использовать функцию [generate_series](https://www.postgresql.org/docs/current/functions-srf.html):

    SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

    Функция generate_series принимает три параметра.

    Первый параметр - это начальное значение. В этом примере мы используем CURRENT_DATE - INTERVAL '5 day'. Это возвращает текущую дату минус пять дней - или «пять дней назад».

    Второй параметр - текущая дата (CURRENT_DATE).

    Третий параметр - это «интервал шага», то есть то, на сколько мы хотим увеличивать значение каждый раз. Поскольку это ежедневные задачи, мы будем использовать интервал в один день (INTERVAL '1 day').

    Если сложить все вместе, то получится серия дат, начинающаяся пять дней назад, заканчивающаяся сегодня и увеличивающаяся на один день за раз.

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

    Результатом этого запроса являются последние пять дней плюс сегодняшний:

      day
------------
 2020-08-19
 2020-08-20
 2020-08-21
 2020-08-22
 2020-08-23
 2020-08-24
(6 rows)

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

    CREATE TABLE tasks(
  name TEXT
);

INSERT INTO tasks(name) VALUES
('Brush teeth'),
('Eat breakfast'),
('Shower'),
('Get dressed');

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

    Теперь давайте сделаем CROSS JOIN наших задач с запросом, чтобы сгенерировать даты:

    SELECT
  tasks.name,
  dates.day
FROM tasks
CROSS JOIN
(
  SELECT generate_series(
    (CURRENT_DATE - INTERVAL '5 day'),
    CURRENT_DATE,
    INTERVAL '1 day'
  )::DATE    AS day
) AS dates

    (Поскольку наш запрос на генерацию даты не является таблицей, мы просто записываем его в виде подзапроса).

    Из этого запроса мы возвращаем название задачи и день, а набор результатов выглядит следующим образом:

    name      |    day
---------------+------------
 Brush teeth   | 2020-08-19
 Brush teeth   | 2020-08-20
 Brush teeth   | 2020-08-21
 Brush teeth   | 2020-08-22
 Brush teeth   | 2020-08-23
 Brush teeth   | 2020-08-24
 Eat breakfast | 2020-08-19
 Eat breakfast | 2020-08-20
 Eat breakfast | 2020-08-21
 Eat breakfast | 2020-08-22
 ...
 (24 rows)

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

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

    Создание режиссеров и фильмов

    Чтобы проиллюстрировать следующие типы соединений, мы воспользуемся примером фильмов и режиссеров.

    В этой ситуации у фильма есть один режиссер, но фильм не обязан иметь режиссера - представьте, что анонсирован новый фильм, но выбор режиссера еще не подтвержден.

    В нашей таблице directors будет храниться имя каждого режиссера, а в таблице movies - название фильма и ссылка на режиссера фильма (если он есть).

    Давайте создадим эти две таблицы и вставим в них данные:

    CREATE TABLE directors(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO directors(name) VALUES
('John Smith'),
('Jane Doe'),
('Xavier Wills')
('Bev Scott'),
('Bree Jensen');

CREATE TABLE movies(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  director_id INTEGER REFERENCES directors 
);

INSERT INTO movies(name, director_id) VALUES
('Movie 1', 1),
('Movie 2', 1),
('Movie 3', 2),
('Movie 4', NULL),
('Movie 5', NULL);

    У нас есть пять режиссеров, пять фильмов, и у трех из этих фильмов есть назначенные режиссеры. У режиссера ID 1 есть два фильма, а у режиссера ID 2 - один.

    FULL OUTER JOIN — полное внешнее соединение

    Теперь, когда у нас есть данные для работы, давайте рассмотрим полное внешнее соединение (FULL OUTER JOIN).

    FULL OUTER JOIN имеет некоторые сходства с CROSS JOIN, но у него есть и пара ключевых отличий.

    Первое отличие заключается в том, что для FULL OUTER JOIN требуется условие присоединения.

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

    В нашем примере таблица «Фильмы» имеет ссылку на режиссера через столбец director_id, и этот столбец совпадает со столбцом id таблицы «Режиссеры». Именно эти два столбца мы будем использовать в качестве условия объединения.

    Вот как мы напишем это соединение между нашими двумя таблицами:

    SELECT *
FROM movies
FULL OUTER JOIN directors
  ON directors.id = movies.director_id;

    Обратите внимание на указанное нами условие объединения, которое сопоставляет фильм с его режиссером: ON movies.director_id = directors.id.

    Наш набор результатов выглядит как нечетное декартово произведение:

    id  |  name   | director_id |  id  |     name
------+---------+-------------+------+--------------
    1 | Movie 1 |           1 |    1 | John Smith
    2 | Movie 2 |           1 |    1 | John Smith
    3 | Movie 3 |           2 |    2 | Jane Doe
    4 | Movie 4 |        NULL | NULL | NULL
    5 | Movie 5 |        NULL | NULL | NULL
 NULL | NULL    |        NULL |    5 | Bree Jensen
 NULL | NULL    |        NULL |    4 | Bev Scott
 NULL | NULL    |        NULL |    3 | Xavier Wills
(8 rows)

    Первыми мы видим строки, в которых у фильма есть режиссер, и наше условие объединения оценивается как true.

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

    Примечание: если вы не знакомы со значениями NULL, посмотрите мое объяснение в этом учебнике по операторам SQL.

    Здесь мы также видим еще одно различие между CROSS JOIN и FULL OUTER JOIN. FULL OUTER JOIN возвращает одну отдельную строку из каждой таблицы - в отличие от CROSS JOIN, который имеет несколько строк.

    INNER JOIN — внутреннее соединение

    Следующий тип соединения, INNER JOIN, является одним из наиболее часто используемых типов соединения.

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

    В нашем примере внутреннее соединение между таблицами «Фильмы» и «Режиссеры» вернет только те записи, в которых фильму назначен режиссер.

    Синтаксис в основном такой же, как и раньше:

    SELECT *
FROM movies
INNER JOIN directors
  ON directors.id = movies.director_id;

    Наш результат показывает три фильма, у которых есть режиссер:

    id |  name   | director_id | id |    name
----+---------+-------------+----+------------
  1 | Movie 1 |           1 |  1 | John Smith
  2 | Movie 2 |           1 |  1 | John Smith
  3 | Movie 3 |           2 |  2 | Jane Doe
(3 rows)

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

    Если мы изменим порядок таблиц в запросе, то получим тот же результат:

    SELECT *
FROM directors
INNER JOIN movies
  ON movies.director_id = directors.id;
     
    id |    name    | id |  name   | director_id
----+------------+----+---------+-------------
  1 | John Smith |  1 | Movie 1 |           1
  1 | John Smith |  2 | Movie 2 |           1
  2 | Jane Doe   |  3 | Movie 3 |           2
(3 rows)

    Поскольку в этом запросе мы сначала перечислили таблицу «Режиссеры» и выбрали все столбцы (SELECT *), мы видим сначала данные столбца «Режиссеры», а затем столбцы «Фильмы», но результирующие данные одинаковы.

    Это полезное свойство внутренних объединений, но оно верно не для всех типов объединений - например, для следующего типа.

    LEFT JOIN / RIGHT JOIN

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

    Примечание: LEFT JOIN и RIGHT JOIN также могут называться LEFT OUTER JOIN и RIGHT OUTER JOIN.

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

    Если связанной таблицы не существует, мы все равно получим все данные «основной» таблицы.

    Это запрос информации о конкретной вещи и бонусной информации, если такая информация существует.

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

    SELECT *
FROM movies
LEFT JOIN directors
  ON directors.id = movies.director_id;

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

    В этом примере «левой» таблицей является таблица «Фильмы».

    Если мы напишем запрос в одну строку, это будет немного проще увидеть:

    ... FROM movies LEFT JOIN directors ...

    Левое соединение возвращает все записи из «левой» таблицы.

    Левое соединение возвращает все строки из «правой» таблицы, которые соответствуют условию соединения.

    Строки из «правой» таблицы, не соответствующие условию соединения, возвращаются как NULL.

    id |  name   | director_id |  id  |    name
----+---------+-------------+------+------------
  1 | Movie 1 |           1 |    1 | John Smith
  2 | Movie 2 |           1 |    1 | John Smith
  3 | Movie 3 |           2 |    2 | Jane Doe
  4 | Movie 4 |        NULL | NULL | NULL
  5 | Movie 5 |        NULL | NULL | NULL
(5 rows)

    Глядя на этот набор результатов, мы можем понять, почему этот тип объединения полезен для запросов типа «все это и, если оно существует, кое-что из этого».

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

    В правом соединении возвращаются все строки из «правой» таблицы. Данные из «левой» таблицы возвращаются условно, на основании условия соединения.

    Давайте используем тот же запрос, что и выше, но заменим LEFT JOIN на RIGHT JOIN:

    SELECT *
FROM movies
RIGHT JOIN directors
  ON directors.id = movies.director_id;
     
    id  |  name   | director_id | id |     name
------+---------+-------------+----+--------------
    1 | Movie 1 |           1 |  1 | John Smith
    2 | Movie 2 |           1 |  1 | John Smith
    3 | Movie 3 |           2 |  2 | Jane Doe
 NULL | NULL    |        NULL |  5 | Bree Jensen
 NULL | NULL    |        NULL |  4 | Bev Scott
 NULL | NULL    |        NULL |  3 | Xavier Wills
(6 rows)

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

    LEFT JOIN / RIGHT JOIN в производственных приложениях

    В производственных приложениях я всегда использую только LEFT JOIN и никогда не использую RIGHT JOIN.

    Я делаю это потому, что, по моему мнению, LEFT JOIN делает запрос более легким для чтения и понимания.

    Когда я пишу запросы, мне нравится думать о том, чтобы начинать с «базового» набора результатов, скажем, всех фильмов, а затем добавлять (или вычитать) группы вещей из этой базы.

    Поскольку мне нравится начинать с базы, LEFT JOIN подходит для этой линии мышления. Мне нужны все строки из моей базовой таблицы («левая» таблица), и я условно хочу получить строки из «правой» таблицы.

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

    Переписывание RIGHT JOIN

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

    Все, что нам нужно сделать, — это изменить порядок таблиц в запросе и заменить RIGHT на LEFT:

    SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id;

    Примечание: мне нравится ставить таблицу, к которой происходит присоединение («правая» таблица - в примере с фильмами), первой в условии присоединения (ON movies.director_id = ...) - но это только мои личные предпочтения.

    Фильтрация с помощью LEFT JOIN

    Существует два варианта использования LEFT JOIN (или RIGHT JOIN).

    Первый случай мы уже рассмотрели: возврат всех строк из одной таблицы и условно из другой.

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

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

    Для этого мы начнем с LEFT JOIN, и наша таблица режиссеров будет первичной или «левой» таблицей:

    SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id;

    Для режиссера, не относящегося к фильму, столбцы таблицы «Фильмы» имеют значение NULL:

    id |     name     |  id  |  name   | director_id
----+--------------+------+---------+-------------
  1 | John Smith   |    1 | Movie 1 |           1
  1 | John Smith   |    2 | Movie 2 |           1
  2 | Jane Doe     |    3 | Movie 3 |           2
  5 | Bree Jensen  | NULL | NULL    |        NULL
  4 | Bev Scott    | NULL | NULL    |        NULL
  3 | Xavier Wills | NULL | NULL    |        NULL
(6 rows)

    В нашем примере идентификаторы режиссера 3, 4 и 5 не принадлежат ни одному фильму.

    Чтобы отфильтровать наш набор результатов только по этим строкам, мы можем добавить предложение WHERE, чтобы возвращать только строки, в которых данные о фильме равны NULL:

    SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id
WHERE movies.id IS NULL;
     
    id |     name     |  id  | name | director_id
----+--------------+------+------+-------------
  5 | Bree Jensen  | NULL | NULL |        NULL
  4 | Bev Scott    | NULL | NULL |        NULL
  3 | Xavier Wills | NULL | NULL |        NULL
(3 rows)

    И вот наши три режиссера без фильмов!

    Обычно для фильтрации используется столбец id таблицы (WHERE movies.id IS NULL), но все столбцы таблицы movies имеют NULL, поэтому подойдет любой из них.

    (Поскольку мы знаем, что все столбцы из таблицы movies будут NULL, в приведенном выше запросе мы могли бы просто написать SELECT directors.* вместо SELECT *, чтобы просто вернуть всю информацию о режиссерах).

    Использование LEFT JOIN для поиска совпадений

    В нашем предыдущем запросе мы нашли режиссеров, которые не принадлежали фильмам.

    Используя ту же структуру, мы можем найти режиссеров, которые действительно принадлежат фильмам, изменив условие WHERE, чтобы искать строки, в которых данные о фильме не являются NULL:

    SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id
WHERE movies.id IS NOT NULL;
     
    id |    name    | id |  name   | director_id
----+------------+----+---------+-------------
  1 | John Smith |  1 | Movie 1 |           1
  1 | John Smith |  2 | Movie 2 |           1
  2 | Jane Doe   |  3 | Movie 3 |           2
(3 rows)

    Это может показаться удобным, но на самом деле мы просто повторно реализовали INNER JOIN!

    Множественные соединения

    Мы уже видели, как объединить две таблицы, но как насчет нескольких объединений подряд?

    На самом деле это довольно просто, но чтобы проиллюстрировать это, нам понадобится третья таблица: билеты.

    В этой таблице будут представлены билеты, проданные на фильм:

    CREATE TABLE tickets(
  id SERIAL PRIMARY KEY,
  movie_id INTEGER REFERENCES movies NOT NULL
);

INSERT INTO tickets(movie_id) VALUES (1), (1), (3);

    В таблице билетов есть только идентификатор и ссылка на фильм: movie_id.

    Мы также вставили два билета, проданных на фильм ID 1, и один билет, проданный на фильм ID 3.

    Теперь давайте соединим режиссеров с фильмами, а затем фильмы с билетами!

    SELECT *
FROM directors
INNER JOIN movies
  ON movies.director_id = directors.id
INNER JOIN tickets
  ON tickets.movie_id = movies.id;

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

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

    В нашем наборе результатов можно заметить, что мы еще больше сузили возвращаемые строки:

    id |    name    | id |  name   | director_id | id | movie_id
----+------------+----+---------+-------------+----+----------
  1 | John Smith |  1 | Movie 1 |           1 |  1 |        1
  1 | John Smith |  1 | Movie 1 |           1 |  2 |        1
  2 | Jane Doe   |  3 | Movie 3 |           2 |  3 |        3
(3 rows)

    Это имеет смысл, потому что мы добавили еще один INNER JOIN. По сути, это добавляет еще одно условие «И» к нашему запросу.

    Наш запрос, по сути, говорит: «Вернуть всех режиссеров, которые принадлежат к фильмам, на которые также есть продажи билетов».

    Если бы вместо этого мы хотели найти режиссеров, которые принадлежат к фильмам, на которые еще не проданы билеты, мы могли бы заменить наш последний INNER JOIN на LEFT JOIN:

    SELECT *
FROM directors
JOIN movies
  ON movies.director_id = directors.id
LEFT JOIN tickets
  ON tickets.movie_id = movies.id;

    Мы видим, что фильм №2 снова появился в наборе результатов:

    id |    name    | id |  name   | director_id |  id  | movie_id
----+------------+----+---------+-------------+------+----------
  1 | John Smith |  1 | Movie 1 |           1 |    1 |        1
  1 | John Smith |  1 | Movie 1 |           1 |    2 |        1
  2 | Jane Doe   |  3 | Movie 3 |           2 |    3 |        3
  1 | John Smith |  2 | Movie 2 |           1 | NULL |     NULL
(4 rows)

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

    Я оставлю это упражнение для читателей, но как найти режиссеров, которые принадлежат к фильмам, на которые не было продано ни одного билета?

    Порядок выполнения соединения

    В конце концов, нам неважно, в каком порядке выполняются соединения.

    Одно из ключевых отличий SQL от других современных языков программирования заключается в том, что SQL - это декларативный язык.

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

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

    (Примечание: это несколько упрощенное объяснение).

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

    Объединения с дополнительными условиями

    Последняя тема, которую мы рассмотрим, — это объединение с дополнительными условиями.

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

    Например, если мы хотим найти фильмы с режиссерами, которых не зовут «Джон Смит», мы можем добавить это дополнительное условие к нашему соединению с помощью AND:

    SELECT *
FROM movies
INNER JOIN directors
  ON directors.id = movies.director_id
  AND directors.name <> 'John Smith';

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

    Мы также получим тот же результат, если поместим условие в предложение WHERE:

    SELECT *
FROM movies
INNER JOIN directors
  ON directors.id = movies.director_id
WHERE directors.name <> 'John Smith';

    Здесь есть несколько тонких различий, но для целей этой статьи набор результатов одинаков.

    (Если вы не знакомы со всеми способами фильтрации SQL-запросов, ознакомьтесь с ранее упомянутой статьей здесь).

    Реальность написания запросов с соединениями

    На самом деле я использую соединения только в трех различных вариантах:

    INNER JOIN

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

    Это такие ситуации, как поиск «фильмов, у которых есть режиссеры» или «пользователей с постами».

    LEFT JOIN

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

    Это такие ситуации, как «фильмы с режиссерами, если у них есть режиссер» или «пользователи с постами, если у них есть посты».

    Исключение LEFT JOIN

    Третий наиболее распространенный случай использования - это наш второй случай использования LEFT JOIN: поиск записей в одной таблице, которые не имеют отношения ко второй таблице.

    Это такие ситуации, как «фильмы без режиссеров» или «пользователи без сообщений».

    Два самых полезных типа объединений

    Не думаю, что я когда-либо использовал FULL OUTER JOIN или RIGHT JOIN в производственном приложении. Просто такие случаи не так часто встречаются, или запрос можно написать более понятным способом (в случае RIGHT JOIN).

    Итак, хорошие новости! На самом деле существует только два типа объединений, которые необходимо понимать для 99,9% случаев использования: INNER JOIN и LEFT JOIN!

     

    SQL
    JOIN
    Cross Join
    Inner Join
    Left Join
    Right Join
    Full Outer Join
    Ссылка
    скопирована
    Получите бесплатные уроки на наших курсах
    Все курсы
    Программирование
    Скидка 25%
    Python Advanced. Продвинутый курс
    Освойте асинхронное и метапрограммирование, изучите аннотацию типов и напишите собственное приложение на FastAPI. Улучшите свои навыки Python, чтобы совершить быстрый рост вашего грейда до уровня middle.
    Бесплатный урок
    Подробнее
    Получи бесплатный
    вводный урок!
    Пожалуйста, укажите корректный e-mail
    отправили вводный урок на твой e-mail!
    Получи все материалы в telegram и ускорь обучение!
    Получить
    img
    Еще по теме:
    img
    Разработка
    How to
    Погружение в aiogram (#3 types.Message)
    В этой статье обсудим один из важнейших аргументов функции, который ТЫ, мой друг, будешь использовать в каждом своем боте.  Ты с
    img
    Разработка
    How to
    Погружение в aiogram (#4 message_handler())
    Введение    Настало время глубже погрузиться во взаимодействие человека с ботом. Сегодня изучим декоратор message_handler(). Узн
    img
    Разработка
    How to
    Погружение в aiogram (#5 Отправка стикеров)
    Погружение в aiogram (#5 Отправка стикеров)   Введение   Продолжаем изучать функционал библиотеки aiogram для работы с Telegram
    img
    Разработка
    Обзоры
    Proxmox против ESXi: сравниваем два гипервизора
    Гипервизор - это программное обеспечение для виртуализации, используемое для создания и запуска виртуальных машин (ВМ). Гипервиз
    img
    Разработка
    Обзоры
    Сравниваем Proxmox и Hyper: какой гипервизор выбрать
    Виртуализация серверов позволяет запускать несколько виртуальных машин на одном физическом сервере. Запуск виртуальных машин (ВМ
    img
    Разработка
    How to
    Руководство по PHP: настраиваем PHP и XAMPP для вашего проекта
    Сегодня мы рассмотрим, как настроить и использовать PHP в проекте. Но прежде чем начать, нужно понять, что такое PHP. Что такое
    Комментарии
    ЗИМНИЕ СКИДКИ
    40%
    50%
    60%
    До конца акции: 30 дней 24 : 59 : 59
    Выбрать курс