语法如下:table_references:
table_reference [, table_reference] …table_reference:
table_factor
| join_tabletable_factor:
tbl_name [[AS] alias]
[{USE|IGNORE|FORCE} INDEX (key_list)]
| ( table_references )
| { OJ table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON condition
| table_reference LEFT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
| table_reference RIGHT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [RIGHT [OUTER]] JOIN table_factorjoin_condition:
ON conditional_expr
| USING (column_list)table_reference STRAIGHT_JOIN table_factor
这个可以强制链接顺序,但是他是JOIN的,如果是LEFT JOIN或RIGHT JOIN呢??怎么样强制某个表在前面??
table_reference [, table_reference] …table_reference:
table_factor
| join_tabletable_factor:
tbl_name [[AS] alias]
[{USE|IGNORE|FORCE} INDEX (key_list)]
| ( table_references )
| { OJ table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON condition
| table_reference LEFT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
| table_reference RIGHT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [RIGHT [OUTER]] JOIN table_factorjoin_condition:
ON conditional_expr
| USING (column_list)table_reference STRAIGHT_JOIN table_factor
这个可以强制链接顺序,但是他是JOIN的,如果是LEFT JOIN或RIGHT JOIN呢??怎么样强制某个表在前面??
SELECT * FROM a a1
STRAIGHT_JOIN b b1 ON a1.id=b1.id
LEFT JOIN a3 a2 ON a2.id=a2.id
联接优化器计算表应联接的顺序。LEFT JOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地工作,因为检查的表交换更少。请注意这说明如果执行下面类型的查询,MySQL进行全扫描b,因为LEFT JOIN强制它在d之前读取:SELECT *
FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
mysql> explain select * from t left join book on t.a=book.id;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | book | eq_ref | PRIMARY | PRIMARY | 4 | mybook.t.a | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
2 rows in set (0.00 sec)mysql> explain select * from book right join t on t.a=book.id;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | book | eq_ref | PRIMARY | PRIMARY | 4 | mybook.t.a | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
2 rows in set (0.00 sec)mysql>
怎么样写,才能够让他先 扫描BOOK表。就这个问题
r.regionName, c.cityName, d.departmentName
FROM
m_city c LEFT JOIN m_region r ON c.regionId = r.id,
m_department d
WHERE c.id = d.cityId
ORDER BY r.regionName, c.cityName, d.departmentName
r和c的顺序不可能改变,但是dc的顺序可以改变
SELECT STRAIGHT_JOIN
r.regionName, c.cityName, d.departmentName
FROM
m_department d,
m_city c LEFT JOIN m_region r ON c.regionId = r.id
WHERE c.id = d.cityId
ORDER BY r.regionName, c.cityName, d.departmentName
INNER JOIN,这个时候,先左还是先右,无所谓2 左边口袋的球,如果找得到右边口袋里面一样颜色的话,那么把两个球都放上去;如果找不到,就单独把左边口袋的球,放到右边架子上面去
LEFT JOIN,这个时候,你是先找左,还是先找右?