12.4-----left join 分析

标题   left join 分析     选择自 libkhorse 的 Blog 关键字   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.
然后继续读下一条。

 

 

引申：

我们可以用右表没有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)
    ->          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