[sehrope]

LATERAL is awesome. It makes a lot of queries that required sub-select joins much simpler to write and later read.

It's also great for set returning functions. Even cooler, you don't need to explicitly specify the LATERAL keyword. The query planner will add it for you automatically:

    -- NOTE: WITH clause is just to fake a table with data:
    WITH foo AS (
      SELECT 'a' AS name
           , 2 AS quantity
      UNION ALL
      SELECT 'b' AS name
           , 4 AS quantity)
    SELECT t.*
         , x
    FROM foo t
      -- No need to say "LATERAL" here as it's added automatically
      , generate_series(1,quantity) x;
    
    name | quantity | x
    ------+----------+---
    a    |        2 | 1
    a    |        2 | 2
    b    |        4 | 1
    b    |        4 | 2
    b    |        4 | 3
    b    |        4 | 4
    (6 rows)

[bchallenor]

Thanks for the example. It seems to be the same as MS SQL Server's CROSS APPLY / OUTER APPLY?

Anyway, good that Postgres has it too, now. There are several Postgres features I'd love in SQL Server, like range types...

[tracker1]

Usually when I've had to use cross/outer apply it's been to work around overly normalized, and somewhat bad data.

Agreed on range types.. proper enums in T-SQL would be nice too. I'm really liking where PL/v8 is going, and would like to see something similar in MS-SQL server as well.. the .Net extensions are just too much of a pain to do much with. It's be nice to have a syntax that makes working with custom data types, or even just JSON and XML easier.

If PostgreSQL adds built-in replication to the Open-Source version that isn't a hacky add-on, and has failover similar to, for example MongoDB's replica sets, I'm so pushing pg for most new projects.

Maria/MySQL seem to be getting interesting as well. Honestly, I like MS-SQL until the cost of running it gets a little wonky (Azure pricing going from a single instance to anything that can have replication for example). Some of Amazon's offerings are really getting compelling here.

[tome]

Am I right in thinking that this does not increase Postgres's expressive power but allows more concise implementation?

[bchallenor]

No, you can write queries that are not really possible to express without it. Basically, it allows you to execute a table-valued function for each row in an earlier query.

For example, in SQL Server I find a common use of CROSS APPLY (which appears to be the same thing) is where the "table-valued function" is a SELECT with a WHERE clause referencing the earlier query, an ORDER BY, and a TOP (=LIMIT) 1. (In fact, this is exactly the example given in the article.) It allows you to do things like "for each row in table A, join the last row in table B where NaturalKey(A) = NaturalKey(B) and Value1(A) is greater than or equal to Value2(B)".

[nunwuo]

That's not true. Anything you can do with LATERAL you can also do with correlated scalar subqueries in the SELECT list. LATERAL simply makes writing these kinds of queries easier and more intuitive.

[bchallenor]

The syntax for this is pretty horrible, however. And if you want to return more than one column from the subquery, you would have to duplicate the subquery definition for each column, right? Then you'd have to have faith that the optimizer can work out what you meant and reconstruct just a single subquery.

[nunwuo]

There's no faith required; the planner is guaranteed not to do that. The "normal" way is to create a composite type containing each of the columns you need, and then "unpack" it to separate columns. Horrible? Yeah, but it's possible.

[bchallenor]

Is it possible with scalar subqueries to perform anything other than a tree of correlation? With CROSS APPLY one can correlate a DAG of subqueries, e.g. a diamond where B and C depend on A, and D depends on B and C.

[ajw0100]

What if the limits on the lateral subqueries were 2 instead of 1, and they were doing select * instead on select sum() in the outer query? How would you recreate that with correlated SCALAR subqueries? There's no such thing as non-scalar correlated subqueries is there?

[nunwuo]

Untested, but this is the general approach:

  SELECT unnest(ar).* FROM
    (SELECT ARRAY(SELECT tbl FROM tbl
                  WHERE .. ORDER BY .. LIMIT 2) AS ar
     FROM .. OFFSET 0) ss;

If you want a specific set of columns instead of *, you'd need to create a custom composite type to create an array of, since it's not possible to "unpack" anonymous records.

[tome]

Thanks, joining to the last row is an instructive example.

[j-kidd]

Looking at the examples from the official documentation, I agree with your sentiment. Indeed, the conciseness can cause some confusion to people familiar with the existing scoping rules.

IMHO, it's a good thing if LATERAL is only added as some kind of syntactic sugar. I once had to use LATERAL in DB2 as a band-aid solution for its broken scoping rules: https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQ...