几种MySQL中的联接查询操作方法总结
前言
现在系统的各种业务是如此的复杂，数据都存在数据库中的各种表中，这个主键啊，那个外键啊，而表与表之间就依靠着这些主键和外键联系在一起。而我们进行业务操作时，就需要在多个表之间，使用sql语句建立起关系，然后再进行各种sql操作。那么在使用sql写出各种操作时，如何使用sql语句，将多个表关联在一起，进行业务操作呢？而这篇文章，就对这个知识点进行总结。
联接查询是一种常见的数据库操作，即在两张表（多张表）中进行匹配的操作。MySQL数据库支持如下的联接查询：
- CROSSJOIN（交叉联接）
- INNERJOIN（内联接）
- OUTERJOIN（外联接）
- 其它
在进行各种联接操作时，一定要回忆一下在《SQL逻辑查询语句执行顺序》这篇文章中总结的SQL逻辑查询语句执行的前三步：
- 执行FROM语句（笛卡尔积）
- 执行ON过滤
- 添加外部行
每个联接都只发生在两个表之间，即使FROM子句中包含多个表也是如此。每次联接操作也只进行逻辑查询语句的前三步，每次产生一个虚拟表，这个虚拟表再依次与FROM子句的下一个表进行联接，重复上述步骤，直到FROM子句中的表都被处理完为止。
前期准备
1.新建一个测试数据库TestDB；
createdatabaseTestDB;
创建测试表table1和table2；
CREATETABLEtable1 ( customer_idVARCHAR(10)NOTNULL, cityVARCHAR(10)NOTNULL, PRIMARYKEY(customer_id) )ENGINE=INNODBDEFAULTCHARSET=UTF8; CREATETABLEtable2 ( order_idINTNOTNULLauto_increment, customer_idVARCHAR(10), PRIMARYKEY(order_id) )ENGINE=INNODBDEFAULTCHARSET=UTF8;
插入测试数据；
INSERTINTOtable1(customer_id,city)VALUES('163','hangzhou');
INSERTINTOtable1(customer_id,city)VALUES('9you','shanghai');
INSERTINTOtable1(customer_id,city)VALUES('tx','hangzhou');
INSERTINTOtable1(customer_id,city)VALUES('baidu','hangzhou');
INSERTINTOtable2(customer_id)VALUES('163');
INSERTINTOtable2(customer_id)VALUES('163');
INSERTINTOtable2(customer_id)VALUES('9you');
INSERTINTOtable2(customer_id)VALUES('9you');
INSERTINTOtable2(customer_id)VALUES('9you');
INSERTINTOtable2(customer_id)VALUES('tx');
准备工作做完以后，table1和table2看起来应该像下面这样：
mysql>select*fromtable1; +-------------+----------+ |customer_id|city| +-------------+----------+ |163|hangzhou| |9you|shanghai| |baidu|hangzhou| |tx|hangzhou| +-------------+----------+ 4rowsinset(0.00sec) mysql>select*fromtable2; +----------+-------------+ |order_id|customer_id| +----------+-------------+ |1|163| |2|163| |3|9you| |4|9you| |5|9you| |6|tx| +----------+-------------+ 7rowsinset(0.00sec)
准备工作做的差不多了，开始今天的总结吧。
CROSSJOIN联接（交叉联接）
CROSSJOIN对两个表执行FROM语句（笛卡尔积）操作，返回两个表中所有列的组合。如果左表有m行数据，右表有n行数据，则执行CROSSJOIN将返回m*n行数据。CROSSJOIN只执行SQL逻辑查询语句执行的前三步中的第一步。
CROSSJOIN可以干什么？由于CROSSJOIN只执行笛卡尔积操作，并不会进行过滤，所以，我们在实际中，可以使用CROSSJOIN生成大量的测试数据。
对上述测试数据，使用以下查询：
select*fromtable1crossjointable2;
就会得到以下结果：
+-------------+----------+----------+-------------+ |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| +-------------+----------+----------+-------------+
INNERJOIN联接（内联接）
INNERJOIN比CROSSJOIN强大的一点在于，INNERJOIN可以根据一些过滤条件来匹配表之间的数据。在SQL逻辑查询语句执行的前三步中，INNERJOIN会执行第一步和第二步；即没有第三步，不添加外部行，这是INNERJOIN和接下来要说的OUTERJOIN的最大区别之一。
现在来看看使用INNERJOIN来查询一下：
select* fromtable1 innerjointable2 ontable1.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| +-------------+----------+----------+-------------+
对于INNERJOIN来说，如果没有使用ON条件的过滤，INNERJOIN和CROSSJOIN的效果是一样的。当在ON中设置的过滤条件列具有相同的名称，我们可以使用USING关键字来简写ON的过滤条件，这样可以简化sql语句，例如：
select*fromtable1innerjointable2using(customer_id);
在实际编写sql语句时，我们都可以省略掉INNER关键字，例如：
select* fromtable1 jointable2 ontable1.customer_id=table2.customer_id;
但是，请记住，这还是INNERJOIN。
OUTERJOIN联接（外联接）
哦，记得有一次参加面试，还问我这个问题来着，那在这里再好好的总结一下。通过OUTERJOIN，我们可以按照一些过滤条件来匹配表之间的数据。OUTERJOIN的结果集等于INNERJOIN的结果集加上外部行；也就是说，在使用OUTERJOIN时，SQL逻辑查询语句执行的前三步，都会执行一遍。关于如何添加外部行，请参考《SQL逻辑查询语句执行顺序》这篇文章中的添加外部行部分内容。
MySQL数据库支持LEFTOUTERJOIN和RIGHTOUTERJOIN，与INNER关键字一样，我们可以省略OUTER关键字。对于OUTERJOIN，同样的也可以使用USING来简化ON子句。所以，对于以下sql语句：
select* fromtable1 leftouterjointable2 ontable1.customer_id=table2.customer_id;
我们可以简写成这样：
select* fromtable1 leftjointable2 using(customer_id);
但是，与INNERJOIN还有一点区别是，对于OUTERJOIN，必须指定ON（或者using）子句，否则MySQL数据库会抛出异常。
NATURALJOIN联接（自然连接）
NATURALJOIN等同于INNER(OUTER)JOIN与USING的组合，它隐含的作用是将两个表中具有相同名称的列进行匹配。同样的，NATURALLEFT(RIGHT)JOIN等同于LEFT(RIGHT)JOIN与USING的组合。比如：
select* fromtable1 jointable2 using(customer_id);
与
select* fromtable1 naturaljointable2;
等价。
在比如：
select* fromtable1 leftjointable2 using(customer_id);
与
select* fromtable1 naturalleftjointable2;
等价。
STRAIGHT_JOIN联接
STRAIGHT_JOIN并不是一个新的联接类型，而是用户对sql优化器的控制，其等同于JOIN。通过STRAIGHT_JOIN，MySQL数据库会强制先读取左边的表。举个例子来说，比如以下sql语句：
explainselect* fromtable1jointable2 ontable1.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方式，例如：
explainselect* fromtable1straight_jointable2 ontable1.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。
多表联接
在上面的所有例子中，我都是使用的两个表之间的联接，而更多时候，我们在工作中，可能不止要联接两张表，可能要涉及到三张或者更多张表的联接查询操作。
对于INNERJOIN的多表联接查询，可以随意安排表的顺序，而不会影响查询的结果。这是因为优化器会自动根据成本评估出访问表的顺序。如果你想指定联接顺序，可以使用上面总结的STRAIGHT_JOIN。
而对于OUTERJOIN的多表联接查询，表的位置不同，涉及到添加外部行的问题，就可能会影响最终的结果。
总结
这是MySQL中联接操作的全部内容了，内容虽多，但是都还比较简单，结合文章中的例子，再自己实际操作一遍，完全可以搞定的。这一篇文章就这样了。