幾種MySQL中的聯結查詢操作方法總結_Mysql

前言

現在系統的各種業務是如此的複雜，資料都存在資料庫中的各種表中，這個主鍵啊，那個外鍵啊，而表與表之間就依靠著這些主鍵和外鍵聯絡在一起。而我們進行業務操作時，就需要在多個表之間，使用sql語句建立起關係，然後再進行各種sql操作。那麼在使用sql寫出各種操作時，如何使用sql語句，將多個表關聯在一起，進行業務操作呢？而這篇文章，就對這個知識點進行總結。

聯結查詢是一種常見的資料庫操作，即在兩張表（多張表）中進行匹配的操作。MySQL資料庫支援如下的聯結查詢：

•     CROSS JOIN（交叉聯結）
•     INNER JOIN（內聯結）
•     OUTER JOIN（外聯結）
•     其它

在進行各種聯結操作時，一定要回憶一下在《SQL邏輯查詢語句執行順序》這篇文章中總結的SQL邏輯查詢語句執行的前三步：

•     執行FROM語句（笛卡爾積）
•     執行ON過濾
•     添加外部行

每個聯結都只發生在兩個表之間，即使FROM子句中包含多個表也是如此。每次聯結操作也只進行邏輯查詢語句的前三步，每次產生一個虛擬表，這個虛擬表再依次與FROM子句的下一個表進行聯結，重複上述步驟，直到FROM子句中的表都被處理完為止。
前期準備

 1.建立一個測試資料庫TestDB；

      

create database TestDB;

    建立測試表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;

    插入測試資料；

   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');

    準備工作做完以後，table1和table2看起來應該像下面這樣：

   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)

準備工作做的差不多了，開始今天的總結吧。
CROSS JOIN聯結（交叉聯結）

CROSS JOIN對兩個表執行FROM語句（笛卡爾積）操作，返回兩個表中所有列的組合。如果左表有m行資料，右表有n行資料，則執行CROSS JOIN將返回m*n行資料。CROSS JOIN只執行SQL邏輯查詢語句執行的前三步中的第一步。

CROSS JOIN可以幹什嗎？由於CROSS JOIN只執行笛卡爾積操作，並不會進行過濾，所以，我們在實際中，可以使用CROSS JOIN產生大量的測試資料。

對上述測試資料，使用以下查詢：

select * from table1 cross join table2;

就會得到以下結果：

+-------------+----------+----------+-------------+| 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聯結（內聯結）

INNER JOIN比CROSS JOIN強大的一點在於，INNER JOIN可以根據一些過濾條件來匹配表之間的資料。在SQL邏輯查詢語句執行的前三步中，INNER JOIN會執行第一步和第二步；即沒有第三步，不添加外部行，這是INNER JOIN和接下來要說的OUTER JOIN的最大區別之一。

現在來看看使用INNER JOIN來查詢一下：

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

就會得到以下結果：

+-------------+----------+----------+-------------+| 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     |+-------------+----------+----------+-------------+

對於INNER JOIN來說，如果沒有使用ON條件的過濾，INNER JOIN和CROSS JOIN的效果是一樣的。當在ON中設定的過濾條件列具有相同的名稱，我們可以使用USING關鍵字來簡寫ON的過濾條件，這樣可以簡化sql語句，例如：

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

在實際編寫sql語句時，我們都可以省略掉INNER關鍵字，例如：

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

但是，請記住，這還是INNER JOIN。
OUTER JOIN聯結（外聯結）

哦，記得有一次參加面試，還問我這個問題來著，那在這裡再好好的總結一下。通過OUTER JOIN，我們可以按照一些過濾條件來匹配表之間的資料。OUTER JOIN的結果集等於INNER JOIN的結果集加上外部行；也就是說，在使用OUTER JOIN時，SQL邏輯查詢語句執行的前三步，都會執行一遍。關於如何添加外部行，請參考《SQL邏輯查詢語句執行順序》這篇文章中的添加外部行部分內容。

MySQL資料庫支援LEFT OUTER JOIN和RIGHT OUTER JOIN，與INNER關鍵字一樣，我們可以省略OUTER關鍵字。對於OUTER JOIN，同樣的也可以使用USING來簡化ON子句。所以，對於以下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還有一點區別是，對於OUTER JOIN，必須指定ON（或者using）子句，否則MySQL資料庫會拋出異常。
NATURAL JOIN聯結（自然串連）

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

select * from table1 join table2 using(customer_id);

與

select * from table1 natural join table2;

等價。

在比如：

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

與

select * from table1 natural left join table2;

等價。
STRAIGHT_JOIN聯結

STRAIGHT_JOIN並不是一個新的聯結類型，而是使用者對sql最佳化器的控制，其等同於JOIN。通過STRAIGHT_JOIN，MySQL資料庫會強制先讀取左邊的表。舉個例子來說，比如以下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表，然後再進行的匹配。如果我們指定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     |+----+-------------+--------+------+---------------+

可以看到，當指定STRAIGHT_JOIN方式以後，MySQL就會先選擇table1表，然後再進行的匹配。

那麼就有讀者問了，這有啥好處呢？效能，還是效能。由於我這裡測試資料比較少，大進行大量資料的訪問時，我們指定STRAIGHT_JOIN讓MySQL先讀取左邊的表，讓MySQL按照我們的意願來完成聯結操作。在進行效能最佳化時，我們可以考慮使用STRAIGHT_JOIN。
多表聯結

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

對於INNER JOIN的多表聯結查詢，可以隨意安排表的順序，而不會影響查詢的結果。這是因為最佳化器會自動根據成本評估出訪問表的順序。如果你想指定聯結順序，可以使用上面總結的STRAIGHT_JOIN。

而對於OUTER JOIN的多表聯結查詢，表的位置不同，涉及到添加外部行的問題，就可能會影響最終的結果。
總結

這是MySQL中聯結操作的全部內容了，內容雖多，但是都還比較簡單，結合文章中的例子，再自己實際操作一遍，完全可以搞定的。這一篇文章就這樣了。