Mysql连接（leftjoin,rightjoin,innerjoin,fulljoin）

左连接：右边对左边做交集

右连接：左边对右边做交集

内连接：两个同时做交集

full连接：两个同时做并集

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

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

本章节我们将向大家介绍如何使用 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

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 INNERJOIN b ona.a = b.b;
select a.*,b.*  from a,bwhere a.a = b.b;
 
a | b
--+--
3 | 3
4 | 4

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

Left join

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

select * from a LEFTJOIN b ona.a = b.b;
 
a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right join

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

select * from a RIGHTJOIN b ona.a = b.b;
 
a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

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 FULLJOIN b ona.a = b.b;
 
 a   |  b
-----+-----
   1 |null
   2 |null
   3 |    3
   4 |    4
null |    6
null |    5

　　?

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

select * from a leftjoin b ona.a = b.b UNION select * froma right join b on a.a = b.b;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    1 | NULL|
|    2 | NULL|
| NULL|    5 |
| NULL|    6 |
+------+------+
6 rowsin set (0.00 sec)