MySQL基础05-关联查询

简介

数据库的关联查询可分为5种，它们分别是：交叉连接（CROSS JOIN）、内连接（INNER JOIN）、外连接（LEFT JOIN/RIGHT JOIN）、联合查询（UNION与UNION ALL）、全连接（FULL JOIN）。

实验表A：

mysql> select * from A;
+------+--------+
| id   | name   |
+------+--------+
|    1 | nosee  |
|    2 | chan   |
|    3 | cheese |
|    4 | xyz    |
+------+--------+
4 rows in set (0.00 sec)

实验表B：

mysql> select * from B;
+------+------+
| id   | age  |
+------+------+
|    3 |   18 |
|    4 |   28 |
|    5 |   16 |
|    6 |  100 |
+------+------+
4 rows in set (0.00 sec)

一、交叉连接（CROSS JOIN） — 笛卡尔积

因为没有连接条件，所进行的表与表间的所有行的连接。，结果集会很大，没有意义，很少使用。

语法：

SELECT * FROM A,B(,C...);
# 或者
SELECT * FROM A CROSS JOIN B (CROSS JOIN C ...);

特点：
①连接查询没有写任何连接条件
②结果集中的总行数就是两张表中总行数的乘积(笛卡尔积)

案例：

mysql> select * from A,B;   # 或 select * from A CROSS JOIN B;
+------+--------+------+------+
| id   | name   | id   | age  |
+------+--------+------+------+
|    1 | nosee  |    3 |   18 |
|    2 | chan   |    3 |   18 |
|    3 | cheese |    3 |   18 |
|    4 | xyz    |    3 |   18 |
|    1 | nosee  |    4 |   28 |
|    2 | chan   |    4 |   28 |
|    3 | cheese |    4 |   28 |
|    4 | xyz    |    4 |   28 |
|    1 | nosee  |    5 |   16 |
|    2 | chan   |    5 |   16 |
|    3 | cheese |    5 |   16 |
|    4 | xyz    |    5 |   16 |
|    1 | nosee  |    6 |  100 |
|    2 | chan   |    6 |  100 |
|    3 | cheese |    6 |  100 |
|    4 | xyz    |    6 |  100 |
+------+--------+------+------+
16 rows in set (0.00 sec)

二、内连接（INNER JOIN）

多表中同时符合某种条件的数据记录的集合。 INNER JOIN可以缩写为JOIN。

语法：

SELECT * FROM A,B WHERE A.id = B.id;
# 或者
SELECT * FROM A INNER JOIN B ON A.id = B.id;

还可以使用using子句做条件，但相对用的比较少。

内连接分为3类：
1）等值连接：ON A.id = B.id
2）不等值连接：ON A.id > B.id
3）自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id = T2.pid;

案例：

mysql> SELECT * FROM A JOIN B ON A.id = B.id;  # 或  SELECT * FROM A,B WHERE A.id = B.id;
+------+--------+------+------+
| id   | name   | id   | age  |
+------+--------+------+------+
|    3 | cheese |    3 |   18 |
|    4 | xyz    |    4 |   28 |
+------+--------+------+------+
2 rows in set (0.00 sec)

三、外连接（LEFT JOIN/RIGHT JOIN）

1、左外连接
LEFT OUTER JOIN，以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN。

案例：

mysql> SELECT * FROM A LEFT JOIN B ON A.id = B.id;
+------+--------+------+------+
| id   | name   | id   | age  |
+------+--------+------+------+
|    1 | nosee  | NULL | NULL |
|    2 | chan   | NULL | NULL |
|    3 | cheese |    3 |   18 |
|    4 | xyz    |    4 |   28 |
+------+--------+------+------+
4 rows in set (0.00 sec)

2、右外连接
RIGHT OUTER JOIN，以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN。

案例：

mysql> SELECT * FROM A RIGHT JOIN B ON A.id = B.id;
+------+--------+------+------+
| id   | name   | id   | age  |
+------+--------+------+------+
|    3 | cheese |    3 |   18 |
|    4 | xyz    |    4 |   28 |
| NULL | NULL   |    5 |   16 |
| NULL | NULL   |    6 |  100 |
+------+--------+------+------+
4 rows in set (0.01 sec)

四、联合查询（UNION与UNION ALL）

语法：

SELECT * FROM A UNION SELECT * FROM B UNION ...

就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并。如果使用UNION ALL，则不会合并重复的记录行。

案例：

mysql> SELECT * FROM A UNION SELECT * FROM B;
+------+--------+
| id   | name   |
+------+--------+
|    1 | nosee  |
|    2 | chan   |
|    3 | cheese |
|    4 | xyz    |
|    3 | 18     |
|    4 | 28     |
|    5 | 16     |
|    6 | 100    |
+------+--------+
8 rows in set (0.02 sec)

UNION与UNION ALL的区别：
1）对重复结果的处理：UNION会去掉重复记录，UNION ALL不会；
2）对排序的处理：UNION会排序，UNION ALL只是简单地将两个结果集合并；
3）效率方面的区别：因为UNION 会做去重和排序处理，因此效率比UNION ALL慢很多

五、全连接（FULL JOIN）

MySQL不支持全连接。

可以使用LEFT JOIN和UNION和RIGHT JOIN联合使用来达到全连接的效果。

如：

SELECT * FROM A LEFT JOIN B ON A.id=B.id UNION 
SELECT * FROM A RIGHT JOIN B ON A.id=B.id

案例：

mysql> SELECT * FROM A LEFT JOIN B ON A.id=B.id UNION
    -> SELECT * FROM A RIGHT JOIN B ON A.id=B.id;
+------+--------+------+------+
| id   | name   | id   | age  |
+------+--------+------+------+
|    1 | nosee  | NULL | NULL |
|    2 | chan   | NULL | NULL |
|    3 | cheese |    3 |   18 |
|    4 | xyz    |    4 |   28 |
| NULL | NULL   |    5 |   16 |
| NULL | NULL   |    6 |  100 |
+------+--------+------+------+
6 rows in set (0.00 sec)

六、嵌套查询–子查询

用一条SQL语句的结果作为另一条SQL语句的条件。

如：

SELECT * FROM A WHERE id IN (SELECT id FROM B);

特点（规范）：
①子查询必须放在小括号中
②子查询一般放在比较操作符的右边，以增强代码可读性
③子查询(小括号里的内容)可出现在几乎所有的SELECT子句中(如：SELECT子句、FROM子句、WHERE子句、ORDER BY子句、HAVING子句……)

注意：一个子查询会返回一个标量（就一个值）、一个行、一个列或一个表，这些子查询称之为标量、行、列和表子查询

案例：

mysql> SELECT * FROM A WHERE id IN (SELECT id FROM B);
+------+--------+
| id   | name   |
+------+--------+
|    3 | cheese |
|    4 | xyz    |
+------+--------+
2 rows in set (0.03 sec)

七、经典实例

1、为了记录足球比赛的结果，设计如下表：
1）参赛队伍表：team

name	type	comment
teamID	int	主键
teamName	varchar(20)	队伍名称

mysql> select * from team;
+--------+----------+
| teamID | teamName |
+--------+----------+
|      1 | AA       |
|      2 | BB       |
|      3 | CC       |
+--------+----------+
3 rows in set (0.00 sec)

2）赛程表：match

name	type	comment
matchID	int	主键
hostTeanID	int	主队ID
guestTeanID	int	客队ID
matchResult	varchar(20)	比赛结果
matchTime	date	比赛日期

mysql> select * from `match`;
+---------+------------+-------------+-------------+------------+
| matchID | hostTeamID | guestTeamID | matchResult | matchTime  |
+---------+------------+-------------+-------------+------------+
|       1 |          1 |           2 | 2:0         | 2006-06-05 |
|       2 |          2 |           3 | 1:2         | 2006-06-12 |
|       3 |          1 |           2 | 3:0         | 2006-06-23 |
+---------+------------+-------------+-------------+------------+
3 rows in set (0.00 sec)

要求：
其中，match赛程表中的hostTeamID与guestTeamID都和team表中的teamID关联，查询2006-6-1到2006-7-1之间举行的所有比赛，并且用以下形式列出：拜仁 2:1 不菜 2006-6-22

分析：
主要的数据都在match表，基础查询如下：

select hostTeamID,matchResult,guestTeamID,matchTime from `match` 
where matchTime between "2006-6-1" and "2006-7-1";

关联查询：

# 相当于3表联合查询。
mysql> select t1.teamName,m.matchResult,t2.teamName,m.matchTime
    -> from `match` as m
    -> left join team as t1 on m.hostTeamID = t1.teamID
    -> left join team as t2 on m.guestTeamID = t2.teamID
    -> where m.matchTime between "2006-6-1" and "2006-7-1";
+----------+-------------+----------+------------+
| teamName | matchResult | teamName | matchTime  |
+----------+-------------+----------+------------+
| AA       | 2:0         | BB       | 2006-06-05 |
| BB       | 1:2         | CC       | 2006-06-12 |
| AA       | 3:0         | BB       | 2006-06-23 |
+----------+-------------+----------+------------+
3 rows in set (0.00 sec)