LATERAL Keyword

The "LATERAL" keyword is used in queries to reference the results of previous expressions. It can be used with subqueries or functions and allows you to use the results of earlier expressions as inputs for subsequent expressions in the query.

The roles of the "LATERAL" keyword are:

Dependent correlated subqueries: Using the "LATERAL" keyword, you can reference earlier expressions in the query, creating dependent correlated subqueries. This means you can access the results of earlier expressions within a subquery and use that result as input for subsequent expressions in the query.
Dynamic column calculation: "LATERAL" can be used for dynamically calculating column values. For example, you can use "LATERAL" in a query to calculate column values for each row, where the calculations depend on the results of earlier expressions or data from other tables.
Reducing query execution: By using the "LATERAL" keyword, you can avoid executing repetitive subqueries multiple times. Subqueries can reference the results of expressions in the outer query, reducing the number of query executions during query processing and improving performance.

Uqbar=# CREATE TABLE t_product AS
    SELECT   id AS product_id,
             id * 10 * random() AS price,
             'product ' || id AS product
    FROM generate_series(1, 1000) AS id;
 
Uqbar=# CREATE TABLE t_wishlist
(
    wishlist_id        int,
    username           text,
    desired_price      numeric
);
 
Uqbar=# INSERT INTO t_wishlist VALUES
    (1, 'hans', '450'),
    (2, 'joe', '60'),
    (3, 'jane', '1500');

Uqbar=# SELECT        *
FROM      t_wishlist AS w,
    LATERAL  (SELECT      *
        FROM       t_product AS p
        WHERE       p.price < w.desired_price
        ORDER BY p.price DESC
        LIMIT 3
       ) AS x
ORDER BY wishlist_id, price DESC;Uqbar-# Uqbar-# Uqbar(# Uqbar(# Uqbar(# Uqbar(# Uqbar(# Uqbar-#
 wishlist_id | username | desired_price | product_id |      price       |   product
-------------+----------+---------------+------------+------------------+-------------
           1 | hans     |           450 |        268 | 447.912638001144 | product 268
           1 | hans     |           450 |        445 |  440.73279609438 | product 445
           1 | hans     |           450 |         90 | 436.825405899435 | product 90
           2 | joe      |            60 |        122 | 57.7194433659315 | product 122
           2 | joe      |            60 |         50 | 55.5128382984549 | product 50
           2 | joe      |            60 |          9 | 55.1704007294029 | product 9
           3 | jane     |          1500 |        168 | 1498.51569615304 | product 168
           3 | jane     |          1500 |        774 | 1496.66411731392 | product 774
           3 | jane     |          1500 |        420 | 1494.14442060515 | product 420
(9 rows)