В чем разница между латеральным и подзапросом в PostgreSQL?

еще как коррелируется подзапрос

A LATERAL join (Postgres 9.3+) больше похоже на коррелированный подзапрос, а не простой подзапрос. Как @Andomar указал, функция или подзапрос справа от LATERAL join обычно должен оцениваться много раз-один раз для каждой строки слева от LATERAL присоединиться-так же, как коррелируется подзапрос - при вычислении простого подзапроса (табличного выражения) после только. (Однако планировщик запросов имеет способы оптимизации производительности для любого из них.)
Этот связанный ответ имеет примеры кода для обоих бок о бок, решая одну и ту же проблему:

• оптимизация группы по запросу для получения последней записи на пользователя

возвращение более одной колонки, a LATERAL join обычно проще, чище и быстрее. Кроме того, помните, что эквивалент коррелированного подзапросLEFT JOIN LATERAL ... ON true:

• вызовите функцию set-returning с аргументом массива несколько раз

читать инструкции для LATERAL

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

• https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-LATERAL
• http://www.postgresql.org/docs/current/static/sql-select.html

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

здесь are вещи, которые a LATERAL join может сделать, но (коррелированный) подзапрос не может (легко). Коррелированный подзапрос может возвращать только одно значение, а не несколько столбцы, а не несколько строк - за исключением голых вызовов функций (которые умножают результирующие строки, если они возвращают несколько строк). Но даже некоторые функции возврата набора разрешены только в FROM предложения. Как новый unnest() С несколькими параметрами в Postgres 9.4. инструкции:

это разрешено только в FROM п.;

так что это работает, но не может быть легко заменен на подзапрос:

CREATE TABLE tbl (a1 int[], a2 int[]);

SELECT *
FROM   tbl t, unnest(t.a1, t.a2) u(elem1, elem2);  -- implicit LATERAL

^{(запятая (,) в FROM предложение-это короткая нотация для CROSS JOIN.
LATERAL предполагается автоматически для табличных функций.)}

подробнее о частном случае UNNEST( array_expression [, ... ] ) при этом позже вопрос о dba.SE:

• как вы объявляете set-returning-function разрешенной только в предложении FROM?

Set-функции возврата в SELECT список

вы также можете использовать функции set-returning, такие как unnest() на SELECT список напрямую. Это раньше демонстрировало удивительное поведение с более чем одним экземпляром в одном и том же SELECT список до Postgres 9.6. но он, наконец, был дезинфицирован с Postgres 10 и является допустимой альтернативой сейчас (даже если не стандартный SQL).
Основываясь на приведенном выше примере:

SELECT *, unnest(t.a1) AS elem1, unnest(t.a2) AS elem2
FROM   tbl t;

для сравнения:

dbfiddle для pg 9.6 здесь
dbfiddle для pg 10 здесь

уточнить дезу

руководство разъясняет вводящую в заблуждение информацию здесь:

на INNER и OUTER типы соединений, условие соединения должно быть указано, а именно ровно один из NATURAL,ONjoin_condition, или USING (join_column [, ...]). См. ниже для значения.
Ибо CROSS JOIN, ни один из этих пунктов не может появиться.

таким образом, эти два запроса действительны (даже если не особенно полезны):

SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t ON TRUE;

SELECT *
FROM   tbl t, LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t;

а это:

SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t;

вот почему @Andomar-х пример кода является правильным (CROSS JOIN не требует условия соединения) и @Аттилыи был недействительный.