几种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中联接操作的全部内容了，内容虽多，但是都还比较简单，结合文章中的例子，再自己实际操作一遍，完全可以搞定的。这一篇文章就这样了。
您可能感兴趣的文章:简单介绍MySQL中的事务机制详解MySQL下InnoDB引擎中的Memcached插件MySQL下的RAND()优化案例分析
绛旓細澶栬繛鎺ユ槸涓绉嶅叧绯诲瀷鏁版嵁搴撳疄鐜扮粨鍚堟煡璇㈢殑鎶鏈傚湪浣跨敤澶栬繛鎺ユ煡璇㈣鍙ユ椂锛屽鏋滈渶瑕佹煡璇㈢殑琛ㄤ腑瀛樺湪涓瀵瑰鐨勫叧绯伙紝閭ｄ箞鎴戜滑瑕佷娇鐢ㄥ杩炴帴鎿嶄綔銆傞拡瀵瑰杩炴帴鎿嶄綔锛孧ySQL鏁版嵁搴撴彁渚涗簡涓夌涓嶅悓鐨勫疄鐜版柟寮忥細1. 宸﹀杩炴帴 宸﹀杩炴帴鏄互宸﹁〃涓哄熀纭鐨勮繛鎺ユ柟寮忥紝鏌ヨ宸﹁〃涓墍鏈夊瓨鍦ㄧ殑璁板綍锛屽苟灏嗗彸琛ㄤ腑鐨勮褰曞叏閮ㄥ尮閰嶃...
绛旓細MySQL涓父瑙佺殑杩炴帴鏌ヨ鏈夛細绛夊艰繛鎺锛屼娇鐢=杩炴帴涓ゅ垪鏁版嵁锛屾墍鏈夎兘澶熷尮閰嶇殑缁撴灉閮戒細琚樉绀哄嚭鏉ワ紱鍐呰繛鎺ワ紝鍏抽敭瀛桰NNER JOIN ON锛岃繛鎺ユ晥鏋滅瓑鍚屼簬绛夊艰繛鎺ワ紱宸﹁繛鎺ワ紝鍏抽敭瀛桳EFT JOIN ON锛屽叧閿瓧宸︿晶鐨勮〃鐨勬墍鏈夋暟鎹潎鏄剧ず锛屽叧閿瓧鍙充晶鐨勮〃鍖归厤鍐呭鏄剧ず锛屾棤瀵瑰簲鍐呭浣跨敤NULL濉厖;鍙宠繛鎺ワ紝鍏抽敭瀛桼IGHT JOIN ON锛屽叧...
绛旓細鍒嗕负鍐呰繛鎺ワ紝宸﹁繛鎺ワ紝鍙宠繛鎺ュ拰鍏ㄨ繛鎺ュ洓绉锛屽唴杩炴帴inner join :缁勫悎涓や釜琛ㄤ腑鐨勮褰曪紝杩斿洖鍏宠仈瀛楁鐩哥鐨勮褰曪紝涔熷氨鏄繑鍥炰袱涓〃鐨勪氦闆嗭紙闃村奖锛夐儴鍒嗐傚乏杩炴帴left join 鏄痩eft outer join鐨勭畝鍐欙紝瀹冪殑鍏ㄧО鏄乏澶栬繛鎺ワ紝鏄杩炴帴涓殑涓绉嶃 宸(澶)杩炴帴锛屽乏琛(a_table)鐨勮褰曞皢浼氬叏閮ㄨ〃绀哄嚭鏉ワ紝鑰屽彸琛(...
绛旓細澶氬澶氬叧绯伙細閫氳繃鍏宠仈琛ㄥ鎴愮哗琛紝杩炴帴瀛︾敓琛ㄥ拰绉戠洰琛ㄣ 琛ㄨ繛鎺ユ煡璇㈣瑙 鍐呰繛鎺ワ紙INNER JOIN锛夛細鍙樉绀哄叧鑱旀暟鎹紝SQL鏍囧噯鏂瑰紡銆 宸﹀杩炴帴锛圠EFT JOIN锛夛細宸﹁〃鍏ㄦ樉锛屽彸琛ㄥ叧鑱旀樉锛屾湭鍏宠仈鏄剧ずNULL銆 鍙冲杩炴帴锛圧IGHT JOIN锛夛細鍙宠〃鍏ㄦ樉锛屽乏琛ㄥ叧鑱旀樉锛屾湭鍏宠仈鏄剧ずNULL銆 鑷虫锛屾湰鏂囩殑绮惧崕...
绛旓細鍐呰繛鎺ユ槸 MySQL 涓渶甯哥敤鐨勮繛鎺ユ柟寮忎箣涓锛屼篃绉颁负绛夊艰繛鎺ャ傚唴杩炴帴鍙互灏嗙鍚堟潯浠剁殑鏁版嵁琛屼粠涓や釜鎴栧涓暟鎹〃涓煡璇㈠嚭鏉ワ紝骞跺皢瀹冧滑缁勫悎鎴愪竴寮犳柊琛ㄣ傚唴杩炴帴鐨勫叧閿湪浜庢寚瀹氱敤浜庤繛鎺ユ暟鎹〃鐨勫瓧娈碉紝杩欎簺瀛楁蹇呴』鍦ㄤ袱涓垨澶氫釜鏁版嵁琛ㄤ腑鍏锋湁鐩稿悓鐨勫笺傚父鐢ㄧ殑鍐呰繛鎺ョ被鍨嬪寘鎷笁琛ㄥ唴杩炴帴銆佸乏杩炴帴銆佸彸杩炴帴鍜屽叏杩炴帴銆
绛旓細MySQL涓夎〃宸﹁仈鏌: 杩炴帴琛鏌ヨ绠渚挎槗琛 鍦ㄦ暟鎹瓨鍌ㄥ拰澶勭悊杩囩▼涓紝琛ㄧ殑鑱旀帴鏌ヨ鏄潪甯稿父瑙佺殑鎿嶄綔銆傚畠浠娇寰楁垜浠彲浠ュ皢鏉ヨ嚜涓嶅悓琛ㄧ殑鏁版嵁缁勫悎璧锋潵浠ュ疄鐜版洿涓哄鏉傜殑鏌ヨ銆MySQL涓殑宸﹁仈鏌(left join)鏄叾涓竴绉嶈仈鎺ユ柟娉曪紝瀹冨厑璁告垜浠粠宸﹁〃涓寘鍚墍鏈夌殑璁板綍锛岃屽浜庡彸琛ㄦ潵璇达紝浠呬粎鍖呭惈閭ｄ簺鑳藉鍖归厤宸﹁〃鐨勮褰曘
绛旓細澶氳〃鏌ヨ鏈3绉嶆柟寮忥紝鍒嗗埆鏄細涓銆佷紶缁熸柟寮/*-- 浼犵粺杩炴帴鏂瑰紡 --*/ select e.ename,d.dname from dept d , emp e where d.deptno = e.deptno /*--natural join 涓ゅ紶琛ㄤ腑鍙湁涓涓悓鍚嶆爮浣嶆椂琛岃繛鎺 --*/ select e.ename,d.dname from dept d natural join emp e /*--using...
绛旓細鑱氬悎鏌ヨ灏辨槸鍦ㄤ竴涓垪涓婃墽琛屾煇涓鎿嶄綔锛屼緥濡傛眰鍒楃殑鍜屻佸钩鍧囧笺佹渶澶у笺佹渶灏忓肩瓑銆MySQL鎻愪緵浜嗗绉嶈仛鍚堝嚱鏁帮紝濡係UM銆丄VG銆丮AX銆丮IN銆丆OUNT绛夈備緥濡傦細SELECT SUM(salary) FROM employees;涓婅堪璇彞琛ㄧず鏌ヨ鍚嶄负鈥渆mployees鈥濈殑琛ㄤ腑锛屾墍鏈夎柂姘寸殑鎬诲拰銆杩炴帴鏌ヨ 杩炴帴鏌ヨ鏄寚灏嗕袱涓垨澶氫釜琛ㄧ殑鏁版嵁杩涜缁勫悎...
绛旓細Mysql杩炴帴锛坖oin锛夋煡璇 1銆佸熀鏈蹇 灏嗕袱涓〃鐨勬瘡涓琛岋紝浠モ滀袱涓ゆí鍚戝鎺モ濈殑鏂瑰紡锛屾墍寰楀埌鐨勬墍鏈夎鐨勭粨鏋溿傚亣璁撅細琛ˋ鏈塶1琛岋紝m1鍒楋紱琛˙鏈塶2琛岋紝m2鍒楋紱鍒欒〃A鍜岃〃B鈥滃鎺モ濅箣鍚庯紝灏变細鏈夛細n1*n2琛岋紱m1+m2鍒椼2銆佸垯浠栦滑瀵规帴锛堣繛鎺ワ級涔嬪悗鐨勭粨鏋滅被浼艰繖鏍凤細3銆杩炴帴鏌ヨ鍩烘湰褰㈠紡锛 from 琛1 銆愯繛鎺...
绛旓細缁欐煡璇㈠垪璧峰埆鍚 鏉′欢鏌ヨ闇瑕佺敤鍒皐here璇彞锛寃here璇彞蹇呴』鏀惧埌from璇彞鍚庨潰銆==璇硶鏍煎紡濡備笅== ==鏉′欢鏌ヨ鏀寔浠ヤ笅杩愮畻绗== ==鏌ヨ宸ヨ祫涓5000鐨勫憳宸ュ鍚== ==鎼厤%浣跨敤== (1)(2)(3)==鎼厤_浣跨敤== ==娉ㄦ剰浜嬮」== ==杩炴帴鏌ヨ鍒嗙被== 鏌ヨ姣忎釜鍛樺伐鐨勯儴闂ㄥ悕绉帮紝瑕佹眰鏄剧ず鍛樺伐鍚嶅拰閮ㄩ棬鍚 鎵惧嚭姣忎釜...
