Lateral is a relatively new concept for PostgreSQL, introduced in 9.3 version. At the time of writing of this post, there is already
10.1 version is out, but many of us had not even heard about the lateral feature. Basically, it should provide nicer syntax for some
occasions where we had to deal with PL/PGSQL procedures in versions prior to 9.3. But it is not only syntactic sugar and is
being interpreted by query planner in its own way.
The definition of lateral given in the official documentation can seem confusing to many of us, so basically lateral
keyword provides the ability to access columns of the from expression appearing in the same statement before lateral keyword.
In the above pseudo-code, subquery_2 can access each row of subquery_1 and thus use them in the computation of each row.
Let’s look at the simplest example implemented with both subquery and lateral join:
Given that we have the tables articles and users with following structure:
Assuming that we want to select articles, assigned to given user and having given status code, but allowed to use only user’s email, not id:
Explain output shows that in this very simple example lateral approach does not have any advantage over the subquery approach,
neither from point of readability nor from point of performance.
Let’s tackle another, more complex example.
The example dataset represents a table of articles, each containing publication date,
its author’s id. We will use the lateral joins to find the last article that was published by each of the authors.
Our articles table looks like this:
The result should look like this:
Of course, there are could be much more convenient ways to tackle this problem, but for demonstration purposes, we chose this example.
If we want to solve this task using lateral joins, we can approach the problem in the following way:
FROM clause will select all existing users for us.
Then we join articles table, order records by publication date and select first record
Finally, we select title, id, and author_email from resulting dataset
Let’s implement this steps, one-by-one:
Selecting all users:
Selecting corresponding articles:
And finally selecting needed fields:
Lateral is not only meant to be used with joins because its core concept is re-using computed values of the
from a clause in subqueries. Thus it finds its application also in aggregates and custom functions.