《MySQL技术内幕：SQL编程》读书笔记 -- 连接与集合操作

连接查询

准备工作

首先创建两张数据表，用来后面做验证，表情况如下：

mysql> select * from a;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from b;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

1、CROSS JOIN（交叉连接）

CROSS JOIN 表示对两个表执行笛卡尔积，语法如下：

mysql> select * from a cross join b;
+------+------+
| id   | id   |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    3 |    1 |
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
|    1 |    3 |
|    2 |    3 |
|    3 |    3 |
|    1 |    4 |
|    2 |    4 |
|    3 |    4 |
+------+------+
12 rows in set (0.00 sec)

--ANSI SQL 89语法如下：

mysql> select * from a, b;
+------+------+
| id   | id   |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    3 |    1 |
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
|    1 |    3 |
|    2 |    3 |
|    3 |    3 |
|    1 |    4 |
|    2 |    4 |
|    3 |    4 |
+------+------+
12 rows in set (0.00 sec)

2、INNER JOIN（内连接）

[INNER] JOIN 表示两个表连接时表与表直接匹配的数据行查询出来，过滤条件对两张表都有作用，不同于OUTER JOIN，INNER JOIN 的过滤条条件可以在ON子句，也可是在where子句，二者没有区别。

mysql> select * from a inner join b on a.id = b.id;
+------+------+
| id   | id   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

// inner 可以省略

mysql> select * from a join b on a.id = b.id;
+------+------+
| id   | id   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from a join b where a.id = b.id;
+------+------+
| id   | id   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

3、OUTER JOIN（外连接）

外连接分为 LEFT [OUTER] JOIN（左外连接）、 RIGHT [OUTER] JOIN（右外连接），和 INNER JOIN 一样，OUTER可以省略；

LEFT [OUTER] JOIN 表示以左表位准，左表不受 ON 字句中的条件限制，如果没有和左表相匹配的右表记录，对应的记录行显示为NULL；RIGHT [OUTER] JOIN 相反:

mysql> select a.id aid, b.id bid from a left join b on a.id = b.id;
+------+------+
| aid  | bid  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

mysql> select a.id aid, b.id bid from a right join b on a.id = b.id;
+------+------+
| aid  | bid  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
| NULL |    4 |
+------+------+
4 rows in set (0.00 sec)

集合操作

1、UNION

UNION用于合并两个或多个SELECT的结果集，并去除任何重复记录。

注意：

       1）UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型；

       2）若类型不同，MySQL会进行隐式转换；

       3）结果列的名称由第一个输入决定。

mysql> select id from a union select id from b;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

2、UNION ALL

UNION ALL也是用与合并两个或多个SELECT的结果集，与UNION不同的是，不去除重复记录。

mysql> select id from a union all select id from b;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+
7 rows in set (0.00 sec)