幾種MySQL中的聯(lián)接查詢操作方法總結
前言
現(xiàn)在系統(tǒng)的各種業(yè)務是如此的復雜，數(shù)據(jù)都存在數(shù)據(jù)庫中的各種表中，這個主鍵啊，那個外鍵啊，而表與表之間就依靠著這些主鍵和外鍵聯(lián)系在一起。而我們進行業(yè)務操作時，就需要在多個表之間，使用sql語句建立起關系，然后再進行各種sql操作。那么在使用sql寫出各種操作時，如何使用sql語句，將多個表關聯(lián)在一起，進行業(yè)務操作呢？而這篇文章，就對這個知識點進行總結。
聯(lián)接查詢是一種常見的數(shù)據(jù)庫操作，即在兩張表（多張表）中進行匹配的操作。MySQL數(shù)據(jù)庫支持如下的聯(lián)接查詢：
- CROSS JOIN（交叉聯(lián)接）
- INNER JOIN（內(nèi)聯(lián)接）
- OUTER JOIN（外聯(lián)接）
- 其它
在進行各種聯(lián)接操作時，一定要回憶一下在《SQL邏輯查詢語句執(zhí)行順序》這篇文章中總結的SQL邏輯查詢語句執(zhí)行的前三步：
- 執(zhí)行FROM語句（笛卡爾積）
- 執(zhí)行ON過濾
- 添加外部行
每個聯(lián)接都只發(fā)生在兩個表之間，即使FROM子句中包含多個表也是如此。每次聯(lián)接操作也只進行邏輯查詢語句的前三步，每次產(chǎn)生一個虛擬表，這個虛擬表再依次與FROM子句的下一個表進行聯(lián)接，重復上述步驟，直到FROM子句中的表都被處理完為止。
前期準備
1.新建一個測試數(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');
準備工作做完以后，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聯(lián)接（交叉聯(lián)接）
CROSS JOIN對兩個表執(zhí)行FROM語句（笛卡爾積）操作，返回兩個表中所有列的組合。如果左表有m行數(shù)據(jù)，右表有n行數(shù)據(jù)，則執(zhí)行CROSS JOIN將返回m*n行數(shù)據(jù)。CROSS JOIN只執(zhí)行SQL邏輯查詢語句執(zhí)行的前三步中的第一步。
CROSS JOIN可以干什么？由于CROSS JOIN只執(zhí)行笛卡爾積操作，并不會進行過濾，所以，我們在實際中，可以使用CROSS JOIN生成大量的測試數(shù)據(jù)。
對上述測試數(shù)據(jù)，使用以下查詢：
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聯(lián)接（內(nèi)聯(lián)接）
INNER JOIN比CROSS JOIN強大的一點在于，INNER JOIN可以根據(jù)一些過濾條件來匹配表之間的數(shù)據(jù)。在SQL邏輯查詢語句執(zhí)行的前三步中，INNER JOIN會執(zhí)行第一步和第二步；即沒有第三步，不添加外部行，這是INNER JOIN和接下來要說的OUTER JOIN的最大區(qū)別之一。
現(xiàn)在來看看使用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聯(lián)接（外聯(lián)接）
哦，記得有一次參加面試，還問我這個問題來著，那在這里再好好的總結一下。通過OUTER JOIN，我們可以按照一些過濾條件來匹配表之間的數(shù)據(jù)。OUTER JOIN的結果集等于INNER JOIN的結果集加上外部行；也就是說，在使用OUTER JOIN時，SQL邏輯查詢語句執(zhí)行的前三步，都會執(zhí)行一遍。關于如何添加外部行，請參考《SQL邏輯查詢語句執(zhí)行順序》這篇文章中的添加外部行部分內(nèi)容。
MySQL數(shù)據(jù)庫支持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還有一點區(qū)別是，對于OUTER JOIN，必須指定ON（或者using）子句，否則MySQL數(shù)據(jù)庫會拋出異常。
NATURAL JOIN聯(lián)接（自然連接）
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聯(lián)接
STRAIGHT_JOIN并不是一個新的聯(lián)接類型，而是用戶對sql優(yōu)化器的控制，其等同于JOIN。通過STRAIGHT_JOIN，MySQL數(shù)據(jù)庫會強制先讀取左邊的表。舉個例子來說，比如以下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表，然后再進行的匹配。
那么就有讀者問了，這有啥好處呢？性能，還是性能。由于我這里測試數(shù)據(jù)比較少，大進行大量數(shù)據(jù)的訪問時，我們指定STRAIGHT_JOIN讓MySQL先讀取左邊的表，讓MySQL按照我們的意愿來完成聯(lián)接操作。在進行性能優(yōu)化時，我們可以考慮使用STRAIGHT_JOIN。
多表聯(lián)接
在上面的所有例子中，我都是使用的兩個表之間的聯(lián)接，而更多時候，我們在工作中，可能不止要聯(lián)接兩張表，可能要涉及到三張或者更多張表的聯(lián)接查詢操作。
對于INNER JOIN的多表聯(lián)接查詢，可以隨意安排表的順序，而不會影響查詢的結果。這是因為優(yōu)化器會自動根據(jù)成本評估出訪問表的順序。如果你想指定聯(lián)接順序，可以使用上面總結的STRAIGHT_JOIN。
而對于OUTER JOIN的多表聯(lián)接查詢，表的位置不同，涉及到添加外部行的問題，就可能會影響最終的結果。
總結
這是MySQL中聯(lián)接操作的全部內(nèi)容了，內(nèi)容雖多，但是都還比較簡單，結合文章中的例子，再自己實際操作一遍，完全可以搞定的。這一篇文章就這樣了。
相關文章
mysql 松散的索引掃描(Loose index scan)
今天讀《High Performance MySQL》，發(fā)現(xiàn)一個“Loose index scan”，之前完全沒有聽說過。網(wǎng)上查了些資料，這個叫松散的索引掃描(Loose index scan)2016-05-05
MySQL中l(wèi)ower_case_table_names作用及使用小結
在使用DataEase連接外部數(shù)據(jù)庫時,可能會遇到啟動報錯的問題,官方文檔指出,修改數(shù)據(jù)庫配置文件中的lower_case_table_names=1參數(shù)可以解決此問題,此參數(shù)控制表名大小寫敏感性,感興趣的可以了解一下2024-09-09
這篇文章主要給大家介紹了關于mysql死鎖和分庫分表問題的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-04-04
MySQL遠程連接丟失問題解決方法（Lost connection to MySQL server）
這篇文章主要介紹了MySQL遠程連接丟失問題解決方法,Mysql錯誤Lost connection to MySQL server at ‘reading initial communication packet’, system error: 0解決方法,需要的朋友可以參考下2014-06-06
數(shù)據(jù)庫中的JOIN稱為連接,連接的主要作用是根據(jù)兩個或多個表中的列之間的關系,獲取存在于不同表中的數(shù)據(jù),下面這篇文章主要給大家介紹了關于MySQL中join查詢的深入探究,需要的朋友可以參考下2022-11-11
MySQL 是可以自定義臨時變量的，而臨時變量在做查詢優(yōu)化和靜態(tài)查詢分析時十分有用。而實際上很少有人記得 MySQL 的自定義變量。本篇文章講述 MySQL 的自定義變量的概念及特點。2021-05-05
這篇文章主要介紹了MySQL中的物理存儲結構用法,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2025-03-03
授權就是為某個用戶賦予某些權限。MySQL 提供了 GRANT 語句來為用戶設置權限。具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-06-06
MYSQL使用.frm恢復數(shù)據(jù)表結構的實現(xiàn)方法
在這里我們探討使用.frm文件恢復數(shù)據(jù)表機構（當然如果你以前備份過數(shù)據(jù)表，你可以使用調(diào)用備份的數(shù)據(jù)表）2010-02-02
MySql?字符集不同導致?left?join?慢查詢的問題解決
當兩個表的字符集不一樣,在使用字符型字段進行表連接查詢時,就需要特別注意下查詢耗時是否符合預期,本文主要介紹了MySql?字符集不同導致?left?join?慢查詢的問題解決,感興趣的可以了解一下2024-05-05