Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье мы рассмотрим, как работает БОКОВОЕ СОЕДИНЕНИЕ SQL и как мы можем использовать его для перекрестных ссылок на строки из подзапроса со строками во внешней таблице и построения составных результирующих наборов.
БОКОВОЕ соединение может использоваться либо явно, как мы увидим в этой статье, либо неявно, как в случае с функцией MySQL JSON_TABLE .
Таблица базы данных
Давайте предположим, что у нас есть следующая блог таблица базы данных, в которой хранятся блоги, размещенные на нашей платформе:
И в настоящее время у нас есть два блога, размещенных:
| id | created_on | title | url | |----|------------|----------------------|--------------------------| | 1 | 2013-09-30 | Vlad Mihalcea's Blog | https://vladmihalcea.com | | 2 | 2017-01-22 | Hypersistence | https://hypersistence.io |
Получение отчета без использования БОКОВОГО СОЕДИНЕНИЯ SQL
Нам нужно создать отчет, который извлекает следующие данные из таблицы блог :
- идентификатор блога
- возраст блога, в годах
- дата следующей годовщины блога
- количество дней, оставшихся до следующей годовщины.
Вычисление возраста блога с использованием функций интервала дат
Возраст блога необходимо рассчитать, вычитая дату создания блога из текущей даты.
Дату следующей годовщины блога можно рассчитать, увеличив возраст в годах и добавив его к дате создания блога.
Количество дней до следующей годовщины можно рассчитать, извлекая количество дней из интервала, заданного следующей годовщиной блога, и текущей даты.
В зависимости от используемой реляционной базы данных это можно сделать следующими способами.
Для PostgreSQL вы можете использовать следующий запрос:
SELECT
b.id as blog_id,
extract(
YEAR FROM age(now(), b.created_on)
) AS age_in_years,
date(
created_on + (
extract(YEAR FROM age(now(), b.created_on)) + 1
) * interval '1 year'
) AS next_anniversary,
date(
created_on + (
extract(YEAR FROM age(now(), b.created_on)) + 1
) * interval '1 year'
) - date(now()) AS days_to_next_anniversary
FROM blog b
ORDER BY blog_id
И вы получите ожидаемый результат:
| blog_id | age_in_years | next_anniversary | days_to_next_anniversary | |---------|--------------|------------------|--------------------------| | 1 | 7 | 2021-09-30 | 295 | | 2 | 3 | 2021-01-22 | 44 |
Если вы используете MySQL, то вам необходимо выполнить следующий SQL-запрос:
SELECT
b.id as blog_id,
TIMESTAMPDIFF(
YEAR,
b.created_on, CURRENT_TIMESTAMP()
) AS age_in_years,
DATE_ADD(
created_on,
INTERVAL(
TIMESTAMPDIFF(
YEAR,
b.created_on, CURRENT_TIMESTAMP()
) + 1
) YEAR
) AS next_anniversary,
TIMESTAMPDIFF(
DAY,
CURRENT_TIMESTAMP(),
DATE_ADD(
created_on,
INTERVAL(
TIMESTAMPDIFF(
YEAR,
b.created_on,
CURRENT_TIMESTAMP()
) + 1
) YEAR
)
) AS days_to_next_anniversary
FROM blog b
ORDER BY blog_id
Как вы можете видеть, значение age_in_years должно быть определено три раза, потому что оно необходимо при расчете значений next_anniversary и days_to_next_anniversary .
И именно в этом БОКОВОЕ СОЕДИНЕНИЕ может нам помочь.
Получение отчета с помощью БОКОВОГО СОЕДИНЕНИЯ SQL
Следующие системы реляционных баз данных поддерживают синтаксис БОКОВОГО СОЕДИНЕНИЯ :
- Оракул с 12 века
- PostgreSQL с 9.3
- MySQL с 8.0.14
SQL Server может эмулировать БОКОВОЕ СОЕДИНЕНИЕ , используя ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ и ВНЕШНЕЕ ПРИМЕНЕНИЕ .
БОКОВОЕ СОЕДИНЕНИЕ позволяет нам уменьшить значение age_in_years и просто передать его дальше при расчете значений next_anniversary и days_to_next_anniversary .
Например, предыдущий запрос PostgreSQL может быть переписан следующим образом:
SELECT
b.id as blog_id,
age_in_years,
date(
created_on + (age_in_years + 1) * interval '1 year'
) AS next_anniversary,
date(
created_on + (age_in_years + 1) * interval '1 year'
) - date(now()) AS days_to_next_anniversary
FROM blog b
CROSS JOIN LATERAL (
SELECT
cast(
extract(YEAR FROM age(now(), b.created_on)) AS int
) AS age_in_years
) AS t
ORDER BY blog_id
И, значение age_in_years может быть вычислено одно и повторно использовано для следующего года и дней_то_некст_векст вычислений:
| blog_id | age_in_years | next_anniversary | days_to_next_anniversary | |---------|--------------|------------------|--------------------------| | 1 | 7 | 2021-09-30 | 295 | | 2 | 3 | 2021-01-22 | 44 |
И, для MySQL, предыдущий запрос может быть переписан для использования БОКОВОГО СОЕДИНЕНИЯ следующим образом:
SELECT
b.id as blog_id,
age_in_years,
DATE_ADD(
created_on,
INTERVAL (age_in_years + 1) YEAR
) AS next_anniversary,
TIMESTAMPDIFF(
DAY,
CURRENT_TIMESTAMP(),
DATE_ADD(
created_on,
INTERVAL (age_in_years + 1) YEAR
)
) + 1 AS days_to_next_anniversary
FROM blog b
CROSS JOIN LATERAL (
SELECT
TIMESTAMPDIFF(
YEAR,
b.created_on,
CURRENT_TIMESTAMP()
) AS age_in_years
) AS t
ORDER BY blog_id
Намного лучше, правда?
Значение age_in_years рассчитывается для каждой записи в таблице блог . Таким образом, он работает как коррелированный подзапрос, но записи подзапроса соединены с основной таблицей, и по этой причине мы можем ссылаться на столбцы, созданные подзапросом.
Вывод
БОКОВОЕ СОЕДИНЕНИЕ-очень полезная функция. Это позволяет инкапсулировать данное вычисление в подзапросе и повторно использовать его во внешнем запросе.
В отличие от прямого соединения с производной таблицей, БОКОВОЕ СОЕДИНЕНИЕ оценивается для каждой записи в основной таблице, а не только один раз.