Here is the query in question :
SELECT name, totals1.all_purchases FROM accounts, (SELECT orders.account_id, SUM(total) AS all_purchases FROM orders GROUP BY orders.account_id) totals1 WHERE accounts.id = totals1.account_id AND totals1.all_purchases >= ALL (SELECT totals1.all_purchases FROM totals1);
The error text returned is the following:
ERROR: syntax error at or near ")" LINE 7: ALL (SELECT totals1.all_purchases FROM LATERAL totals1)
I know I can easily solve this problem using a postgres CTE, and that it would be the better approach. But I want to understand why this code behaves this way, and want someone to point out the flaws in my logic. By the Wikipedia definition of a correlated query:
a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query.
because the subquery is correlated with a column of the outer query, it must be re-executed for each row of the result.
My logic is, since a correlated subquery is driven by the outer query, it should execute after the outer query. Therefore this inner query should have no problem recognizing the totals1 table.
For example, this code fails as expected, because when the FROM statement is first executed, the subquery that creates totals1 has not yet been executed.
SELECT name, totals1.all_purchases FROM accounts, totals1 WHERE accounts.id = totals1.account_id AND totals1.all_purchases >= ALL(SELECT totals1.all_purchases FROM (SELECT orders.account_id, SUM(total) AS all_purchases FROM orders GROUP BY orders.account_id) totals1;
I am also aware of the existence of the LATERAL keyword. I don’t think it applies to this situation though, I believe that was meant for correlated subqueries in the outer FROM clause. Case in point I literally used it in every way possible to no avail.
Could this be a possible limitation of PostgreSQL, or is it in the SQL standard altogether ?
PS: here is the schema. You might recognize it from the Udacity’s SQL for data analysts course.