Re: Problem with LATERAL

On Thu, Oct 13, 2022 at 08:04:03AM +0000, Eagna wrote:
>
> > > ERROR: syntax error at or near "WHERE"
> > > LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0);
>
>
> > There error here is because a JOIN clause requires a join condition. Adding an
> > "ON true" is probably what you want. You would also need to change isnull()
> > with coalesce().
>
> > The final query should be:
>
> ...
> ...
> ...
> > ) AS sub ON true
> ...
> ...
>
> OK - I see that it works now - which is great!
>
> However, it's unclear to me what, exactly, it is that is "TRUE"?
>
> What am I joining to what?
>
> The syntax is unclear to me - if I'm joining, I should be joining on tab_A.field_x = tab_B.field_y - no?

Well, yes but the join condition in that case is already in the WHERE clause in
the sub select, so trying to put an actual join clause would be unnecessary and
add extra cost.

But I'm not sure why you want a LATERAL clause in the first place, wouldn't
this query have the same meaning?

SELECT o.order_id,
o.total_price - coalesce(sum(p.amount), 0)
FROM _order o
LEFT JOIN payment p ON p.order_id = o.order_id
GROUP BY o.order_id, o.total_price
HAVING o.total_price > coalesce(sum(p.amount), 0);

It should perform better if you have a lot of orders, as it can be executed
with something better than a nested loop.

> Why does SQL Server's OUTER APPLY not require this?

I don't know much about sql server, I'm assuming CROSS APPLY is an alias for
LEFT JOIN LATERAL () ON TRUE.