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: result: 分析:注意到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. 然后继续读下一条。 引申:我们可以用右表没有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) 以下为mysql官方关于join的工作原理及注意事项的说明5.2.6 How MySQL Optimises LEFT JOIN and RIGHT JOIN
The table B is set to be dependent on table A and all tables that A is dependent on. 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) 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) |
MYSQl left join 分析
最新推荐文章于 2024-10-09 22:03:37 发布