PostgreSQL supports the SQL join type: LATERAL. Useful for creating advanced query’s.
What is a LATERAL join?
In the PostgreSQL documentation:
Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.)
Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional; the function’s arguments can contain references to columns provided by preceding FROM items in any case.
A LATERAL item can appear at top level in the FROM list, or within a JOIN tree. In the latter case it can also refer to any items that are on the left-hand side of a JOIN that it is on the right-hand side of.
When a FROM item contains LATERAL cross-references, evaluation proceeds as follows: for each row of the FROM item providing the cross-referenced column(s), or set of rows of multiple FROM items providing the columns, the LATERAL item is evaluated using that row or row set’s values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s).
Example
We have a table with orders and want to join a related table orderlines to find out the lowest and highest status of the order lines.
SELECT orders.orderid, statuslow, statushigh
FROM orders
JOIN LATERAL
(
SELECT orderlinestatus.name AS statuslow, orderlines.orderid
FROM orderlines JOIN orderlinestatus USING(orderlinestatusid) WHERE orderlines.orderid = orders.orderid
GROUP BY orderlines.orderid, orderlinestatus.name, orderlinestatus.weight
ORDER BY orderlinestatus.weight LIMIT 1
)
AS subquery_statuslow ON (subquery_statuslow.orderid=orders.orderid),
JOIN LATERAL
(
SELECT orderlinestatus.name AS statushigh, orderlines.orderid
FROM orderlines JOIN orderlinestatus USING(orderlinestatusid) WHERE orderlines.orderid = orders.orderid
GROUP BY orderlines.orderid, orderlinestatus.name, orderlinestatus.weight
ORDER BY orderlinestatus.weight DESC LIMIT 1
)
AS subquery_statushigh ON (subquery_statushigh.orderid=orders.orderid)
Notice that with the LATERAL join keyword we can reference the orders table in a subselect query which is not possible with standard joins. Depending on the use case this can increase performance. Otherwise we would have need to join the orders and orderlines table and do the aggregation at the end. For large tables this requires a lot of temporarily memory. With the lateral join the work data is reduced to one row in the subselect query.
More information on the PostgreSQL documentation website: https://www.postgresql.org/docs/9.3/static/queries-table-expressions.html