Where can there be an error in the SQL query?

Question

PostgreSQL

select
sum(h.area),
sum(s.area)
from
table1 , table2
left join table1 h on table1.id = h.id 
left join table2 s on table2.id = s.id

Answer 1

The reason is very simple. Formally, comma-style join is an alias for CROSS JOIN. But there is a catch - the priority of comma-style join is lower than the priority of explicit join. And the data source expression, if you arrange the brackets in accordance with the priority, you get this:

from
table1 , ( table2
           left join table1 h on table1.id = h.id
           left join table2 s on table2.id = s.id )

Now it is perfectly clear that inside the bracket nothing is known about the existence somewhere outside of table1 .
That's right - forget FOREVER about the possibility of using a comma. And write like this:

FROM table1 
CROSS JOIN table2
LEFT JOIN table1 h ON table1.id = h.id
LEFT JOIN table2 s ON table2.id = s.id

PS. If, nevertheless, life is not nice without comma-style, you can do what is usually done to explicitly set the priority. That is, add parentheses that specify the priority:

FROM (table1 , table2)
LEFT JOIN table1 h ON table1.id = h.id
LEFT JOIN table2 s ON table2.id = s.id

Now everything is in order - the comma will be executed first, and then the other two tables will LEFT JOIN to the result.