sql多表查询之内连接、union (all)_union all sql

多表查询 - 多表连接

（原文：https://www.nowcoder.com/practice/55f3d94c3f4d47b69833b335867c06c1）
若一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询包括内连接、外连接和交叉连接等。
连接查询中用于连接两个表的条件称为连接条件或连接谓词。一般格式为：

内连接

内连接语法如下：

SELECT …
FROM 表名
[INNER] JOIN 被连接表
ON 连接条件
1
2
3
4

例1：查询每个学生及其班级的详细信息。

SELECT * FROM 学生表
INNER JOIN 班级表 ON 学生表.班号=班级表.班号
1
2

结果中有重复的列：班号。
例2.：改进，去掉例1中的重复列。

SELECT 学号, 姓名,班级表.班号, 班名 FROM 学生表 JOIN 班级表
ON 学生表.班号=班级表.班号
1
2

例3：查询重修学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。

SELECT 姓名, 课程号, 成绩
FROM 学生表 JOIN 成绩表
ON 学生表.学号 = 成绩表.学号
WHERE 状态 = '重修'
1
2
3
4

执行连接操作的过程

首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，
找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。 表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2， …
重复这个过程，直到表1中的全部元组都处理完毕为止。

表别名

可以为表提供别名，其格式如下：
<源表名> [ AS ] <表别名> ，“as”可加可不加，不过最好加上。
于是使用别名时例3可写为如下形式：

SELECT 姓名, 课程号, 成绩
FROM 学生表 S JOIN 成绩表 g 
ON S.学号 = g.学号
WHERE 状态 = ‘重修’
1
2
3
4

注：如果为表指定了别名，则查询语句中其他所有用到表名的地方都要使用别名
例4：查询所有学生的姓名、班名和系名。

SELECT 姓名,班名,系名
FROM 学生表 s JOIN 班级表 bjb
ON s.班号 = bjb.班号
JOIN 系表 xb ON bjb.系号 = xb.系号
1
2
3
4

例5：查询软件工程系所有学生的情况，要求列出学生姓名和所在的系。

SELECT 姓名, 系名
FROM 学生表 s JOIN 班级表 bjb
ON s.班号 = bjb.班号
JOIN 系表 xb ON bjb.系号 = xb.系号
WHERE 系名= '软件工程系
1
2
3
4
5

例6：有分组的多表连接查询。查询每个班的学生的考试平均成绩。

SELECT 班号, 
AVG(成绩) AS 班平均
FROM 学生表 S JOIN 成绩表 g
ON S.学号 = g.学号
GROUP BY 班号
1
2
3
4
5

例7：有分组和行过滤的多表连接查询。查询21226P班每门课程的选课人数、平均成绩、最高成绩和最低成绩。

SELECT 课程号, COUNT(*) AS Total, 
AVG(成绩) AS Avg成绩, 
MAX(成绩) AS Max成绩, 
MIN(成绩) AS Min成绩
FROM 学生表 S JOIN 成绩表 g
ON S.学号 = g.学号
WHERE 班号 = '21226P'
GROUP BY 课程号
1
2
3
4
5
6
7
8

题目

1.这里
2种方案：
1）多表连接查询

select t1.device_id,t1.question_id,t1.result FROM
question_practice_detail as t1 inner JOIN user_profile as t2 # 不加inner也ok,因为join默认是inner join
on t1.device_id=t2.device_id
WHERE t2.university="浙江大学";
1
2
3
4

2）子表查询

select device_id, question_id, result
from question_practice_detail
WHERE device_id IN
(select device_id from user_profile WHERE university="浙江大学");  # 这必须加括号
1
2
3
4

2.这里
方法1：

select 
    university,
    difficult_level,
    round(count(t2.question_id) / count(DISTINCT t1.device_id), 4) as avg_answer_cnt
from 
    user_profile as t1
    inner JOIN question_practice_detail as t2 on t1.device_id=t2.device_id and t1.university="山东大学" # 注意这儿不能用where。
    inner JOIN question_detail as t3 on t2.question_id=t3.question_id 
GROUP BY 
    difficult_level;
1
2
3
4
5
6
7
8
9
10

方法2：

select 
    university,
    difficult_level,
    round(count(t2.question_id) / count(DISTINCT t1.device_id), 4) as avg_answer_cnt
from 
    user_profile as t1
    inner JOIN question_practice_detail as t2 on t1.device_id=t2.device_id 
    inner JOIN question_detail as t3 on t2.question_id=t3.question_id 
where # where应用在这儿
    t1.university="山东大学"
GROUP BY 
    difficult_level;   
1
2
3
4
5
6
7
8
9
10
11
12

方法3：

SELECT 
    t1.university,
    t3.difficult_level,
    COUNT(t2.question_id) / COUNT(DISTINCT(t2.device_id)) as avg_answer_cnt
from 
    user_profile as t1,
    question_practice_detail as t2,
    question_detail as t3
WHERE 
    t1.university = '山东大学'
    and t1.device_id = t2.device_id
    and t2.question_id = t3.question_id
GROUP BY
    t3.difficult_level;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

方法4：

select 
    university,
    difficult_level,
    round(count(t2.question_id) / count(DISTINCT t1.device_id), 4) as avg_answer_cnt
from 
    user_profile as t1
    inner JOIN question_practice_detail as t2 on t1.device_id=t2.device_id 
    inner JOIN question_detail as t3 on t2.question_id=t3.question_id 
GROUP BY 
    t1.university,difficult_level # 注意：要对t1.university使用having，前提是group by中存在t1.university
having 
    t1.university="山东大学"; # 这一步的前提是GROUP BY中包括t1.university
1
2
3
4
5
6
7
8
9
10
11
12

综上，多表查询中，若某列是所有表中的某一表所独有的，则在sql语句中可以不写明表名称。除此情况以外，比如某列在二个表（举个列子：表1和表2）中都有，则sql语句中若涉及到该列，可写为表1.col或表2.col。
例如，于是方法4就可重写为：

select 
    university,
    difficult_level,
    round(count(t2.question_id) / count(DISTINCT t2.device_id), 4) as avg_answer_cnt 
    # 由于列device_id是表t1、t2都有的，因此t2.device_id或t1.device_id都OK；同理，question_id列是表t2、t3都有的，因此t2.question_id或t3.question_id都OK.
from 
    user_profile as t1
    inner JOIN question_practice_detail as t2 on t1.device_id=t2.device_id 
    inner JOIN question_detail as t3 on t2.question_id=t3.question_id 
GROUP BY 
    university,difficult_level # 注意：要对university使用having，前提是group by中存在t1.university
having 
    university="山东大学"; # 这一步的前提是GROUP BY中包括university
1
2
3
4
5
6
7
8
9
10
11
12
13

3.union all的使用

select 
    device_id,gender,age,gpa
from 
    user_profile
where
    university="山东大学"

union all

select
    device_id, gender, age, gpa
from 
    user_profile
where gender='male';
1
2
3
4
5
6
7
8
9
10
11
12
13
14

不去重：union all
去重：union ，是默认去重的。where … or … 也是模式去重的。
本题若是不去重,则有2种写法，且结果一样：

select 
    device_id,gender,age,gpa
from 
    user_profile
where
    university="山东大学" or gender='male';
1
2
3
4
5
6

或

select 
    device_id,gender,age,gpa
from 
    user_profile
where
    university="山东大学"

union 

select
    device_id, gender, age, gpa
from 
    user_profile
where gender='male';
1
2
3
4
5
6
7
8
9
10
11
12
13
14