The order doesn't matter for INNER joins. As long as you change your selects from SELECT * to SELECT a.*, b.*, c.* the queries will return the same results.
But the order matters for (LEFT, RIGHT or FULL) OUTER joins.
Outer joins are not commutative.
Therefore, a LEFT JOIN b is not the same as b LEFT JOIN a. Outer joins are not associative either.
In your examples both (commutativity and associativity) properties are involved:
a LEFT JOIN b
ON b.ab_id = a.ab_id
LEFT JOIN c
ON c.ac_id = a.ac_id
The above query is equivalent to:
a LEFT JOIN c
ON c.ac_id = a.ac_id
LEFT JOIN b
ON b.ab_id = a.ab_id
but the below query is:
a LEFT JOIN b
ON b.ab_id = a.ab_id
LEFT JOIN c
ON c.ac_id = a.ac_id
AND c.bc_id = b.bc_id
Not equivalent to this query:
a LEFT JOIN c
ON c.ac_id = a.ac_id
LEFT JOIN b
ON b.ab_id = a.ab_id
AND b.bc_id = c.bc_id
Another associativity example is as follows. Assume this as (a LEFT JOIN b) LEFT JOIN c:
a LEFT JOIN b
ON b.ab_id = a.ab_id -- AB condition
LEFT JOIN c
ON c.bc_id = b.bc_id -- BC condition
Which is equivalent to a LEFT JOIN (b LEFT JOIN c):
a LEFT JOIN
b LEFT JOIN c
ON c.bc_id = b.bc_id -- BC condition
ON b.ab_id = a.ab_id -- AB condition
In avove code, both ON b.ab_id = a.ab_id and c.bc_id = b.bc_id are equality checks and do not involve NULL comparisons.
You can even have conditions with other operators or more complex ones like this:
ON a.x <= b.x or ON a.x = 7 or ON a.x LIKE b.x or ON (a.x, a.y) = (b.x, b.y)
and the two queries will still be equivalent.
Note: If any of these involved IS NULL or a function that is related to nulls like COALESCE(), for example if the condition was b.ab_id IS NULL, then the two queries will never be equivalent.
Enroll yourself in the best sql courses to learn in-depth about SQL statements, queries and become proficient in SQL.
Back