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.
