MySQL的Join使用

工程師WWW發表於2014-10-25
MySql

在MySQL(以5.1為例)中，表連線的語法可以參見MySQL官方手冊：MySQL官方手冊-JOIN

在查詢中，連線的語法類似

[sql] view plaincopy
  1. SELECT select_expr FROM table_references  

table_references（對錶的引用）的定義如下（也可以看成連線表示式）：（暈暈暈哈）

[sql] view plaincopy
  1. table_references:  
  2.     table_reference [, table_reference] ...  
  3.   
  4. table_reference:  
  5.     table_factor  
  6.   | join_table  
  7.   
  8. table_factor:  
  9.     tbl_name [[AS] alias] [index_hint_list]  
  10.   | table_subquery [AS] alias  
  11.   | ( table_references )  
  12.   | { OJ table_reference LEFT OUTER JOIN table_reference  
  13.         ON conditional_expr }  
  14.   
  15. join_table:  
  16.     table_reference [INNER | CROSS] JOIN table_factor [join_condition]  
  17.   | table_reference STRAIGHT_JOIN table_factor  
  18.   | table_reference STRAIGHT_JOIN table_factor ON conditional_expr  
  19.   | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition  
  20.   | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor  
  21.   
  22. join_condition:  
  23.     ON conditional_expr  
  24.   | USING (column_list)  
  25.   
  26. index_hint_list:  
  27.     index_hint [, index_hint] ...  
  28.   
  29. index_hint:  
  30.     USE {INDEX|KEY}  
  31.       [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])  
  32.   | IGNORE {INDEX|KEY}  
  33.       [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)  
  34.   | FORCE {INDEX|KEY}  
  35.       [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)  
  36.   
  37. index_list:  
  38.     index_name [, index_name] ...  

其中，table_factor是基本的表選擇，而join_table是基於表的一些擴充套件。
下面，通過實驗介紹一下表連線。
首先，假設有以下幾個表

table1
id book
1 java
2 c++
3 php

table2
id author
2 zhang
3 wang
4 li

table3
author year
zhang 2003
ma 2006
liu 2011

Inner Join 內連線
將兩個表中存在連線關係的欄位，組成的記錄集，叫做內連線。
內連線等價於

[sql] view plaincopy
  1. mysql> select table1.id as id,book,author from table1, table2 where table1.id=table2.id;  
  2. +------+------+--------+  
  3. | id   | book | author |  
  4. +------+------+--------+  
  5. |    2 | c++  | zhang  |  
  6. |    3 | php  | wang   |  
  7. +------+------+--------+  
  8. 2 rows in set (0.00 sec)  
  9. mysql> select * from table1 inner join table2 using (id);  
  10. +------+------+--------+  
  11. | id   | book | author |  
  12. +------+------+--------+  
  13. |    2 | c++  | zhang  |  
  14. |    3 | php  | wang   |  
  15. +------+------+--------+  
  16. 2 rows in set (0.00 sec)  

可以看出，兩者是等價的。沒有Using子句的Inner Join相當於是求兩個table的笛卡爾積。
Cross Join 交叉連線
在Mysql中，Cross Join可以用逗號表示式表示，例如(table1, table 2)。在Mysql中，Cross Join 和 Inner Join 是等價的，但是在標準SQL中，它們並不等價，Inner Join 用於帶有on表示式的連線，反之用Cross Join。以下兩個SQL語句是等價的。
Cross Join 指的是兩個table的笛卡爾積。以下三句SQL是等價的。

[sql] view plaincopy
  1. mysql> select * from table1 inner join table2;  
  2. mysql> select * from table1 cross join table2;  
  3. mysql> select * from (table1, table2);  
  4. mysql> select * from table1 nature join table2;  
  5. 結果集：  
  6. +------+------+------+--------+  
  7. | id   | book | id   | author |  
  8. +------+------+------+--------+  
  9. |    1 | java |    2 | zhang  |  
  10. |    2 | c++  |    2 | zhang  |  
  11. |    3 | php  |    2 | zhang  |  
  12. |    1 | java |    3 | wang   |  
  13. |    2 | c++  |    3 | wang   |  
  14. |    3 | php  |    3 | wang   |  
  15. |    1 | java |    4 | li     |  
  16. |    2 | c++  |    4 | li     |  
  17. |    3 | php  |    4 | li     |  
  18. +------+------+------+--------+  

不難理解，下面兩句SQL也是等價的。

[sql] view plaincopy
  1. mysql> select * from table1 left join (table2, table3) on (table2.id = table1.id and table2.author = table3.author);  
  2. mysql> select * from table1 left join (table2 cross join table3) on (table2.id = table1.id and table2.author = table3.author);  
  3. 結果集：  
  4. +------+------+------+--------+--------+------+  
  5. | id   | book | id   | author | author | year |  
  6. +------+------+------+--------+--------+------+  
  7. |    1 | java | NULL | NULL   | NULL   | NULL |  
  8. |    2 | c++  |    2 | zhang  | zhang  | 2003 |  
  9. |    3 | php  | NULL | NULL   | NULL   | NULL |  
  10. +------+------+------+--------+--------+------+  

Natural Join 自然連線
NATURAL [LEFT] JOIN：這個句子的作用相當於INNER JOIN，或者是在USING子句中包含了聯結的表中所有公共欄位的Left JOIN（左聯結）。
也就是說：下面兩個SQL是等價的。

[sql] view plaincopy
  1. mysql> select * from table1 natural join table2;  
  2. mysql> select * from table1 inner join table2 using (id);  
  3.   
  4. 結果集：  
  5. +------+------+--------+  
  6. | id   | book | author |  
  7. +------+------+--------+  
  8. |    2 | c++  | zhang  |  
  9. |    3 | php  | wang   |  
  10. +------+------+--------+  

同時，下面兩個SQL也是等價的。

[sql] view plaincopy
  1. mysql> select * from table1 natural left join table2;  
  2. mysql> select * from table1 left join table2 using(id);  
  3. 結果集：  
  4. +------+------+--------+  
  5. | id   | book | author |  
  6. +------+------+--------+  
  7. |    1 | java | NULL   |  
  8. |    2 | c++  | zhang  |  
  9. |    3 | php  | wang   |  
  10. +------+------+--------+  

Left Join 左外連線
左外連線A、B表的意思就是將表A中的全部記錄和表B中欄位連線形成的記錄集，這裡注意的是最後出來的記錄集會包括表A的全部記錄。
左連線表1，表二等價於右連線表二，表一。如下兩個SQL是等價的：
[sql] view plaincopy
  1. mysql> select * from table1 left join table2 using (id);  
  2. mysql> select * from table2 right join table1 using (id);  
  3. 結果集：  
  4. +------+------+--------+  
  5. | id   | book | author |  
  6. +------+------+--------+  
  7. |    1 | java | NULL   |  
  8. |    2 | c++  | zhang  |  
  9. |    3 | php  | wang   |  
  10. +------+------+--------+  

Right Join 右外連線

右外連線和左外連線是類似的。為了方便資料庫便於訪問，推薦使用左外連線代替右外連線。


最後，講一下Mysql表連線的一些注意事項。

1、兩個表求差集的方法
如果求 左表 - 右表 的差集，使用類似下面的SQL：
[sql] view plaincopy
  1. SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL;  
  2. 例如  
  3. mysql> select table1.* from table1 left join table2 using(id) where table2.id is null;  
  4. +------+------+  
  5. | id   | book |  
  6. +------+------+  
  7. |    1 | java |  
  8. +------+------+  
  9. 1 row in set (0.00 sec)  

2、Using子句
Using子句可以使用On子句重寫。但是使用Select *　查詢出的結果有差別。以下兩句話是等價的：
[sql] view plaincopy
  1. mysql> select id, book, author from table1 join table2 using (id);  
  2. mysql> select table1.id, book, author from table1 join table2 on table1.id=table2.id;  
  3. 結果集：  
  4. +------+------+--------+  
  5. | id   | book | author |  
  6. +------+------+--------+  
  7. |    2 | c++  | zhang  |  
  8. |    3 | php  | wang   |  
  9. +------+------+--------+  

但是下面兩個有些許不同，使用on時候，重複的部分會被輸出兩次。
[sql] view plaincopy
  1. mysql> select * from table1 join table2 using (id);  
  2. +------+------+--------+  
  3. | id   | book | author |  
  4. +------+------+--------+  
  5. |    2 | c++  | zhang  |  
  6. |    3 | php  | wang   |  
  7. +------+------+--------+  
  8. 2 rows in set (0.00 sec)  
  9. mysql> select * from table1 join table2 on table1.id=table2.id;  
  10. +------+------+------+--------+  
  11. | id   | book | id   | author |  
  12. +------+------+------+--------+  
  13. |    2 | c++  |    2 | zhang  |  
  14. |    3 | php  |    3 | wang   |  
  15. +------+------+------+--------+  
  16. 2 rows in set (0.00 sec)  

3、Straight Join的使用
STRAIGHT_JOIN 和 JOIN相似，除了大部分情況下，在使用STRAIGHT_JOIN時候，先讀右表後讀左表。而在大部分情況下是先讀左表的。STRAIGHT_JOIN僅用於少數情況下的表連線效能優化，比如右表記錄數目明顯少於左表。

4、Mysql表連線的運算順序
在MySQL 5.1版本中，INNER JOIN, CROSS JOIN, LEFT JOIN, 和RIGHT JOIN 比逗號表示式具有更高的優先順序。
因此SQL1被解析成SQL3，而不是SQL2。
[sql] view plaincopy
  1. SQL1 :　SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);  
  2. SQL2 :　SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);  
  3. SQL3 :　SELECT * FROM t1, (t2 JOIN t3 ON (t1.i1 = t3.i3));  

因此會報錯，找不到i1列。因此以後在寫這樣的查詢的時候，最好寫明白，不要省略括號，這樣能避免很多錯誤。

5、迴圈的自然連線
在MySQL 5.1版本中，SQL1等價於SQL3， 而在MySQL以前版本中，SQL1等價於SQL2。
[sql] view plaincopy
  1. SQL1 : SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;  
  2. SQL2 : SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c;  
  3. SQL3 : SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;  

相關文章