Перевод первой части статьи «SQL Joins Tutorial: Cross Join, Full Outer Join, Inner Join, Left Join, and Right Join».
Операции объединения в SQL позволяют нашим реляционным базам данных быть… хм… реляционными (англ. relational — «относительный»). Они дают нам возможность реконструировать наши отдельные базы данных с учетом отношений между ними, а это важно для наших приложений.
В этой статье вы рассмотрим все виды JOIN в SQL и расскажем, как ими пользоваться.
В первой части статьи:
- Что такое JOIN?
- Настройка базы данных
- CROSS JOIN
- Настройка наших экспериментальных данных
- FULL OUTER JOIN
- INNER JOIN
(Спойлер: мы рассмотрим пять разных видов объединений, но на самом деле вам нужно знать только два из них!)
Что такое JOIN?
JOIN это операция объединения двух строк в одну. Эти строки обычно бывают из двух разных таблиц, но это не обязательно.
Прежде чем мы разберем, как писать 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)
Мы видим всех наших пользователей сразу с их адресами.
Но операции объединения позволяют не просто выводить такие вот комбинированные сведения. У них есть еще одна важная функция: с их помощью можно получать отфильтрованные результаты.
Например, если мы хотим послать настоящие бумажные письма всем пользователям, живущим в Оклахоме, мы можем объединить таблицы и отфильтровать результаты по столбцу city
.
Теперь, когда вы поняли, для чего вообще нужны операции объединения, давайте приступим к написанию запросов!
Настройка базы данных
Прежде чем писать какие-либо запросы, нужно настроить базу данных.
Для примеров в этой статье будет использоваться PostgreSQL, но запросы и концепции, показанные здесь, легко применимы в любой другой современной СУБД (MySQL, SQL Server и т. д.).
Для работы с нашей базой данных PostgreSQL мы будем пользоваться интерактивной cli-программой psql. Если у вас установлен другой клиент, вы прекрасно можете работать с ним!
Для начала давайте создадим нашу базу данных. Поскольку у нас уже установлена PostgreSQL, для создания новой базы данных мы можем запустить в терминале psql-команду createdb <имя-базы-данных>
. Свою базу я назвал fcc
:
$ createdb fcc
Теперь давайте воспользуемся интерактивной консолью (запустив команду psql
) и подключимся к только что созданной базе данных при помощи команды \c <имя-базы-данных>
:
$ psql psql (11.5) Type "help" for help. john=# \c fcc You are now connected to database "fcc" as user "john". fcc=#
Примечание: в примерах я подчистил вывод, чтобы их было легче читать. Поэтому не волнуйтесь, что показанный здесь output не в точности совпадает с тем, что вы видите в своем терминале.
Я советую вам прорабатывать запросы, которые мы будем составлять, писать их вместе со мной и запускать. Работая с примерами, вы поймете и запомните куда больше, чем если будете просто читать.
CROSS JOIN (перекрестное объединение)
Самое простое объединение, которое мы можем сделать, это CROSS JOIN (перекрестное объединение) или «декартово произведение».
При этом объединении мы берем каждую строку одной таблицы и соединяем ее с каждой строкой другой таблицы.
Если у нас есть два списка и в одном из них содержатся цифры 1, 2, 3
, а в другом — буквы А, В, С
, то декартово произведение этих списков будет выглядеть так:
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);
Наши таблицы letters
и numbers
имеют по одному столбцу с простыми текстовыми полями.
Теперь давайте объединим эти таблицы, используя 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)
Это наипростейший вид объединения, но даже на этом примере мы можем видеть, как работает JOIN. Две разных строки (одна из таблицы letters
, другая — из numbers
) объединяются друг с другом, образуя одну строку.
Хотя этот пример часто упоминается как чисто учебный, и для него есть практическое применение: покрытие диапазона дат.
CROSS JOIN с диапазонами дат
Хороший вариант использования CROSS JOIN — брать каждую строку таблицы и объединять ее с каждым днем из диапазона дат.
Скажем, вы создаете приложение, которое должно отслеживать ежедневную рутину (чистка зубов, завтрак, душ).
Если вы хотите генерировать запись для каждой задачи за каждый день прошлой недели, вы можете использовать CROSS JOIN с диапазоном дат.
Чтобы создать диапазон дат, мы можем воспользоваться функцией generate_series:
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');
В нашей таблице tasks есть только один столбец — name
— в который мы добавили несколько задач.
Теперь давайте осуществим перекрестное объединение наших задач с запросом на генерацию дат:
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
Чтобы проиллюстрировать следующие виды объединений, мы воспользуемся примером с фильмами (movies) и режиссерами (movie directors).
Каждый фильм имеет режиссера, но это не является обязательным условием. Может быть ситуация, когда фильм уже анонсировали, но режиссер еще не выбран.
В нашей таблице 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 требуется условие объединения.
Это условие определяет, как именно связаны строки разных таблиц и по какому критерию они должны объединяться.
В нашем примере в таблице movies
есть ссылка на режиссеров через столбец director_id
, который совпадает со столбцом id
в таблице directors
. Эти два столбца мы и будем использовать в качестве условия объединения.
Вот как это выглядит в коде:
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)
Сначала идут несколько строк с фильмами, для которых указаны режиссеры: здесь наше условие явно соблюдается.
Но после мы видим оставшиеся строки из каждой таблицы, но со значениями NULL в тех местах, где в другой таблице не нашлось совпадений.
Примечание: если вы не знакомы со значениями NULL, объяснение можно почитать здесь.
Здесь мы также видим второе различие между перекрестным и полным внешним объединением. FULL OUTER JOIN соединяет одну строку первой таблицы с определенной строкой второй таблицы, а при CROSS JOIN каждая строка одной таблицы соединяется с каждой строкой другой.
INNER JOIN
Следующий вид объединения — внутреннее, INNER JOIN. Внутреннее объединение является наиболее используемым.
INNER JOIN возвращает только те строки, где соблюдается условие объединения.
Если брать наш пример с таблицами movies и directors, внутреннее объединение вернет только те записи, где у фильма есть указанный режиссер.
Синтаксис практически тот же:
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 | 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 *
) и сначала указали таблицу directors, то в результате первыми будут идти данные из этой таблицы, а дальше — из таблицы movies. Но данные, попавшие в выборку, те же.
Это полезное свойство операции внутреннего объединения: не у всех видов объединений оно есть.
Конец первой части. Читайте во второй части:
- LEFT JOIN / RIGHT JOIN
- Фильтрация с использованием LEFT JOIN
- Множественные объединения
- Объединения с дополнительными условиями
- Правда жизни относительно написания запросов с объединениями.
[customscript]techrocks_custom_after_post_html[/customscript]
[customscript]techrocks_custom_script[/customscript]