MySQL(以5.1為例)中,表連接配接的文法可以參見MySQL官方手冊:
Join 連接配接
在查詢中,連接配接的文法類似
SELECTselect_exprFROMtable_references
table_references(對表的引用)的定義如下(也可以看成連接配接表達式):
table_references:
table_reference [, table_reference] ...
table_reference:
table_factor
| join_table
table_factor:
tbl_name [[AS] alias] [index_hint_list]
| table_subquery [AS] alias
| ( table_references )
| { OJ table_reference LEFTOUTERJOINtable_reference
ONconditional_expr }
join_table:
table_reference [INNER|CROSS]JOINtable_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ONconditional_expr
| table_reference {LEFT|RIGHT} [OUTER]JOINtable_reference join_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]]JOINtable_factor
join_condition:
ONconditional_expr
| USING (column_list)
index_hint_list:
index_hint [, index_hint] ...
index_hint:
USE {INDEX|KEY}
[{FOR{JOIN|ORDERBY|GROUPBY}] ([index_list])
| IGNORE{INDEX|KEY}
[{FOR{JOIN|ORDERBY|GROUPBY}] (index_list)
| FORCE{INDEX|KEY}
[{FOR{JOIN|ORDERBY|GROUPBY}] (index_list)
index_list:
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 内連接配接
将兩個表中存在連接配接關系的字段,組成的記錄集,叫做内連接配接。
内連接配接等價于
mysql>selecttable1.idasid,book,authorfromtable1, table2wheretable1.id=table2.id;
+------+------+--------+
| id | book | author |
+------+------+--------+
| 2 | c++ | zhang |
| 3 | php | wang |
+------+------+--------+
2 rowsinset(0.00 sec)
mysql> select*fromtable1innerjointable2 using (id);
+------+------+--------+
| id | book | author |
+------+------+--------+
| 2 | c++ | zhang |
| 3 | php | wang |
+------+------+--------+
2 rowsinset(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是等價的。
mysql>select*fromtable1innerjointable2;
mysql> select*fromtable1crossjointable2;
mysql> select*from(table1, table2);
mysql> select*fromtable1 naturejointable2;
結果集:
+------+------+------+--------+
| id | book | id | author |
+------+------+------+--------+
| 1 | java | 2 | zhang |
| 2 | c++ | 2 | zhang |
| 3 | php | 2 | zhang |
| 1 | java | 3 | wang |
| 2 | c++ | 3 | wang |
| 3 | php | 3 | wang |
| 1 | java | 4 | li |
| 2 | c++ | 4 | li |
| 3 | php | 4 | li |
+------+------+------+--------+
不難了解,下面兩句SQL也是等價的。
mysql>select*fromtable1leftjoin(table2, table3)on(table2.id = table1.idandtable2.author = table3.author);
mysql> select*fromtable1leftjoin(table2crossjointable3)on(table2.id = table1.idandtable2.author = table3.author);
結果集:
+------+------+------+--------+--------+------+
| id | book | id | author | author | year|
+------+------+------+--------+--------+------+
| 1 | java | NULL|NULL|NULL|NULL|
| 2 | c++ | 2 | zhang | zhang | 2003 |
| 3 | php | NULL|NULL|NULL|NULL|
+------+------+------+--------+--------+------+
Natural Join 自然連接配接
NATURAL [LEFT] JOIN:這個句子的作用相當于INNER JOIN,或者是在USING子句中包含了聯結的表中所有公共字段的Left JOIN(左聯結)。
也就是說:下面兩個SQL是等價的。
mysql>select*fromtable1 naturaljointable2;
mysql> select*fromtable1innerjointable2 using (id);
結果集:
+------+------+--------+
| id | book | author |
+------+------+--------+
| 2 | c++ | zhang |
| 3 | php | wang |
+------+------+--------+
同時,下面兩個SQL也是等價的。
mysql>select*fromtable1 naturalleftjointable2;
mysql> select*fromtable1leftjointable2 using(id);
結果集:
+------+------+--------+
| id | book | author |
+------+------+--------+
| 1 | java | NULL|
| 2 | c++ | zhang |
| 3 | php | wang |
+------+------+--------+
Left Join 左外連接配接
左外連接配接A、B表的意思就是将表A中的全部記錄和表B中字段連接配接形成的記錄集,這裡注意的是最後出來的記錄集會包括表A的全部記錄。
左連接配接表1,表二等價于右連接配接表二,表一。如下兩個SQL是等價的:
mysql>select*fromtable1leftjointable2 using (id);
mysql> select*fromtable2rightjointable1 using (id);
結果集:
+------+------+--------+
| id | book | author |
+------+------+--------+
| 1 | java | NULL|
| 2 | c++ | zhang |
| 3 | php | wang |
+------+------+--------+
Right Join 右外連接配接
右外連接配接和左外連接配接是類似的。為了友善資料庫便于通路,推薦使用左外連接配接代替右外連接配接。
最後,講一下Mysql表連接配接的一些注意事項。
1、兩個表求差集的方法
如果求 左表 - 右表 的差集,使用類似下面的SQL:
SELECTleft_tbl.*FROMleft_tblLEFTJOINright_tblONleft_tbl.id = right_tbl.idWHEREright_tbl.idISNULL;
例如
mysql> selecttable1.*fromtable1leftjointable2 using(id)wheretable2.idisnull;
+------+------+
| id | book |
+------+------+
| 1 | java |
+------+------+
1 row inset(0.00 sec)
2、Using子句
Using子句可以使用On子句重寫。但是使用Select * 查詢出的結果有差别。以下兩句話是等價的:
mysql>selectid, book, authorfromtable1jointable2 using (id);
mysql> selecttable1.id, book, authorfromtable1jointable2ontable1.id=table2.id;
結果集:
+------+------+--------+
| id | book | author |
+------+------+--------+
| 2 | c++ | zhang |
| 3 | php | wang |
+------+------+--------+
但是下面兩個有些許不同,使用on時候,重複的部分會被輸出兩次。
mysql>select*fromtable1jointable2 using (id);
+------+------+--------+
| id | book | author |
+------+------+--------+
| 2 | c++ | zhang |
| 3 | php | wang |
+------+------+--------+
2 rowsinset(0.00 sec)
mysql> select*fromtable1jointable2ontable1.id=table2.id;
+------+------+------+--------+
| id | book | id | author |
+------+------+------+--------+
| 2 | c++ | 2 | zhang |
| 3 | php | 3 | wang |
+------+------+------+--------+
2 rowsinset(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
SQL1 :SELECT*FROMt1, t2JOINt3ON(t1.i1 = t3.i3);
SQL2 : SELECT*FROM(t1, t2)JOINt3ON(t1.i1 = t3.i3);
SQL3 : SELECT*FROMt1, (t2JOINt3ON(t1.i1 = t3.i3));
是以會報錯,找不到i1列。是以以後在寫這樣的查詢的時候,最好寫明白,不要省略括号,這樣能避免很多錯誤。
5、循環的自然連接配接
在MySQL 5.1版本中,SQL1等價于SQL3, 而在MySQL以前版本中,SQL1等價于SQL2
SQL1 :SELECT...FROMt1 NATURALJOINt2 NATURALJOINt3;
SQL2 : SELECT...FROMt1, t2, t3WHEREt1.b = t2.bANDt2.c = t3.c;
SQL3 : SELECT...FROMt1, t2, t3WHEREt1.b = t2.bANDt2.c = t3.cANDt1.a = t3.a;