LATERAL JOIN vs. Subqueries in PostgreSQL: When Should You Use Which?

LATERAL JOIN vs. subquery in PostgreSQL: when to use which?

Background

The emergence of LATERAL JOIN in PostgreSQL provides a potential solution to complex data dump problems involving inefficient subqueries. This article aims to clarify the situation by exploring the use cases and differences of LATERAL JOIN and subqueries.

What is LATERAL JOIN?

LATERAL JOIN, introduced in PostgreSQL 9.3, allows subqueries or table functions to appear in a FROM clause, enabling them to reference columns from previous FROM entries. Unlike ordinary subqueries that are executed only once, LATERAL JOIN evaluates the expression on the right side for each row of the left join, similar to a correlated subquery.

versus correlated subquery

LATERAL JOIN is similar to a correlated subquery, but has some key differences. It's worth noting that LATERAL JOIN can return multiple columns and rows, while correlated subqueries are usually limited to a single value. Additionally, in LATERAL JOIN syntax, the equivalent of a correlated subquery is LEFT JOIN LATERAL ... ON true.

Beyond subquery functionality

LATERAL JOIN provides functions that are difficult to achieve with subqueries. For example, they allow the use of table functions, cross-referencing multiple FROM entries, and returning multiple columns from multiple rows. They can also take advantage of functions that return collections for more efficient operations.

SELECT return collection function in the list

Collection-returning functions like unnest() can be used directly in SELECT lists. While previously problematic, this has been optimized since PostgreSQL 10, providing an alternative to LATERAL JOIN for returning multiple rows or columns. However, it is important to note that in this case, missing rows in the result eliminate the entire row, unlike LATERAL JOIN.

The above is the detailed content of LATERAL JOIN vs. Subqueries in PostgreSQL: When Should You Use Which?. For more information, please follow other related articles on the PHP Chinese website!