What is the difference between LATERAL and a subquery in PostgreSQL

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

User · Answer

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

User · Answer

Database table Having the following blog database table storing the blogs hosted by our platform   And  we have two blogs currently hosted      id created on title url     1 2013-09-30 Vlad Mihalcea s Blog https   vladmihalcea com   2 2017-01-22 Hypersistence https   hypersistence io    Getting our report without using the SQL LATERAL JOIN We need to build a report that extracts the following data from the blog table   the blog id the blog age  in years the date for the next blog anniversary the number of days remaining until the next anniversary   If you re using PostgreSQL  then you have to execute the following SQL query  SELECT   b id as blog id    extract      YEAR FROM age now    b created on      AS age in years    date      created on           extract YEAR FROM age now    b created on     1         interval  1 year      AS next anniversary    date      created on           extract YEAR FROM age now    b created on     1         interval  1 year      - date now    AS days to next anniversary FROM blog b ORDER BY blog id  As you can see  the age in years has to be defined three times because you need it when calculating the next anniversary and days to next anniversary values  And  that s exactly where LATERAL JOIN can help us  Getting the report using the SQL LATERAL JOIN The following relational database systems support the LATERAL JOIN syntax   Oracle since 12c PostgreSQL since 9 3 MySQL since 8 0 14  SQL Server can emulate the LATERAL JOIN using CROSS APPLY and OUTER APPLY  LATERAL JOIN allows us to reuse the age in years value and just pass it further when calculating the next anniversary and days to next anniversary values  The previous query can be rewritten to use the LATERAL JOIN  as follows  SELECT   b id as blog id    age in years    date      created on    age in years   1    interval  1 year      AS next anniversary    date      created on    age in years   1    interval  1 year      - date now    AS days to next anniversary FROM blog b CROSS JOIN LATERAL     SELECT     cast        extract YEAR FROM age now    b created on   AS int       AS age in years   AS t ORDER BY blog id  And  the age in years value can be calculated one and reused for the next anniversary and days to next anniversary computations      blog id age in years next anniversary days to next anniversary     1 7 2021-09-30 295   2 3 2021-01-22 44     Much better  right  The age in years is calculated for every record of the blog table  So  it works like a correlated subquery  but the subquery records are joined with the primary table and  for this reason  we can reference the columns produced by the subquery

User · Answer

What is a LATERAL join  The feature was introduced with PostgreSQL 9 3  Quoting the manual   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   Basic code examples are given there  More like a correlated subquery A LATERAL join is more like a correlated subquery  not a plain subquery  in that expressions to the right of a LATERAL join are evaluated once for each row left of it - 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   Related answer with code examples for both side by side  solving the same problem   Optimize GROUP BY query to retrieve latest row 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  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 unnest   with multiple parameters in Postgres 9 4 or later  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  unnest a1  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  About the special case of UNNEST  array expression              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 such function 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   See   What is the expected behaviour for multiple set-returning functions in SELECT clause   Building on above example  SELECT    unnest a1  AS elem1  unnest a2  AS elem2 FROM   tbl   Comparison  dbfiddle for pg 9 6 here dbfiddle for pg 10 here Clarify misinformation The manual   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 is not  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 not

User · Answer

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

User · Answer

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  lt   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

[sql] What is the difference between LATERAL and a subquery in PostgreSQL?

Examples related to sql

Examples related to postgresql

Examples related to subquery

Examples related to lateral-join