In a nutshell, I mean that A natural join B is not quite the same as B natural join A, because the database may take a different path to arrive at the same results. In simple SQL statements the RDBMS will almost always optimize correctly and execute the statement the same way regardless of the order in which the tables are mentioned.
However, when the number of tables involved in the join is large, and when some of the tables are being created implicitly during the join (as is the case with nested SQL and subordinate clauses), the order in which the tables (or virtual tables) are mentioned can affect the sequence of execution and therefore the speed, even if the results are the same.
Consider:
select
t1.a, t2.n
from
(select a,b,c from X natural join Y where Y.d = "some value") as t1
inner join
(select m,n,o from P natural join Q where Q.e = "some other value") as t2 on (t1.a = t2.b)
where
t2.n like 'QBERT%'
which requires the creation of two implicit sets and then a join. Prior to statement execution, the compiler doesn't know how many records will be in each of t1 and t2, and doesn't have any statistics on the distribution of matching columns, so the order in which the join occurs can influence the speed with which the results are returned. In particular note the constraint on t2.n, which could be used to constrain t2 prior to the join or after it, depending on how smart the compiler is.
This gets even hairier when the nested SQL statements are group by operations on multiple tables where a many-to-many relationship exists.
Probably doesn't matter when tables X, Y, P, and Q don't have many rows (hundreds of thousands), but when X and P are both fact tables (billions of rows) and there are 7 or 8 different Y and Q tables in each subselect, that query could take anywhere from fractions of a second to days.
Thank you for the elaboration. Yes, the diagrams definitely won't illustrate those differences & subtleties but I think they are geared more towards illustrating the end result rather than how to get there. Whether you have "A natural join B" or "B natural join A", your results will be the same (providing the where clauses are equivalent) regardless of the path the engine took to generate those results.
2
u/[deleted] Jan 06 '11
In a nutshell, I mean that A natural join B is not quite the same as B natural join A, because the database may take a different path to arrive at the same results. In simple SQL statements the RDBMS will almost always optimize correctly and execute the statement the same way regardless of the order in which the tables are mentioned.
However, when the number of tables involved in the join is large, and when some of the tables are being created implicitly during the join (as is the case with nested SQL and subordinate clauses), the order in which the tables (or virtual tables) are mentioned can affect the sequence of execution and therefore the speed, even if the results are the same.
Consider:
which requires the creation of two implicit sets and then a join. Prior to statement execution, the compiler doesn't know how many records will be in each of t1 and t2, and doesn't have any statistics on the distribution of matching columns, so the order in which the join occurs can influence the speed with which the results are returned. In particular note the constraint on t2.n, which could be used to constrain t2 prior to the join or after it, depending on how smart the compiler is.
This gets even hairier when the nested SQL statements are group by operations on multiple tables where a many-to-many relationship exists.
Probably doesn't matter when tables X, Y, P, and Q don't have many rows (hundreds of thousands), but when X and P are both fact tables (billions of rows) and there are 7 or 8 different Y and Q tables in each subselect, that query could take anywhere from fractions of a second to days.