content_image/804908.jpg content_image/804909.jpg
The syntax of table_factor  is extended in comparison with the SQL Standard. The latter accepts only table_reference , not a list of them inside a pair of parentheses. This is a conservative extension if we consider each comma in a list of table_reference  items as equivalent to an inner join. For example: 

SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) 

Is equivalent to: 

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) 

In MySQL, CROSS JOIN  is syntactically equivalent to INNER JOIN ; they can replace each other. In standard SQL, they are not equivalent. INNER JOIN  is used with an ON  clause; CROSS JOIN  is used otherwise. 

In general, parentheses can be ignored in join expressions containing only inner join operations. Consider this join expression: 

t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ON t1.a=t2.a 

After removing parentheses and grouping operations to the left, that join expression transforms into this expression: 

(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL 

Yet, the two expressions are not equivalent. To see this, suppose that the tables t1 , t2 , and t3  have the following state: 

•Table t1  contains rows (1) , (2) 

•Table t2  contains row (1,101) 

•Table t3  contains row (101) 

In this case, the first expression returns a result set including the rows (1,1,101,101) , (2,NULL,NULL,NULL) , whereas the second expression returns the rows (1,1,101,101) , (2,NULL,NULL,101) : 

mysql> SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ON t1.a=t2.a; 

<img src='content_image/804908.jpg'>

mysql> SELECT * FROM (t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL; 

<img src='content_image/804909.jpg'>

