mysql 连接查询

一、前言

 

我们已经学会了如果在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。

本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。

你可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

Suppose you have two tables, with a single column each, and data as follows:

A    B
-    -
1    3
2    4
3    5
4    6

 二、Mysql 连接（left join, right join, inner join ,full join）

 

2.1、Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b;
  
a | b
--+--
3 | 3
4 | 4

其实就是只显示2个表的交集

2.2、Left join

A left join will give all rows in A, plus any common rows in B.

select * from a LEFT JOIN b on a.a = b.b;
  
a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

2.3、Right join

A right join will give all rows in B, plus any common rows in A.

select * from a RIGHT JOIN b on a.a = b.b;
  
a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

2.4、Full join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa

select * from a FULL JOIN b on a.a = b.b;
  
 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

 mysql 并不直接支持full join,but 总是难不到我们：

select * from A left join B on A.a = B.b UNION select * from B right join A on A.a = B.b;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    1 | NULL |
|    2 | NULL |
| NULL |    5 |
| NULL |    6 |
+------+------+
6 rows in set (0.00 sec)