JOIN LATERAL

 LATERAL

The primary feature of LATERAL JOIN is to enable access elements of a main query in a subquery which can be very powerful.

Several common uses of LATERAL are to:

• denormalize arrays into parent child tables
• aggregation across several tables
• row or action generation.

Note, however, that the subquery will execute for each main query row since the values used in the subquery
will change. This might make a slower query.

USAGE

SELECT <target list>
FROM <table>
JOIN LATERAL
(<subquery using table.column>) as foo;

Here are three examples of using LATERAL.  Obviously there are many more.:

Normalizing

In the Normalization example we have a table (denorm) containing ids and an array of other ids. We want to to flatten the arrays, creating parent and child tables.  This is also a good example of using a function as a subquery.

denorm.sql

Activity log

The Activity log example captures pg_stat_activity item for current command for
auditing, spying or review. There will be lots of garbage collection and room for
further analysis in table slog.

Logging and auditing is usually done by triggers or rules.
In this case we want to grab the pg_stat_activity data
in the middle of the query. The lateral join is implicitly
on pg_backend_pid().

As you will see, the lateral join is not appropriate for UPDATES and
INSERTS. The slog() function can be called in the
FROM clause in those cases.

log.sql

Aggregation

The Aggregation example examines people, books and checkouts. Filling the fields
is amusing, but not the point of explaining LATERAL. At the end we
will look at various uses of LATERAL while executing aggregates.

sumtest.sql

Drop all example tables:

DROP TABLE IF EXISTS denorm CASCADE;
DROP TABLE IF EXISTS parent CASCADE;
DROP TABLE IF EXISTS child;
DROP TABLE IF EXISTS persons CASCADE;
DROP TABLE IF EXISTS books CASCADE;
DROP TABLE IF EXISTS co_books CASCADE;
DROP TABLE IF EXISTS x2018;
DROP TABLE IF EXISTS abc;
DROP TABLE IF EXISTS slog CASCADE;
DROP FUNCTION IF EXISTS slog();

I hope your examination of LATERAL gives you some good ideas.  If you have a subquery that really wants to access the main query LATERAL is your answer.

[email protected]

PS: I’m back! Thanks to all of my former followers of GeneralBits. I hope you will be happy with my 2ndquadrant blogs.