В чем разница между LATERAL JOIN и подзапросом в PostgreSQL?

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

select  *
from    table1 t1
cross join lateral
        (
        select  *
        from    t2
        where   t1.col1 = t2.col1 -- Only allowed because of lateral
        ) sub

Этот «внешний вид» означает, что подзапрос должен оцениваться более одного раза. В конце концов, t1.col1 может принимать множество значений.

Напротив, подзапрос после соединения, отличного от lateral, может быть оценен один раз:

select  *
from    table1 t1
cross join
        (
        select  *
        from    t2
        where   t2.col1 = 42 -- No reference to outer query
        ) sub

Как и требуется без lateral, внутренний запрос никоим образом не зависит от внешнего запроса. Запрос lateral является примером запроса correlated из-за его связи со строками вне самого запроса.

Во-первых, Боковое и поперечное применение - это одно и то же. Поэтому вы также можете прочитать о Cross Apply. Поскольку он был реализован в SQL Server на протяжении многих лет, вы найдете больше информации о нем, чем Lateral.

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

Рассмотрим следующий запрос.

Select A.*
, (Select B.Column1 from B where B.Fk1 = A.PK and Limit 1)
, (Select B.Column2 from B where B.Fk1 = A.PK and Limit 1)
FROM A 

Вы можете использовать боковой в этом состоянии.

Select A.*
, x.Column1
, x.Column2
FROM A LEFT JOIN LATERAL (
  Select B.Column1,B.Column2,B.Fk1 from B  Limit 1
) x ON X.Fk1 = A.PK

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

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

Таблица базы данных

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

И в настоящее время у нас есть два блога:

Получение нашего отчета без использования SQL LATERAL JOIN

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

• идентификатор блога
• возраст блога, в годах
• дата следующей годовщины блога
• количество дней, оставшихся до следующей годовщины.

Если вы используете PostgreSQL, вам необходимо выполнить следующий SQL-запрос:

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

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

И именно в этом нам может помочь LATERAL JOIN.

Получение отчета с помощью SQL LATERAL JOIN

Следующие системы реляционных баз данных поддерживают синтаксис LATERAL JOIN:

• Oracle с 12c
• PostgreSQL с 9.3
• MySQL с 8.0.14

SQL Server может имитировать LATERAL JOIN, используя CROSS APPLY и OUTER APPLY.

LATERAL JOIN позволяет нам повторно использовать значение age_in_years и просто передавать его дальше при вычислении значений next_anniversary и days_to_next_anniversary.

Предыдущий запрос можно переписать для использования LATERAL JOIN следующим образом:

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 может быть вычислено и повторно использовано для вычислений next_anniversary и days_to_next_anniversary:

Намного лучше, правда?

age_in_years рассчитывается для каждой записи таблицы blog. Таким образом, он работает как коррелированный подзапрос, но записи подзапроса объединяются с основной таблицей, и по этой причине мы можем ссылаться на столбцы, созданные подзапросом.

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

Например:

CREATE OR REPLACE FUNCTION delete_company(companyId varchar(255))
RETURNS void AS $$
    BEGIN
        DELETE FROM company_settings WHERE "company_id"=company_id;
        DELETE FROM users WHERE "company_id"=companyId;
        DELETE FROM companies WHERE id=companyId;
    END; 
$$ LANGUAGE plpgsql;

SELECT * FROM (
    SELECT id, name, created_at FROM companies WHERE created_at < '2018-01-01'
) c, LATERAL delete_company(c.id);

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