Link to home
Start Free TrialLog in
Avatar of midfde
midfdeFlag for United States of America

asked on

About JOIN associativity

Which of the three clauses are equivalent please:
(1) FROM a LEFT JOIN b ON x=y LEFT JOIN c ON xx = yy
(2) FROM (a LEFT JOIN b ON x=y) LEFT JOIN c ON xx = yy
(3) FROM a LEFT JOIN (b LEFT JOIN c ON xx = yy) ON x=y
?
Do SQL standards say anything about JOIN associativity?
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

JOIN is associative, so the order/brackets would not matter

now, LEFT JOIN is a different matter, as:
a LEFT JOIN b  
will not give the same results as
b LEFT JOIN a

so, LEFT JOIN is not associative as such

however, your refer to the the bracketing for LEFT JOINs, which is associative.
Avatar of midfde

ASKER

Sorry angellll, it looks like you mean commutativity (a JOIN b) = (b JOIN a), and I am talking about associativity, i.e. is
((a join b) join c ) the same as (a join (b join c))?

So once again:
Is (1) in my question equivalent to (2) and / or (3)?

yes, sorry :) math courses are some time ago ...

i.e. is ((a join b) join c ) the same as (a join (b join c))?
yes, JOIN is associative

and yes, (1) is the same as (2) and (3).
ASKER CERTIFIED SOLUTION
Avatar of midfde
midfde
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial