news <[EMAIL PROTECTED]> wrote on 12/19/2005 11:28:36 AM: > Hi Shawn, > > > It's beginning to build a lot of information in a single message. So I cut > it in parts. > > > > In order to compute the intermediate table, each row on the left side of > > the predicate is evaluated in combination with each and every row from the > > right side of the predicate. If the ON clause is missing, or is always > > true, what results is called a Cartesian product of the two data sets: > > each row of TableA will be paired with every row of TableB. If TableA had > > 5 rows and TableB had 3 rows, the intermediate table will have 15 rows in > > it. > > I tried it on 2 tables. > > (A) has 3 rows > (B) has 2 rows > > select A.*, B.* from A inner join B; > > gives 6 rows. with every row of A combined to each and every row of B . > like you said. > > different joins give different order for the rows. RIGHT JOIN ,STRAIGHT_JOIN > , RIGHT OUTER gave ids 8-7-8-7-8-7 where LEFT, INNER and CROSS gave > 8-8-8-7-7-7 > NATURAL gave only 1 row ID 8 > NATURAL LEFT and LEFT OUTER gave 2 rows 8-7 > NATURAL RIGHT gave 3 rows null-null-8 > > enough > > Based on how you write you request, RIGHT and LEFT are equivalent and give > the same row order and table result > > select A.* , B.* from B right join A on 1=1 > is equivalent as > select A.*, B.* from A left join B on 1=1 > > Sandy >
I have never tried any of the NATURAL joins so your results were very interesting to me. The order in which the rows were joined provides you with some insight about how the engine actually performs the joins (which table is in the outer loop and which is in the inner loop). *warning* You cannot rely on identical joins on the same tables to always return in the same sort order. By definition, the order of any set of results is deterministic only if you specify an ORDER BY clause as part of the query. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
