SQL之表格的合并与联结 | 北落师门's Blog

表格的合并与联结

1.UNION

给出每条SELECT语句，在各条语句之间放上关键字UNION ，就可以将查询结果合并。例如：

SELECT id, name, gender, district
FROM account_2c
UNION
SELECT id, agent_name, agent_gender, company_addr
FROM account_2b;

上述代码的作用：

从account_2c与account_2b中筛选出用户的名称、性别、位置信息，并合并。

第1、2行，对account_2c表进行查询。

第3行，使用UNION联合下一次查询。

第4、5行，对account_2b表进行查询。

UNION ALL

对合并结果去重，是 UNION 的默认行为。但如果想要保留重复值并返回所有行，我们可以使用 UNION ALL。

可以把 UNION 的过程理解为将第二个表的查询结果上下拼接给第一个表，在这个过程中我们只会保留第一次查询时制定的字段。

对合并后的结果进行 ORDER BY 排序时，需要注意 ORDER BY 只能出现在最后一条 SELECT 语句之后。

2.JOIN

将一个或多个表格通过某种关系横向合并为一个表格的过程，称为联结 JOIN。

这个过程中，两个表格中通常会有重合的内容。

在SQL中，每个表格都可以看作是一个集合，表格的联结就是表格之间相乘得到的笛卡尔积。通俗来讲，所谓的笛卡尔积其实就是在做数据间的排列组合。

进行多个表格的查询时，为了更准确的告诉SQL某一列的归属。在使用 SELECT 查找时，通过点符号限定一个列的来源，格式为 “表名.列名”。

当表名过长时，我们也可以通过 AS 为表设定一个临时别名。在查询结束之前，表都将以设定的别名显示。

三种联结方式:

SQL 提供联结（笛卡尔积）、内联结、外联结三种联结表格的方式。

2.1联结（笛卡尔积）

当没有设定联结条件时，表格间的联结实际就是表格相乘得到的笛卡尔积。其中应用最多的是数据间的排列组合问题，可能是不同表格间的数据组合，也可能是同一个表格的自联结问题。

例如：

SELECT c1.sku_name, c1.exposure, c2.sku_name, c2.exposure
FROM commodity_data_views AS c1
JOIN commodity_data_views AS c2
WHERE c1.sku_name = '榴莲口红' AND c1.exposure < c2.exposure;

上面的代码是从商品数据视图表中，筛选出所有曝光量大于‘榴莲口红’的产品。

第1行，选择显示筛选后的商品名称与曝光量。

第2行，从commodity_data_views中查询，并为表设定别名c1。

第3行，对commodity_data_views自联结，并把联结的表设定为c2。

第4行，设定筛选条件，c1中所有的‘榴莲口红’产品且c2中曝光了大于c1的所有商品。

需要注意，因为联结的是两个相同的表格，所以SQL 无法区分重名的表与列。为了让 SQL 准确知道想要显示的信息，在为表重命名后，同时也要使用表名去限定列的名称。在这里分别为表取别名为c1与c2，在使用c2的sku_name列时，要写为c2.sku_name。

例题：在员工表（employees）中统计出薪水比编号为700001的员工少的人数。

employee_id	name	birthday	gender	salary	department
700001	陈晶	1987/12/22	男	12800	市场
700002	黄峰	1998/06/11	女	23900	人力
700003	杨飞	1996/11/02	男	18400	人力
700004	魏博	1992/07/17	女	23900	人力
700005	杨桂芳	1985/09/16	男	10700	行政
700006	祝艳	1989/11/16	女	23400	市场
700007	罗丽	1988/08/25	男	31200	设计
700008	包秀英	1997/07/27	女	33100	行政
700009	沈琴	1989/11/16	男	20500	增长
700010	曾楠	1991/11/24	女	37200	技术

代码实现：

SELECT COUNT(*) AS 人数
FROM employees AS c1
JOIN employees AS c2
WHERE c1.employee_id = 700001 AND c1.salary > c2.salary;

例题：apps和websites表格分别记录了部分全球比较热门的App和网站。使用SQL语句找出开发者是中国的App和网站，将列重命名为apps_and_websites

apps表

id	app_name	country
1	Wechat	China
2	TikTok	China
3	Notability	US
4	Facebook	US
5	Instagram	US
6	Youtube	US
7	Clean Master	China
8	UC browser	China

websites表

id	website_name	country
1	Google.com	US
2	Youtube.com	US
3	Baidu.com	China
4	Sohu.com	China
5	Amazon.com	US
6	Naver.com	Korea
7	Taobao.com	China

代码实现：

SELECT app_name AS apps_and_websites
FROM apps
WHERE country = 'China'
UNION
SELECT website_name AS apps_and_websites
FROM websites
WHERE country = 'China';

例题：利用SQL语句从weather表中查找出，今天比昨天温度更高的所有日期的id

weathe表

id	date	temperature
1	2021-02-01	10
2	2021-02-02	25
3	2021-02-03	20
4	2021-02-04	30

代码实现：

SELECT c1.id 
FROM weather AS c1
JOIN weather AS c2
WHERE TIMESTAMPDIFF(day, c2.date, c1.date) = 1 AND c1.temperature > c2.temperature;

例题：从表cinema_seat获取所有连续的两个空余座位，并将它们按照seat_id正序排列。

注意：free 字段中（’1’ 表示空余， ‘0’ 表示已被占据）。

cinema_seat表

seat_id	free
1	1
2	0
3	1
4	1
5	0
6	1

代码实现：

SELECT c1.seat_id
FROM cinema_seat AS c1
JOIN cinema_seat AS c2
WHERE ABS(c1.seat_id - c2.seat_id) = 1 AND c1.free = 1 AND c2.free =1
ORDER BY c1.seat_id;

3.内连接INNER JOIN

SELECT c.id, sku_name, brand_name
FROM commodity AS c
INNER JOIN brand AS b
ON c.brand_id = b.id;

上述代码：

将商品表与品牌表的数据联结，并筛选出所有的商品编号，商品名与品牌名。

第1行，选择商品编号、商品名称与品牌名称列。

第2，3行，将商品表与品牌表联结，并分别设定别名。

第4行，设定联结条件，按品牌编号联结。

其中，ON为连接设定约束。与WHERE类似，ON后需要一个或多个条件表达式。

不同的是：

WHERE 设定的条件用于约束本次查询的所有数据

ON 设定的条件用于约束表格的联结过程。

注意：

多个表格进行内联结时，若不同的表中列名相同。必须使用表名来限定列名，从而明确地指出是哪个表的哪列。否则，当 SQL 无法区分列的归属时，查询会报错。
在 SQL 查询时，若未指定联结类型，数据库默认按照内联结进行。所以，内联结的INNER关键字可以省略。

3.1多个表联结：

不只是两个表间的联结，可以使用 JOIN 连接任意个数的表。完成这一操作，只需要在 SELECT … JOIN … ON … 的基础上再加一行JOIN … ON … 就可以。例如：

SELECT Recipe_Classes.RecipeClassDescription,Recipes.RecipeTitle,Recipes.Preparation,
Ingredients.IngredientName,Recipe_Ingredients.RecipeSeqNo,Recipe_Ingredients.Amount,
Measurements.MeasurementDescription
FROM Recipe_Classes
JOIN Recipes
ON Recipe_Classes.RecipeClassID = Recipes.RecipeClassID 
JOIN Recipe_Ingredients
ON Recipes.RecipeID = Recipe_Ingredients.RecipeID
JOIN Ingredients 
ON Ingredients.IngredientID = Recipe_Ingredients.IngredientID
JOIN Measurements 
ON Measurements.MeasureAmountID = Recipe_Ingredients.MeasureAmountID
ORDER BY RecipeTitle, RecipeSeqNo;

注意:

每增加一次联结，查询的时间就会相应地变长，记述的内容也会变得难以让人理解。所以，推荐只在必要的时候使用多个联结。
例题：分别设定商品表、分类表的别名为sku、cate。根据cate.id = sku.category_id联结商品表与分类表。根据brand.id = sku.brand_id 联结品牌表与商品表。需要显示商品表的sku_name、 sku_price，分类表的 category_name与品牌表的 brand_name。

表commodity

sku_name	sku_price	category_name	brand_name
神仙口红	488	美容美妆	香奶奶
五彩缤纷粉底	247	美容美妆	香奶奶
神仙眼影	278	美容美妆	香奶奶

表brand

id	brand_name
200001	香奶奶
200002	雅诗绿黛
200003	绿蔻
200004	百世

表classification

id	category_name
300001	美容美妆
300002	休闲零食
300003	服饰鞋包
300004	家居家电

代码实现：

SELECT sku_name,sku_price,category_name,brand_name
FROM commodity AS sku
JOIN classification AS cate
ON cate.id = sku.category_id
JOIN brand
ON brand.id = sku.brand_id;

3.2 对联结的表格再次进行筛选

当查询开始时，SQL 通过ON后约束的条件，对两个表进行联结。在这个过程结束后，会将联结的结果生成一个临时表。然后SQL再执行 WHERE 中的条件，根据条件从这个临时表中筛选想要的数据。

最后，要想正确快速的使用联结，知道表之间的关系至关重要。所以多表查询第一步，应该是带着需求快速浏览相关ER图。

例题：找出所有居住地在南京市、杭州市和上海市的个人用户。需要输出用户姓名、所在的城市（city）。个人用户表 account_2c 设定别名为 a ，地址表 address 设定别名为 addr

account_2c表

id	name	gender	birthday	district
1004000001	朱桂芝	男	2000-10-05	澳门特别行政区
1004000002	覃丽丽	女	1994-08-30	河北省
1004000003	张娟	男	1999-10-14	新疆维吾尔自治区
1004000004	陈金凤	女	1999-09-13	江苏省

address表

id	account_id	country	province	city	district	detailed_addr	recipient	contact_number
1002000001	1004000001	中国	澳门特别行政区	澳门	花地玛堂区	张路M座	朱桂芝	1522772890
1002000002	1004000002	中国	河北省	石家庄市	新华区	太原街H座	覃丽丽	1894686860
1002000003	1004000003	中国	新疆维吾尔自治区	乌鲁木齐市	沙依巴克区	上海路T座	张娟	1596920559
1002000004	1004000004	中国	江苏省	南京市	鼓楼区	孙路q座	陈金凤	1327886051
1002000005	1004000005	中国	黑龙江省	哈尔滨市	南岗区	刘路i座	王海燕	1551352873
1002000006	1004000006	中国	浙江省	杭州市	江干区	太原街d座	金东	1318641791
1002000007	1004000007	中国	北京市	北京市	东城区	哈尔滨街B座	赵小红	1458242011
1002000008	1004000008	中国	辽宁省	沈阳市	铁西区	天津街q座

代码实现：

SELECT name,city
FROM account_2c AS a
JOIN address AS addr
ON a.id = addr.account_id
WHERE city IN ('南京市','杭州市','上海市');

例题：申请的学校和专业信息记录在apply表中，而大学学校的相关信息则记录在college表中。编写SQL语句帮助明明查询申请表apply中申请学校的相关信息

apply表

cName	enrollment
Cornell	21000
Stanford	15000
MIT	10000
Berkeley	36000

college表

cName	state	enrollment
Berkeley	CA	36000
Cornell	NY	21000
Harvard	MA	29000
MIT	MA	10000

代码实现：

SELECT DISTINCT apply.cName AS cName,enrollment
FROM apply 
JOIN college 
ON apply.cName = college.cName;

例题：websites表中记录了部分网站的名称信息等；而access_log表则记录了网站的访问记录。编写SQL语句找出网站对应的访问记录信息，并按照网站被访问次数正序排列。

表websites

website_name	count	date
Sohu.com	15	2020-05-06
Google.com	46	2020-05-03
Google.com	100	2020-05-01
Sohu.com	201	2020-05-08

表access_log

id	site_id	count	date
1	1	100	2020-05-01
2	3	238	2020-05-03
3	1	46	2020-05-03
4	2	348	2020-05-06
5	5	523	2020-05-06
6	4	15	2020-05-06
7	2	322	2020-05-05

代码实现：

SELECT website_name,count,date
FROM websites
JOIN access_log
ON websites.id = access_log.site_id
ORDER BY count;

例题：员工工资情况记录在company_salary表中，员工的职称情况记录在titles表中。编写SQL语句统计夜曲优选公司各部门员工的平均工资 (average)，并按照平均工资正序排列

company_salary表

emp_no	salary	from_date	to_date
10001	130958	2021-01-26	9999-01-01
10004	174057	2014-12-01	9999-01-01
10006	143311	2021-06-02	9999-01-01
10007	188070	2010-02-07	9999-01-01

titles表

emp_no	title	from_date	to_date
10001	Product Manager	2021-01-26	9999-01-01
10003	Senior Engineer	2011-12-01	9999-01-01
10004	Senior Engineer	2014-12-01	9999-01-01
10006	Senior Engineer	2021-08-02	9999-01-01

代码实现：

SELECT title,AVG(salary) AS average
FROM company_salary
JOIN titles
ON company_salary.emp_no = titles.emp_no
GROUP BY title
ORDER BY average;

4.外联结 OUTER JOIN

SELECT t1.id, sku_name, text
FROM commodity AS t1
LEFT JOIN comment AS t2
ON t1.id = t2.sku_id;

筛选所有数据与评论。

第1行，输出商品的id、名称与评论文本；

第2、3行，左外联结商品表与评论表；

第4行，设定联结列为商品编号；

外联结(OUTER JOIN)能让数据库根据指定的规则返回匹配数据的同时，返回被关联的一个表中不匹配的行。

当使用外联结来创建查询时，SQL 标准会认为第一个表在左边、第二个表在右边。

外联结分为左联结、右联结两种。关键字为 LEFT JOIN 与 RIGHT JOIN 。

4.1 LEFT JOIN

在联结时，若想要返回第一个表（左表）的所有内容，以及第二个表的匹配内容，则使用左联结。

在返回结果中，未找到匹配的数据会被填入NULL。

如下图，当把 Boy 与 Girlfriend 两个表左联结后，因为Joey在Girlfriend中没有找到匹配的数据，所以为NULL。

4.2 RIGHT JOIN

若想要返回第二个表（右边）的所有内容，以及第一个表的匹配内容，则使用右联结。

如下图，当把 Boy 与 Girlfriend 两个表右联结后。因为Phoebe在Boy中未找到匹配的数据，所以为NULL。

可以通过调换表的位置来实现左外连接和右外连接的转换。为了避免混淆，在查询时要尽量不要左、右联结的混合使用。

例题：统计出每个产品的评论数

commodity表

id	sku_price	sku_name	category_id	brand_id
1003000001	488	神仙口红	300001	200001
1003000002	247	五彩缤纷粉底	300001	200001
1003000003	278	神仙眼影	300001	200001
1003000004	276	神仙遮瑕	300001	200001
1003000005	493	神仙口红	300001	200002
1003000006	242	神仙粉底	300001	200002
1003000007	476	五彩缤纷眼影	300001	200002
1003000008	461	神仙遮瑕	300001	200002
1003000009	309	榴莲口红	300001	200003
1003000010	341	榴莲粉底	300001	200003

comment表：

id	sku_id	text
1006000001	1003000001	给家里人买的，很精致
1006000002	1003000001	132唇色深的姐妹还是算了吧颜色挺好看的也不拔干物流挺快的
1006000003	1003000001	明明香奶奶口红扫码扫出来显示是其他牌口红拿到手明显拆封过包装透明袋没有直接就是打开的！口红还是如图显示差评！
1006000004	1003000001	东西特别一般不好用
1006000005	1003000001	超好看的色号
1006000006	1003000001	上嘴效果：很好，女朋友很喜欢
1006000007	1003000001	很好很喜欢
1006000008	1003000001	买了两次了，这次给闺蜜，很好看的颜色
1006000009	1003000001	买来送老婆的，包装很精致！要让一个男人来评价好坏的话我只能说这是正品其他的也不懂！
1006000010	1003000001	实物颜色和图片颜色完全不一样，涂上像巫婆后妈既视感。

代码实现：

SELECT t1.id,sku_name,COUNT(text) AS 评论数
FROM commodity AS t1
LEFT JOIN comment AS t2
ON t1.id = t2.sku_id
GROUP BY t1.id;

4.3 全外联结

若想要获取两个表格联结后的所有信息（无论是否满足联结条件），即全外联结。

只需要将表1与表2的左联结结果与右联结结果，使用UNION合并，就得到了表1与表2的全外联结。

4.4 全补集

若想要获取表2与表1中独有的数据，可以在全外联结的基础上使用WHERE筛选空值。

4.5 各种联结如下图：

4.6 子句的书写顺序：

4.7 SQL代码的执行顺序

与书写顺序不同，SQL代码的执行顺序如上图所示：

查询的初期是加载FROM与JOIN的表格，然后使用ON进行筛选后联结；
然后是对上一步的结果进行WHERE筛选；
接着根据GROUP BY指定的列进行分组，随后执行聚合函数以及分组后数据的HAVING筛选；
数据筛选结束，根据SELECT指定的字段输出；
最后对输出的内容进行去重、排序与限行。

例题：商品子表 order_info 记录了每笔订单下购买的商品情况。请通过分组与统计，列出所有商品的销量。输出包括商品的编号、商品的名称、商品的销量（设定别名为 nums）

表commodity

id	sku_price	sku_name	category_id	brand_id
1003000001	488	神仙口红	300001	200001
1003000002	247	五彩缤纷粉底	300001	200001
1003000003	278	神仙眼影	300001	200001
1003000004	276	神仙遮瑕	300001	200001

表order_info

id	order_id	sku_id	current_price	transaction_price
400001	1000000001	1003000041	1126	1013.4
400002	1000000001	1003000050	19	13.3
400003	1000000002	1003000019	54	48.6
400004	1000000002	1003000076	320	240

代码实现：

SELECT commodity.id,sku_name,COUNT(order_id) AS nums
FROM commodity
LEFT JOIN order_info
ON commodity.id = order_info.sku_id
GROUP BY commodity.id;

例题：筛选出所有未下单的用户编号以及用户名称

表account_2c

id	name	gender	birthday	district
1004000001	朱桂芝	男	2000-10-05	澳门特别行政区
1004000002	覃丽丽	女	1994-08-30	河北省
1004000003	张娟	男	1999-10-14	新疆维吾尔自治区
1004000004	陈金凤	女	1999-09-13	江苏省

表orders

id	account_id	total_price	order_time	payment_method	status	addr_id
1000000001	1004000161	1026.7	2019-11-09 22:04:11	Wechat	true	1002000161
1000000002	1004000206	288.6	2020-11-16 22:04:11	Wechat	true	1002000206
1000000003	1004000054	1578.95	2020-11-11 21:25:36	Wechat	true	1002000054
1000000004	1004000237	605.7	2020-11-13 21:25:36	other	true	1002000237
1000000005	1004000194	NULL	2019-06-07 23:52:52	Alipay	false	1002000194

代码实现：

SELECT account_2c.id,name
FROM account_2c
LEFT JOIN orders
ON account_2c.id = orders.account_id
WHERE orders.order_time is NULL;

例题：一年中每种商品在不同时间段会有多个售卖价格，比如换季打五折等等。现需要计算每种商品的平均售价。计算公式为：平均售价 = 产品总价 / 销售的产品数量

编写SQL代码从表格prices和unitssold中查找计算每个商品的平均售价

其中prices表中每一行表示某个产品(product_id)在某一段时间(start_date ~ end_date)内的价格(price)。
unitssold表中每一行表示某个产品(product_id)的具体出售日期(purchase_date)，售卖个数(units)。最终结果以units表（product_id)分组排列。

表prices

product_id	start_date	end_date	price
1	2020-02-15	2020-02-28	5
1	2020-03-01	2020-03-22	24
2	2020-02-01	2020-02-20	15
2	2020-02-21	2020-03-31	30

表unitssold

product_id	purchase_date	units
1	2020-02-25	100
1	2020-03-03	15
2	2020-02-10	200
2	2020-03-22	30
3	2020-04-10	10

代码实现：

SELECT unitssold.product_id,SUM(price*units)/SUM(units) AS average_price
FROM unitssold
LEFT JOIN prices
ON (purchase_date BETWEEN start_date AND end_date) AND (unitssold.product_id = prices.product_id)
GROUP BY unitssold.product_id;