几种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中联接操作的全部内容了，内容虽多，但是都还比较简单，结合文章中的例子，再自己实际操作一遍，完全可以搞定的。这一篇文章就这样了。