I noticed I wrote a query like this, the day after I wrote it:
SELECT
T1.col1,
T2.col1,
T3.col1
FROM our_schema.table1 T1
INNER JOIN our_schema.table2 T2
LEFT JOIN our_schema.table3 T3 ON (T2.id1 = T3.id1)
ON( (T1.id1 = T2.id1)
ORDER BY T1.col1
It actually ran, despite the "ON" for the first JOIN being after the LEFT JOIN.
I really meant to run it as:
SELECT
T1.col1,
T2.col1,
T3.col1
FROM our_schema.table1 T1
INNER JOIN our_schema.table2 T2 ON( (T1.id1 = T2.id1)
LEFT JOIN our_schema.table3 T3 ON (T2.id1 = T3.id1)
ORDER BY T1.col1
Note: The difference is where the ON clause is placed.
Surprisingly, the corrected version also runs but gives different results.
I would assume that the second query is the 'normal' way to do joins in PostgreSQL.
Can anyone explain what is going on in the first query and what is the difference between the two. I'm surprised the first query didn't cause a compiler error.
