幾種MySQL中的聯(lián)接查詢操作方法總結(jié)

更新時(shí)間：2015年04月13日 17:21:45 作者：楊國棟

這篇文章主要介紹了幾種MySQL中的聯(lián)接查詢操作方法總結(jié),文中包括一些代碼舉例講解,需要的朋友可以參考下

前言

現(xiàn)在系統(tǒng)的各種業(yè)務(wù)是如此的復(fù)雜，數(shù)據(jù)都存在數(shù)據(jù)庫中的各種表中，這個(gè)主鍵啊，那個(gè)外鍵啊，而表與表之間就依靠著這些主鍵和外鍵聯(lián)系在一起。而我們進(jìn)行業(yè)務(wù)操作時(shí)，就需要在多個(gè)表之間，使用sql語句建立起關(guān)系，然后再進(jìn)行各種sql操作。那么在使用sql寫出各種操作時(shí)，如何使用sql語句，將多個(gè)表關(guān)聯(lián)在一起，進(jìn)行業(yè)務(wù)操作呢？而這篇文章，就對(duì)這個(gè)知識(shí)點(diǎn)進(jìn)行總結(jié)。

聯(lián)接查詢是一種常見的數(shù)據(jù)庫操作，即在兩張表（多張表）中進(jìn)行匹配的操作。MySQL數(shù)據(jù)庫支持如下的聯(lián)接查詢：

CROSS JOIN（交叉聯(lián)接）
INNER JOIN（內(nèi)聯(lián)接）
OUTER JOIN（外聯(lián)接）
其它

在進(jìn)行各種聯(lián)接操作時(shí)，一定要回憶一下在《SQL邏輯查詢語句執(zhí)行順序》這篇文章中總結(jié)的SQL邏輯查詢語句執(zhí)行的前三步：

執(zhí)行FROM語句（笛卡爾積）
執(zhí)行ON過濾
添加外部行

每個(gè)聯(lián)接都只發(fā)生在兩個(gè)表之間，即使FROM子句中包含多個(gè)表也是如此。每次聯(lián)接操作也只進(jìn)行邏輯查詢語句的前三步，每次產(chǎn)生一個(gè)虛擬表，這個(gè)虛擬表再依次與FROM子句的下一個(gè)表進(jìn)行聯(lián)接，重復(fù)上述步驟，直到FROM子句中的表都被處理完為止。
前期準(zhǔn)備

1.新建一個(gè)測試數(shù)據(jù)庫TestDB；

create database TestDB;

創(chuàng)建測試表table1和table2；

   CREATE TABLE table1
   (
     customer_id VARCHAR(10) NOT NULL,
     city VARCHAR(10) NOT NULL,
     PRIMARY KEY(customer_id)
   )ENGINE=INNODB DEFAULT CHARSET=UTF8;

   CREATE TABLE table2
   (
     order_id INT NOT NULL auto_increment,
     customer_id VARCHAR(10),
     PRIMARY KEY(order_id)
   )ENGINE=INNODB DEFAULT CHARSET=UTF8;

插入測試數(shù)據(jù)；

   INSERT INTO table1(customer_id,city) VALUES('163','hangzhou');
   INSERT INTO table1(customer_id,city) VALUES('9you','shanghai');
   INSERT INTO table1(customer_id,city) VALUES('tx','hangzhou');
   INSERT INTO table1(customer_id,city) VALUES('baidu','hangzhou');

   INSERT INTO table2(customer_id) VALUES('163');
   INSERT INTO table2(customer_id) VALUES('163');
   INSERT INTO table2(customer_id) VALUES('9you');
   INSERT INTO table2(customer_id) VALUES('9you');
   INSERT INTO table2(customer_id) VALUES('9you');
   INSERT INTO table2(customer_id) VALUES('tx');

準(zhǔn)備工作做完以后，table1和table2看起來應(yīng)該像下面這樣：

   mysql> select * from table1;
   +-------------+----------+
   | customer_id | city   |
   +-------------+----------+
   | 163     | hangzhou |
   | 9you    | shanghai |
   | baidu    | hangzhou |
   | tx     | hangzhou |
   +-------------+----------+
   4 rows in set (0.00 sec)

   mysql> select * from table2;
   +----------+-------------+
   | order_id | customer_id |
   +----------+-------------+
   |    1 | 163     |
   |    2 | 163     |
   |    3 | 9you    |
   |    4 | 9you    |
   |    5 | 9you    |
   |    6 | tx     |
   +----------+-------------+
   7 rows in set (0.00 sec)

準(zhǔn)備工作做的差不多了，開始今天的總結(jié)吧。
CROSS JOIN聯(lián)接（交叉聯(lián)接）

CROSS JOIN對(duì)兩個(gè)表執(zhí)行FROM語句（笛卡爾積）操作，返回兩個(gè)表中所有列的組合。如果左表有m行數(shù)據(jù)，右表有n行數(shù)據(jù)，則執(zhí)行CROSS JOIN將返回m*n行數(shù)據(jù)。CROSS JOIN只執(zhí)行SQL邏輯查詢語句執(zhí)行的前三步中的第一步。

CROSS JOIN可以干什么？由于CROSS JOIN只執(zhí)行笛卡爾積操作，并不會(huì)進(jìn)行過濾，所以，我們在實(shí)際中，可以使用CROSS JOIN生成大量的測試數(shù)據(jù)。

對(duì)上述測試數(shù)據(jù)，使用以下查詢：

select * from table1 cross join table2;

就會(huì)得到以下結(jié)果：

+-------------+----------+----------+-------------+
| customer_id | city   | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163     | hangzhou |    1 | 163     |
| 9you    | shanghai |    1 | 163     |
| baidu    | hangzhou |    1 | 163     |
| tx     | hangzhou |    1 | 163     |
| 163     | hangzhou |    2 | 163     |
| 9you    | shanghai |    2 | 163     |
| baidu    | hangzhou |    2 | 163     |
| tx     | hangzhou |    2 | 163     |
| 163     | hangzhou |    3 | 9you    |
| 9you    | shanghai |    3 | 9you    |
| baidu    | hangzhou |    3 | 9you    |
| tx     | hangzhou |    3 | 9you    |
| 163     | hangzhou |    4 | 9you    |
| 9you    | shanghai |    4 | 9you    |
| baidu    | hangzhou |    4 | 9you    |
| tx     | hangzhou |    4 | 9you    |
| 163     | hangzhou |    5 | 9you    |
| 9you    | shanghai |    5 | 9you    |
| baidu    | hangzhou |    5 | 9you    |
| tx     | hangzhou |    5 | 9you    |
| 163     | hangzhou |    6 | tx     |
| 9you    | shanghai |    6 | tx     |
| baidu    | hangzhou |    6 | tx     |
| tx     | hangzhou |    6 | tx     |
+-------------+----------+----------+-------------+

INNER JOIN聯(lián)接（內(nèi)聯(lián)接）

INNER JOIN比CROSS JOIN強(qiáng)大的一點(diǎn)在于，INNER JOIN可以根據(jù)一些過濾條件來匹配表之間的數(shù)據(jù)。在SQL邏輯查詢語句執(zhí)行的前三步中，INNER JOIN會(huì)執(zhí)行第一步和第二步；即沒有第三步，不添加外部行，這是INNER JOIN和接下來要說的OUTER JOIN的最大區(qū)別之一。

現(xiàn)在來看看使用INNER JOIN來查詢一下：

select * 
from table1 
inner join table2 
on table1.customer_id=table2.customer_id;

就會(huì)得到以下結(jié)果：

+-------------+----------+----------+-------------+
| customer_id | city   | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163     | hangzhou |    1 | 163     |
| 163     | hangzhou |    2 | 163     |
| 9you    | shanghai |    3 | 9you    |
| 9you    | shanghai |    4 | 9you    |
| 9you    | shanghai |    5 | 9you    |
| tx     | hangzhou |    6 | tx     |
+-------------+----------+----------+-------------+

對(duì)于INNER JOIN來說，如果沒有使用ON條件的過濾，INNER JOIN和CROSS JOIN的效果是一樣的。當(dāng)在ON中設(shè)置的過濾條件列具有相同的名稱，我們可以使用USING關(guān)鍵字來簡寫ON的過濾條件，這樣可以簡化sql語句，例如：

select * from table1 inner join table2 using(customer_id);

在實(shí)際編寫sql語句時(shí)，我們都可以省略掉INNER關(guān)鍵字，例如：

select * 
from table1 
join table2 
on table1.customer_id=table2.customer_id;

但是，請記住，這還是INNER JOIN。
OUTER JOIN聯(lián)接（外聯(lián)接）

哦，記得有一次參加面試，還問我這個(gè)問題來著，那在這里再好好的總結(jié)一下。通過OUTER JOIN，我們可以按照一些過濾條件來匹配表之間的數(shù)據(jù)。OUTER JOIN的結(jié)果集等于INNER JOIN的結(jié)果集加上外部行；也就是說，在使用OUTER JOIN時(shí)，SQL邏輯查詢語句執(zhí)行的前三步，都會(huì)執(zhí)行一遍。關(guān)于如何添加外部行，請參考《SQL邏輯查詢語句執(zhí)行順序》這篇文章中的添加外部行部分內(nèi)容。

MySQL數(shù)據(jù)庫支持LEFT OUTER JOIN和RIGHT OUTER JOIN，與INNER關(guān)鍵字一樣，我們可以省略O(shè)UTER關(guān)鍵字。對(duì)于OUTER JOIN，同樣的也可以使用USING來簡化ON子句。所以，對(duì)于以下sql語句：

select * 
from table1 
left outer join table2 
on table1.customer_id=table2.customer_id;

我們可以簡寫成這樣：

select * 
from table1 
left join table2 
using(customer_id);

但是，與INNER JOIN還有一點(diǎn)區(qū)別是，對(duì)于OUTER JOIN，必須指定ON（或者using）子句，否則MySQL數(shù)據(jù)庫會(huì)拋出異常。
NATURAL JOIN聯(lián)接（自然連接）

NATURAL JOIN等同于INNER(OUTER) JOIN與USING的組合，它隱含的作用是將兩個(gè)表中具有相同名稱的列進(jìn)行匹配。同樣的，NATURAL LEFT(RIGHT) JOIN等同于LEFT(RIGHT) JOIN與USING的組合。比如：

select * 
from table1 
join table2 
using(customer_id);

與

select * 
from table1 
natural join table2;

等價(jià)。

在比如：

select * 
from table1 
left join table2 
using(customer_id);

與

select * 
from table1 
natural left join table2;

等價(jià)。
STRAIGHT_JOIN聯(lián)接

STRAIGHT_JOIN并不是一個(gè)新的聯(lián)接類型，而是用戶對(duì)sql優(yōu)化器的控制，其等同于JOIN。通過STRAIGHT_JOIN，MySQL數(shù)據(jù)庫會(huì)強(qiáng)制先讀取左邊的表。舉個(gè)例子來說，比如以下sql語句：

explain select * 
from table1 join table2 
on table1.customer_id=table2.customer_id;

它的主要輸出部分如下：

+----+-------------+--------+------+---------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------+------+---------------+
| 1 | SIMPLE   | table2 | ALL | NULL     |
| 1 | SIMPLE   | table1 | ALL | PRIMARY    |
+----+-------------+--------+------+---------------+

我們可以很清楚的看到，MySQL是先選擇的table2表，然后再進(jìn)行的匹配。如果我們指定STRAIGHT_JOIN方式，例如：

explain select * 
from table1 straight_join table2 
on table1.customer_id=table2.customer_id;

上述語句的主要輸出部分如下：

+----+-------------+--------+------+---------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------+------+---------------+
| 1 | SIMPLE   | table1 | ALL | PRIMARY    |
| 1 | SIMPLE   | table2 | ALL | NULL     |
+----+-------------+--------+------+---------------+

可以看到，當(dāng)指定STRAIGHT_JOIN方式以后，MySQL就會(huì)先選擇table1表，然后再進(jìn)行的匹配。

那么就有讀者問了，這有啥好處呢？性能，還是性能。由于我這里測試數(shù)據(jù)比較少，大進(jìn)行大量數(shù)據(jù)的訪問時(shí)，我們指定STRAIGHT_JOIN讓MySQL先讀取左邊的表，讓MySQL按照我們的意愿來完成聯(lián)接操作。在進(jìn)行性能優(yōu)化時(shí)，我們可以考慮使用STRAIGHT_JOIN。
多表聯(lián)接

在上面的所有例子中，我都是使用的兩個(gè)表之間的聯(lián)接，而更多時(shí)候，我們在工作中，可能不止要聯(lián)接兩張表，可能要涉及到三張或者更多張表的聯(lián)接查詢操作。

對(duì)于INNER JOIN的多表聯(lián)接查詢，可以隨意安排表的順序，而不會(huì)影響查詢的結(jié)果。這是因?yàn)閮?yōu)化器會(huì)自動(dòng)根據(jù)成本評(píng)估出訪問表的順序。如果你想指定聯(lián)接順序，可以使用上面總結(jié)的STRAIGHT_JOIN。

而對(duì)于OUTER JOIN的多表聯(lián)接查詢，表的位置不同，涉及到添加外部行的問題，就可能會(huì)影響最終的結(jié)果。
總結(jié)

這是MySQL中聯(lián)接操作的全部內(nèi)容了，內(nèi)容雖多，但是都還比較簡單，結(jié)合文章中的例子，再自己實(shí)際操作一遍，完全可以搞定的。這一篇文章就這樣了。

您可能感興趣的文章: