几种MySQL中的联接查询操作方法总结

这篇文章主要介绍了几种MySQL中的联接查询操作方法总结,文中包括一些代码举例讲解,需要的朋友可以参考下

前言

现在系统的各种业务是如此的复杂，数据都存在数据库中的各种表中，这个主键啊，那个外键啊，而表与表之间就依靠着这些主键和外键联系在一起。而我们进行业务操作时，就需要在多个表之间，使用sql语句建立起关系，然后再进行各种sql操作。那么在使用sql写出各种操作时，如何使用sql语句，将多个表关联在一起，进行业务操作呢?而这篇文章，就对这个知识点进行总结。

联接查询是一种常见的数据库操作，即在两张表(多张表)中进行匹配的操作。MySQL数据库支持如下的联接查询：

CROSS JOIN(交叉联接)

INNER JOIN(内联接)

OUTER JOIN(外联接)

其它

在进行各种联接操作时，一定要回忆一下在《SQL逻辑查询语句执行顺序》这篇文章中总结的SQL逻辑查询语句执行的前三步：

执行FROM语句(笛卡尔积)

执行ON过滤

添加外部行

每个联接都只发生在两个表之间，即使FROM子句中包含多个表也是如此。每次联接操作也只进行逻辑查询语句的前三步，每次产生一个虚拟表，这个虚拟表再依次与FROM子句的下一个表进行联接，重复上述步骤，直到FROM子句中的表都被处理完为止。

前期准备

1.新建一个测试数据库TestDB;

1. create database TestDB; 

创建测试表table1和table2;

1. CREATE TABLE table1 
2. ( 
3. customer_id VARCHAR(10) NOT NULL, 
4. city VARCHAR(10) NOT NULL, 
5. PRIMARY KEY(customer_id) 
6. )ENGINE=INNODB DEFAULT CHARSET=UTF8; 
7.  
8. CREATE TABLE table2 
9. ( 
10. order_id INT NOT NULL auto_increment, 
11. customer_id VARCHAR(10), 
12. PRIMARY KEY(order_id) 
13. )ENGINE=INNODB DEFAULT CHARSET=UTF8; 

插入测试数据;

1. INSERT INTO table1(customer_id,city) VALUES('163','hangzhou'); 
2. INSERT INTO table1(customer_id,city) VALUES('9you','shanghai'); 
3. INSERT INTO table1(customer_id,city) VALUES('tx','hangzhou'); 
4. INSERT INTO table1(customer_id,city) VALUES('baidu','hangzhou'); 
5.  
6. INSERT INTO table2(customer_id) VALUES('163'); 
7. INSERT INTO table2(customer_id) VALUES('163'); 
8. INSERT INTO table2(customer_id) VALUES('9you'); 
9. INSERT INTO table2(customer_id) VALUES('9you'); 
10. INSERT INTO table2(customer_id) VALUES('9you'); 
11. INSERT INTO table2(customer_id) VALUES('tx'); 

准备工作做完以后，table1和table2看起来应该像下面这样：

1. mysql> select * from table1; 
2. +-------------+----------+ 
3. | customer_id | city | 
4. +-------------+----------+ 
5. | 163 | hangzhou | 
6. | 9you | shanghai | 
7. | baidu | hangzhou | 
8. | tx | hangzhou | 
9. +-------------+----------+ 
10. 4 rows in set (0.00 sec) 
11.  
12. mysql> select * from table2; 
13. +----------+-------------+ 
14. | order_id | customer_id | 
15. +----------+-------------+ 
16. | 1 | 163 | 
17. | 2 | 163 | 
18. | 3 | 9you | 
19. | 4 | 9you | 
20. | 5 | 9you | 
21. | 6 | tx | 
22. +----------+-------------+ 
23. 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生成大量的测试数据。

对上述测试数据，使用以下查询：

1. select * from table1 cross join table2; 

就会得到以下结果：

1. +-------------+----------+----------+-------------+ 
2. | customer_id | city | order_id | customer_id | 
3. +-------------+----------+----------+-------------+ 
4. | 163 | hangzhou | 1 | 163 | 
5. | 9you | shanghai | 1 | 163 | 
6. | baidu | hangzhou | 1 | 163 | 
7. | tx | hangzhou | 1 | 163 | 
8. | 163 | hangzhou | 2 | 163 | 
9. | 9you | shanghai | 2 | 163 | 
10. | baidu | hangzhou | 2 | 163 | 
11. | tx | hangzhou | 2 | 163 | 
12. | 163 | hangzhou | 3 | 9you | 
13. | 9you | shanghai | 3 | 9you | 
14. | baidu | hangzhou | 3 | 9you | 
15. | tx | hangzhou | 3 | 9you | 
16. | 163 | hangzhou | 4 | 9you | 
17. | 9you | shanghai | 4 | 9you | 
18. | baidu | hangzhou | 4 | 9you | 
19. | tx | hangzhou | 4 | 9you | 
20. | 163 | hangzhou | 5 | 9you | 
21. | 9you | shanghai | 5 | 9you | 
22. | baidu | hangzhou | 5 | 9you | 
23. | tx | hangzhou | 5 | 9you | 
24. | 163 | hangzhou | 6 | tx | 
25. | 9you | shanghai | 6 | tx | 
26. | baidu | hangzhou | 6 | tx | 
27. | tx | hangzhou | 6 | tx | 
28. +-------------+----------+----------+-------------+ 

INNER JOIN联接（内联接）

INNER JOIN比CROSS JOIN强大的一点在于，INNER JOIN可以根据一些过滤条件来匹配表之间的数据。在SQL逻辑查询语句执行的前三步中，INNER JOIN会执行第一步和第二步;即没有第三步，不添加外部行，这是INNER JOIN和接下来要说的OUTER JOIN的最大区别之一。

现在来看看使用INNER JOIN来查询一下：

1. select *  
2. from table1  
3. inner join table2  
4. on table1.customer_id=table2.customer_id; 

就会得到以下结果：

1. +-------------+----------+----------+-------------+ 
2. | customer_id | city | order_id | customer_id | 
3. +-------------+----------+----------+-------------+ 
4. | 163 | hangzhou | 1 | 163 | 
5. | 163 | hangzhou | 2 | 163 | 
6. | 9you | shanghai | 3 | 9you | 
7. | 9you | shanghai | 4 | 9you | 
8. | 9you | shanghai | 5 | 9you | 
9. | tx | hangzhou | 6 | tx | 
10. +-------------+----------+----------+-------------+ 

对于INNER JOIN来说，如果没有使用ON条件的过滤，INNER JOIN和CROSS JOIN的效果是一样的。当在ON中设置的过滤条件列具有相同的名称，我们可以使用USING关键字来简写ON的过滤条件，这样可以简化sql语句，例如：

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

在实际编写sql语句时，我们都可以省略掉INNER关键字，例如：

1. select *  
2. from table1  
3. join table2  
4. 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语句：

1. select *  
2. from table1  
3. left outer join table2  
4. on table1.customer_id=table2.customer_id; 

我们可以简写成这样：

1. select *  
2. from table1  
3. left join table2  
4. 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的组合。比如：

1. select *  
2. from table1  
3. join table2  
4. using(customer_id); 

与

1. select *  
2. from table1  
3. natural join table2; 

等价。

在比如：

1. select *  
2. from table1  
3. left join table2  
4. using(customer_id); 

与

1. select *  
2. from table1  
3. natural left join table2; 

等价。

STRAIGHT_JOIN联接

STRAIGHT_JOIN并不是一个新的联接类型，而是用户对sql优化器的控制，其等同于JOIN。通过STRAIGHT_JOIN，MySQL数据库会强制先读取左边的表。举个例子来说，比如以下sql语句：

1. explain select *  
2. from table1 join table2  
3. on table1.customer_id=table2.customer_id; 

它的主要输出部分如下：

1. +----+-------------+--------+------+---------------+ 
2. | id | select_type | table | type | possible_keys | 
3. +----+-------------+--------+------+---------------+ 
4. | 1 | SIMPLE | table2 | ALL | NULL | 
5. | 1 | SIMPLE | table1 | ALL | PRIMARY | 
6. +----+-------------+--------+------+---------------+ 

我们可以很清楚的看到，MySQL是先选择的table2表，然后再进行的匹配。如果我们指定STRAIGHT_JOIN方式，例如：

1. explain select *  
2. from table1 straight_join table2  
3. on table1.customer_id=table2.customer_id; 

上述语句的主要输出部分如下：

1. +----+-------------+--------+------+---------------+ 
2. | id | select_type | table | type | possible_keys | 
3. +----+-------------+--------+------+---------------+ 
4. | 1 | SIMPLE | table1 | ALL | PRIMARY | 
5. | 1 | SIMPLE | table2 | ALL | NULL | 
6. +----+-------------+--------+------+---------------+ 

可以看到，当指定STRAIGHT_JOIN方式以后，MySQL就会先选择table1表，然后再进行的匹配。

那么就有读者问了，这有啥好处呢?性能，还是性能。由于我这里测试数据比较少，大进行大量数据的访问时，我们指定STRAIGHT_JOIN让MySQL先读取左边的表，让MySQL按照我们的意愿来完成联接操作。在进行性能优化时，我们可以考虑使用STRAIGHT_JOIN。

多表联接

在上面的所有例子中，我都是使用的两个表之间的联接，而更多时候，我们在工作中，可能不止要联接两张表，可能要涉及到三张或者更多张表的联接查询操作。

对于INNER JOIN的多表联接查询，可以随意安排表的顺序，而不会影响查询的结果。这是因为优化器会自动根据成本评估出访问表的顺序。如果你想指定联接顺序，可以使用上面总结的STRAIGHT_JOIN。

而对于OUTER JOIN的多表联接查询，表的位置不同，涉及到添加外部行的问题，就可能会影响最终的结果。

总结

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