连接查询
准备工作
首先创建两张数据表,用来后面做验证,表情况如下:
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)