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。
注意:
-
t1
对应图中的Table A
,t2
对应图中的Table B
。 - 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 了。