MySQL多表查询

bookandmusic 2021年08月17日 · 阅读 9

此次主要介绍多表查询中的三部分：合并查询结果、连接查询（交叉连接、内连接、左连接、右连接、全连接）。

UNION

描述

SQL UNION 操作符合并两个或多个 SELECT 语句的结果。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

语法

SQL UNION 语法

SELECT *column_name(s)* FROM *table1*
UNION
SELECT *column_name(s)* FROM *table2*;

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法

SELECT *column_name(s)* FROM *table1*
UNION ALL
SELECT *column_name(s)* FROM *table2*;

UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

实例

下面是选自 "Websites" 表的数据：

id	name	url	alexa	country
1	Google	https://www.google.cm/	1	USA
2	淘宝	https://www.taobao.com/	13	CN
3	微博	http://weibo.com/	20	CN
4	Facebook	https://www.facebook.com/	3	USA
5	stackoverflow	http://stackoverflow.com/	0	IND

下面是 "apps" APP 的数据：

id	app_name	url	country
1	QQ APP	http://im.qq.com/	CN
2	微博 APP	http://weibo.com/	CN
3	淘宝 APP	https://www.taobao.com/	CN

SQL UNION 实例

下面的 SQL 语句从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）：

mysql> SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
+---------+
| country |
+---------+
| CN      |
| IND     |
| USA     |
+---------+
3 rows in set (0.00 sec)

SQL UNION ALL 实例

下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值）：

mysql> SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;
+---------+
| country |
+---------+
| CN      |
| CN      |
| CN      |
| CN      |
| CN      |
| IND     |
| USA     |
| USA     |
+---------+
8 rows in set (0.00 sec)

带有 WHERE 的 SQL UNION ALL

下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据（也有重复的值）：

mysql> SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;
+---------+------------+
| country | name       |
+---------+------------+
| CN      | 淘宝     |
| CN      | 微博     |
| CN      | QQ APP     |
| CN      | 微博 APP |
| CN      | 淘宝 APP |
+---------+------------+
5 rows in set (0.00 sec)

JOIN

描述

SQL join 用于把来自两个或多个表的行结合起来。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

u5NYj0

INNER JOIN 关键字在表中存在至少一个匹配时返回行。
LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。
FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
CROSS JOIN(交叉连接)又可称为笛卡尔积，将左表中每一行与右表中每一行分别连接形成新记录。实际业务中运用较少，需要大量运算成本，但它是其他连接的基础。

语法

SQL CROSS JOIN语法

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

或：

SELECT column_name(s)
FROM table1
JOIN table2

注释： CROSS JOIN 与 JOIN 是相同的。

SQL INNER JOIN语法

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

注释： INNER JOIN 与 JOIN 是相同的。

SQL RIGHT JOIN语法

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

SQL LEFT JOIN语法

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

SQL FULL JOIN语法

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

实例

下面是选自 "Websites" 表的数据：

id	name	url	alexa	country
1	Google	https://www.google.cm/	1	USA
2	淘宝	https://www.taobao.com/	13	CN
3	微博	http://weibo.com/	20	CN
4	Facebook	https://www.facebook.com/	3	USA
5	stackoverflow	http://stackoverflow.com/	0	IND
6	京东	https://www.jd.com/	19	CN

下面是 "access_log" 网站访问记录表的数据：

id	site_id	count	date
1	1	45	2016-05-10
2	3	100	2016-05-13
3	111	230	2016-05-14
4	2	10	2016-05-14
5	5	205	2016-05-14
6	4	13	2016-05-15
7	3	220	2016-05-15
8	5	545	2016-05-16
9	3	201	2016-05-17

SQL INNER JOIN实例

mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;
+----+---------------+-------+------------+
| id | name          | count | date       |
+----+---------------+-------+------------+
|  1 | Google        |    45 | 2016-05-10 |
|  3 | 微博        |   100 | 2016-05-13 |
|  1 | Google        |   230 | 2016-05-14 |
|  2 | 淘宝        |    10 | 2016-05-14 |
|  5 | stackoverflow |   205 | 2016-05-14 |
|  4 | Facebook      |    13 | 2016-05-15 |
|  3 | 微博        |   220 | 2016-05-15 |
|  5 | stackoverflow |   545 | 2016-05-16 |
|  3 | 微博        |   201 | 2016-05-17 |
+----+---------------+-------+------------+
9 rows in set (0.00 sec)

SQL RIGHT JOIN实例

mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
RIGHT JOIN access_log
ON Websites.id=access_log.site_id;
+------+---------------+-------+------------+
| id   | name          | count | date       |
+------+---------------+-------+------------+
|    1 | Google        |    45 | 2016-05-10 |
|    3 | 微博        |   100 | 2016-05-13 |
| NULL | NULL          |   230 | 2016-05-14 |
|    2 | 淘宝        |    10 | 2016-05-14 |
|    5 | stackoverflow |   205 | 2016-05-14 |
|    4 | Facebook      |    13 | 2016-05-15 |
|    3 | 微博        |   220 | 2016-05-15 |
|    5 | stackoverflow |   545 | 2016-05-16 |
|    3 | 微博        |   201 | 2016-05-17 |
+------+---------------+-------+------------+
9 rows in set (0.00 sec)


mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
RIGHT JOIN access_log
ON Websites.id=access_log.site_id WHERE Websites.id IS NULL;
+------+------+-------+------------+
| id   | name | count | date       |
+------+------+-------+------------+
| NULL | NULL |   230 | 2016-05-14 |
+------+------+-------+------------+
1 row in set (0.00 sec)

SQL LEFT JOIN实例

mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id;
+----+---------------+-------+------------+
| id | name          | count | date       |
+----+---------------+-------+------------+
|  1 | Google        |    45 | 2016-05-10 |
|  2 | 淘宝        |    10 | 2016-05-14 |
|  3 | 微博        |   201 | 2016-05-17 |
|  3 | 微博        |   220 | 2016-05-15 |
|  3 | 微博        |   100 | 2016-05-13 |
|  4 | Facebook      |    13 | 2016-05-15 |
|  5 | stackoverflow |   545 | 2016-05-16 |
|  5 | stackoverflow |   205 | 2016-05-14 |
|  6 | 京东        | NULL  | NULL       |
+----+---------------+-------+------------+
9 rows in set (0.00 sec)


mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id WHERE access_log.site_id IS NULL;
+----+--------+-------+------+
| id | name   | count | date |
+----+--------+-------+------+
|  6 | 京东 | NULL  | NULL |
+----+--------+-------+------+
1 row in set (0.00 sec)

SQL FULL JOIN实例

MySQL中不支持 FULL OUTER JOIN，你可以用以下SQL语句实现效果。

mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
UNION
SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
RIGHT JOIN access_log
ON Websites.id=access_log.site_id;
+------+---------------+-------+------------+
| id   | name          | count | date       |
+------+---------------+-------+------------+
|    1 | Google        |    45 | 2016-05-10 |
|    2 | 淘宝        |    10 | 2016-05-14 |
|    3 | 微博        |   201 | 2016-05-17 |
|    3 | 微博        |   220 | 2016-05-15 |
|    3 | 微博        |   100 | 2016-05-13 |
|    4 | Facebook      |    13 | 2016-05-15 |
|    5 | stackoverflow |   545 | 2016-05-16 |
|    5 | stackoverflow |   205 | 2016-05-14 |
|    6 | 京东        | NULL  | NULL       |
| NULL | NULL          |   230 | 2016-05-14 |
+------+---------------+-------+------------+
10 rows in set (0.00 sec);


mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
RIGHT JOIN access_log
ON Websites.id=access_log.site_id WHERE Websites.id IS NULL
UNION 
SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id WHERE access_log.site_id IS NULL;
+------+--------+-------+------------+
| id   | name   | count | date       |
+------+--------+-------+------------+
| NULL | NULL   |   230 | 2016-05-14 |
|    6 | 京东 | NULL  | NULL       |
+------+--------+-------+------------+
2 rows in set (0.00 sec)

0 赞

分类: DB

标签: mysql

本文作者：bookandmusic

本文链接： https://bookandmusic.cn/archives/dc3b805