Join操作集合 INNER JOIN、LEFT/RIGHT JOIN、FULL JOIN、CROSS JOIN

假设两张表：t1 和 t2。它们的数据如下。

id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja

各有四条记录，有两条是一样的。

接下来，就以这两张表作为操作对象，介绍 SQL JOINS。

注意：

1. t1 对应图中的 Table A ， t2 对应图中的 Table B 。
2. MySQL 不支持 FULL JOIN 。

一、INNER JOIN

简单点说，就是交集。

 

请看下面的语句。

SELECT * FROM t1 INNER JOIN t2 
ON t1.name = t2.name

得结果

id  name       id   name
--  ----       --   ----
1   Pirate     2    Pirate
3   Ninja      4    Ninja

其实，多表联查默认使用的就是 INNER JOIN。就是说

SELECT * FROM t1 INNER JOIN t2 
ON t1.name = t2.name

# 等于

SELECT * FROM t1, t2 
WHERE t1.name = t2.name

二、LEFT JOIN

3.1 情况一、以 t1 为查询依据

 

SELECT * FROM t1 LEFT JOIN t2
ON t1.name = t2.name

得结果

id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

最终的数据是以左表 t1 为依据， t2 表中没有的，显示 null。且返回结果中的记录条数，总是等于表 t1 的记录条数。

3.2 情况二、独一无二的 t1

我们把下面的 t1 叫做独一无二的 t1 （相对于 t2 来说的）。

 

SELECT * FROM t1 LEFT JOIN t2
ON t1.name = t2.name
WHERE ISNULL(t2.name);

ISNULL(t2.name) 是过滤操作， 把t1 中包含 t2 的部分（即 t1 和 t2 的交集部分）过滤掉，过滤依据是字段 name。这就得到了独一无二的 t1 。

结果如下

id  name       id     name
--  ----       --     ----
2   Monkey     null   null
4   Spaghetti  null   null

三、RIGHT JOIN

RIGHT JOIN 与 LEFT JOIN 类似，这里不再赘述。

四、FULL JOIN

就是并集。

SELECT * FROM t1 FULL JOIN t2
ON t1.name = t2.name

 结果：

id    name       id    name
--    ----       --    ----
1     Pirate     2     Pirate
2     Monkey     null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

t2 表中没有的，用 null 替代； t1 表中没有的，用 null 替代。

MySQL 不支持 FULL JOIN 。可以用 UNION 来实现类似的功能。

SELECT * FROM t1 LEFT JOIN t2 ON t1.name = t2.name
UNION ALL
SELECT * FROM t1 RIGHT JOIN t2 ON t1.name = t2.name;

查询出非交集部分。

 

SELECT * FROM t1 FULL JOIN t2
ON t1.name = t2.name
WHERE t1.id IS null OR t2.id IS null

五、CROSS

 

SELECT * FROM t1 CROSS JOIN t2;

这是 t1、t2 表笛卡尔积运算后的结果——将 t2 表中的数据依次取出，取出时分别与 t1 表中所有数据挨个组合。也就是说，最后的结果中，一共有 4×4=16 条记录。

总结

通过下面这张图，可以较全面的理解 MySQL JOINS 了。