MySQL中LeftJoin⽤法
MySQL中Left Join⽤法
例⼦：
user表:
id name
---------
1 libk
2 zyfon
3 daodao
user_action表:
user_id action
-
--------------
1 jump
1 kick
1 jump
2 run
4 swim
sql:
select id, name, action from user as u
left join user_action a on u.id = a.user_id
result:
id name action
-
-------------------------------
1 libk jump ①
1 libk kick ②
1 libk jump ③
2 zyfon run ④
3 daodao null ⑤
分析：
注意到user_action中还有⼀个user_id=4, action=swim的纪录，但是没有在结果中出现，⽽user表中的id=3, name=daodao的⽤户在user_action中没有相应的纪录，但是却出现在了结果集中因为现在是left join，所有的⼯作以left为准.结果1，2，3，4都是既在左表⼜在右表的纪录，5是只在左表，不在右表的纪录。
结论：
我们可以想象left join 是这样⼯作的从左表读出⼀条，选出所有与on匹配的右表纪录(n条)进⾏连接， 形成n条纪录(包括重复的⾏，如：结果1和结果3)，如果右边没有与on条件匹配的表，那连接的字段都是null.然后继续读下⼀条。
sql left join 多表连接引申：
我们可以⽤右表没有on匹配则显⽰null的规律, 来出所有在左表，不在右表的纪录， 注意⽤来判断的那列必须声明为not null的。
如：
sql:
select id, name, action from user as u
left join user_action a on u.id = a.user_id
where a.user_id is NULL
(注意:1.列值为null应该⽤is null ⽽不能⽤=NULL
2.这⾥a.user_id 列必须声明为 NOT NULL 的)
result:
id name action
--------------------------
3 daodao NULL
Tips:
1. on a.c1 = b.c1 等同于 using(c1)
2. INNER JOIN 和 , (逗号) 在语义上是等同的
3. 当 MySQL 在从⼀个表中检索信息时，你可以提⽰它选择了哪⼀个索引。
如果 EXPLAIN 显⽰ MySQL 使⽤了可能的索引列表中错误的索引，这个特性将是很有⽤的。
通过指定 USE INDEX (key_list)，你可以告诉 MySQL 使⽤可能的索引中最合适的⼀个索引在表中查记录⾏。
可选的⼆选⼀句法 IGNORE INDEX (key_list) 可被⽤于告诉 MySQL 不使⽤特定的索引。
4. ⼀些例⼦：
mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
-> LEFT JOIN table3 ON table2.id=table3.id;
mysql> SELECT * FROM table1 USE INDEX (key1,key2)
mysql> SELECT * FROM table1 USE INDEX (key1,key2)
-> WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
-
> WHERE key1=1 AND key2=2 AND key3=3;
以下为mysql官⽅关于join的⼯作原理及注意事项的说明
5.2.6 How MySQL Optimises LEFT JOIN and RIGHT JOIN
A LEFT JOIN
B in MySQL is implemented as follows:
The table B is set to be dependent on table A and all tables that A is dependent on.
The table A is set to be dependent on all tables (except B) that are used in the LEFT JOIN condition.
All LEFT JOIN conditions are moved to the WHERE clause.
All standard join optimisations are done, with the exception that a table is always read after all tables it is dependent on. If there is a circular dependence then MySQL will issue an error.
All standard WHERE optimisations are done.
If there is a row in A that matches the WHERE clause, but there wasn"
If you use LEFT JOIN to find rows that don't exist in some table and you have the following test: column_name IS NULL in the WHERE part, where column_name is a column that is declared as NOT NULL, then MySQL will stop searching after more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition.
RIGHT JOIN is implemented analogously as LEFT JOIN.
The table read order forced by LEFT JOIN and STRAIGHT JOIN will help the join optimiser (which calculates in which order tables should be joined) to do its work much more quickly, as there are fewer table permutations to check.
Note that the above means that if you do a query of type:
SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key)
WHERE b.key=d.key
MySQL will do a full scan on b as the LEFT JOIN will force it to be read before d.
The fix in this case is to change the query to:
SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key)
WHERE b.key=d.key

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。