JOIN LATERAL

We're still working at our blogging website "Tedium." Suppose we want to get each blogger's name and their 2 most recent posts. We did something very similar in the previous section. We joined all posts within 7 days of each blogger's sign-up date.

SELECT *
FROM bloggers b
LEFT JOIN blog_posts p
  ON p.blogger_id = b.id
  AND p.created_at < b.registered_at + CAST('7 days' AS INTERVAL);

We were able to join blog_posts, but do so conditionally with an extra condition in the ON clause. The key here is that we are able to compute the comparison p.created_at < b.registered_at + CAST('7 days' AS INTERVAL);. However, for this problem, what comparison is there to make? If we want the 2 most recent posts, there are no values to give to a < comparison to make that happen. We can usually get the 2 most recent of something with ORDER BY created_at DESC LIMIT 2. However, that won't work on a per-blogger basis here. Therefore, even though it sounds like a similar problem, it will take a different approach.

Let's re-think to a strategy like this: for each blogger, query their posts from the posts table and limit to the most recent 2 with ORDER BY created_at DESC LIMIT 2. Then, put together the results from each subquery into a new table and join it to the bloggers table.

We have seen that correlated subqueries can re-execute a query per row, so it should be able to implement our strategy. However, we have only seen correlated subqueries in SELECT or WHERE clauses, where value expressions are expected. These can only produce single values and cannot help us to produce a table. We can do this with a new join type: JOIN LATERAL. A LATERAL join is very different from the other joins because it takes a correlated subquery. Although we've learned you can put a subquery in a normal JOIN clause, it cannot be correlated, as that will fail with an error.

SELECT
  name,
  p.title,
  p.created_at
FROM bloggers b
JOIN (
  SELECT *
  FROM blog_posts
  WHERE blogger_id = b.id
  ORDER BY created_at DESC
  LIMIT 2
) p ON p.blogger_id = b.id;

error: invalid reference to FROM-clause entry for table "b"

Turning the JOIN into a JOIN LATERAL makes the subquery allowed to be correlated by referencing the outer query.

SELECT
  name,
  email,
  p.title,
  p.created_at
FROM bloggers b
JOIN LATERAL (
  SELECT *
  FROM blog_posts
  WHERE blogger_id = b.id
  ORDER BY created_at DESC
  LIMIT 2
) p ON p.blogger_id = b.id;

Just like with correlated subqueries, which evaluate once per row, you can think of the lateral join subquery as running once per row of the left-hand table. The result of each will get combined into 1 table, which then becomes the right-hand table to be joined to the left.

Non-standard Versions

In standard SQL, the proper keywords are JOIN LATERAL. This is how it is used in Postgres. However, several databases, such as Microsoft SQL and Oracle, use the keywords CROSS APPLY instead.

Efficiency

For similar reasons to correlated subqueries, lateral joins can be very computationally expensive. Be wary when using them for performance-critical applications. Only use them when the number of rows in the left-hand table is small, or if absolutely necessary. Re-write queries another way if possible.