Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL’s New LATERAL Join Type

blog.heapanalytics.com

Post reply on HN
Print view View original

11–20 of 42 posts

Re: PostgreSQL’s New LATERAL Join Type

#11

Post by tome »

bchallenor wrote: post #10
tome wrote: post #8

Earlier quoted context omitted.

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

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…

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

Re: PostgreSQL’s New LATERAL Join Type

#12

Post by MrBuddyCasino »

As someone who only works now and then with Postgres, this is what made it click for me:

"Loosely, it means that a LATERAL join is like a SQL foreach loop, in which PostgreSQL will iterate over each row in a result set and evaluate a subquery using that row as a parameter."

Re: PostgreSQL’s New LATERAL Join Type

#14

Post by ams6110 »

MrBuddyCasino wrote: post #12

As someone who only works now and then with Postgres, this is what made it click for me: "Loosely, it means that a LATERAL join is like a SQL foreach loop, in which PostgreSQL will iterate over each row in a result set and evaluate a subquery using that row as a parameter."

Sounds like what Oracle calls a "correlated subquery" is it the same thing?

Re: PostgreSQL’s New LATERAL Join Type

#15

Post by nunwuo »

bchallenor wrote: post #10
tome wrote: post #8

Earlier quoted context omitted.

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

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…

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.

Re: PostgreSQL’s New LATERAL Join Type

#16

Post by pjungwir »

ams6110 wrote: post #14
MrBuddyCasino wrote: post #12

As someone who only works now and then with Postgres, this is what made it click for me: "Loosely, it means that a LATERAL join is like a SQL foreach loop, in which PostgreSQL will iterate over each row in a result set and evaluate a subquery using that row as a parameter."

Sounds like what Oracle calls a "correlated subquery" is it the same thing?

No, Postgres has supported those for a long time. For example:

    SELECT *
    FROM   employees e
    WHERE  EXISTS (SELECT 1
                   FROM   employee_projects ep
                   WHERE  ep.employee_id = e.id
                   AND    ep.project_id = 5)
    ;
This new feature is a lot like correlated subqueries except you can put that nested SELECT into the FROM clause and still access the employees table.

Re: PostgreSQL’s New LATERAL Join Type

#17

Post by bchallenor »

nunwuo wrote: post #15
bchallenor wrote: post #10

Earlier quoted context omitted.

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…

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.

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.

Re: PostgreSQL’s New LATERAL Join Type

#18

Post by nunwuo »

bchallenor wrote: post #17
nunwuo wrote: post #15

Earlier quoted context omitted.

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.

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.

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.
Post reply on HN