What is the difference between LATERAL and a subqu

Question

Since Postgres came out with the ability to do LATERAL joins, I've been reading up on it, since I currently do complex data dumps for my team with lots of inefficient subqueries that make the overall query take four minutes or more.

I understand that LATERAL joins may be able to help me, but even after reading articles like this one from Heap Analytics, I still don't quite follow.

What is the use case for a LATERAL join? What is the difference between a LATERAL join and a subquery?

Answer 1

More like a correlated subquery

A LATERAL join (Postgres 9.3+) is more like a correlated subquery, not a plain subquery. Like @Andomar pointed out, a function or subquery to the right of a LATERAL join typically has to be evaluated many times - once for each row left of the LATERAL join - just like a correlated subquery - while a plain subquery (table expression) is evaluated once only. (The query planner has ways to optimize performance for either, though.)
This related answer has code examples for both side by side, solving the same problem:

• Optimize GROUP BY query to retrieve latest record per user

For returning more than one column, a LATERAL join is typically simpler, cleaner and faster. Also, remember that the equivalent of a correlated subquery is LEFT JOIN LATERAL ... ON true:

• Call a set-returning function with an array argument multiple times

Read the manual for on LATERAL

It is more authoritative than anything we are going to put into answers here:

• https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-LATERAL
• http://www.postgresql.org/docs/current/static/sql-select.html

Things a subquery can't do

There are things that a LATERAL join can do, but a (correlated) subquery cannot (easily). A correlated subquery can only return a single value, not multiple columns and not multiple rows - with the exception of bare function calls (which multiply result rows if they return multiple rows). But even certain set-returning functions are only allowed in the FROM clause. Like the new unnest() with multiple parameters in Postgres 9.4. The manual:

This is only allowed in the FROM clause;

So this works, but cannot easily be replaced with a subquery:

CREATE TABLE tbl (a1 int[], a2 int[]);

SELECT *
FROM   tbl t, unnest(t.a1, t.a2) u(elem1, elem2);  -- implicit LATERAL

^{(The comma (,) in the FROM clause is short notation for CROSS JOIN.
LATERAL is assumed automatically for table functions.)}

More about the special case of UNNEST( array_expression [, ... ] ) under this later question on dba.SE:

• How do you declare a set-returning-function to only be allowed in the FROM clause?

Set-returning functions in the SELECT list

You can also use set-returning functions like unnest() in the SELECT list directly. This used to exhibit surprising behavior with more than one instance in the same SELECT list up to Postgres 9.6. But it has finally been sanitized with Postgres 10 and is a valid alternative now (even if not standard SQL).
Building on above example:

SELECT *, unnest(t.a1) AS elem1, unnest(t.a2) AS elem2
FROM   tbl t;

Comparison:

dbfiddle for pg 9.6 here
dbfiddle for pg 10 here

Clarify misinformation

The manual clarifies misleading information here:

For the INNER and OUTER join types, a join condition must be specified, namely exactly one of NATURAL, ON join_condition, or USING (join_column [, ...]). See below for the meaning.
For CROSS JOIN, none of these clauses can appear.

So these two queries are valid (even if not particularly useful):

SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t ON TRUE;

SELECT *
FROM   tbl t, LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t;

While this one isn't:

SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t;

That's why @Andomar's code example is correct (the CROSS JOIN does not require a join condition) and @Attila's is was invalid.

Answer 2

The difference between a non-lateral and a lateral join lies in whether you can look to the left hand table's row. For example:

select  *
from    table1 t1
cross join lateral
        (
        select  *
        from    t2
        where   t1.col1 = t2.col1 -- Only allowed because of lateral
        ) sub

This "outward looking" means that the subquery has to be evaluated more than once. After all, t1.col1 can assume many values.

By contrast, the subquery after a non-lateral join can be evaluated once:

select  *
from    table1 t1
cross join
        (
        select  *
        from    t2
        where   t2.col1 = 42 -- No reference to outer query
        ) sub

As is required without lateral, the inner query does not depend in any way on the outer query. A lateral query is an example of a correlated query, because of its relation with rows outside the query itself.

Answer 3

One thing no one has pointed out is that you can use LATERAL queries to apply a user-defined function on every selected row.

For instance:

CREATE OR REPLACE FUNCTION delete_company(companyId varchar(255))
RETURNS void AS $$
    BEGIN
        DELETE FROM company_settings WHERE "company_id"=company_id;
        DELETE FROM users WHERE "company_id"=companyId;
        DELETE FROM companies WHERE id=companyId;
    END; 
$$ LANGUAGE plpgsql;

SELECT * FROM (
    SELECT id, name, created_at FROM companies WHERE created_at < '2018-01-01'
) c, LATERAL delete_company(c.id);

That's the only way I know how to do this sort of thing in PostgreSQL.

Answer 4

First, Lateral and Cross Apply is same thing. Therefore you may also read about Cross Apply. Since it was implemented in SQL Server for ages, you will find more information about it then Lateral.

Second, according to my understanding, there is nothing you can not do using subquery instead of using lateral. But:

Consider following query.

Select A.*
, (Select B.Column1 from B where B.Fk1 = A.PK and Limit 1)
, (Select B.Column2 from B where B.Fk1 = A.PK and Limit 1)
FROM A 

You can use lateral in this condition.

Select A.*
, x.Column1
, x.Column2
FROM A LEFT JOIN LATERAL (
  Select B.Column1,B.Column2,B.Fk1 from B  Limit 1
) x ON X.Fk1 = A.PK

In this query you can not use normal join, due to limit clause. Lateral or Cross Apply can be used when there is not simple join condition.

There are more usages for lateral or cross apply but this is most common one I found.