sql多表连接查询与子查询

1. 连接查询

1.1 无连接规则

SELECT语句中不设置连接条件，FROM语句中填入多张表名
第一张表中每一行会和第二张表格中的所有行连接，
两张表以此可以得到笛卡尔积

1.2 WHERE 引导的有规则连接

FROM 表一，表二
WHERE 表一.表一字段=表二.表二字段

如果字段为表格独有，则可以不加表名

1.3 表名简化语句

FROM Ord AS a, Product AS b, Customer AS c
WHERE a.Pno=b.Pno, b.Cno=c.Cno

关键字 AS 可以省略

1.4 使用 INNER JOIN 语句查询

SELECT *（或字段列表）
FROM 表一 INNER JOIN 表名二 ON 连接规则1
INNER JOIN 表名三 ON 连接规则3
......
INNER JOIN 表名n ON 连接规则n

使用 INNER JOIN...ON 连接，可以使WHERE子句变简单，
该方式被称为内连接查询

实操上把每张表中相同的字段连一连就行了

2. 高级连接查询

2.1 自连接查询

Q: 查询张三所在院系中所有学生的信息
通常此问题需要两步：

1. 查询张三所在什么院系
2. 查询该院系的所有学生
   自连接查询可在一段语句中完成示例
   

SELECT 语句中如果仅用一个*，那么得到表会出现重复

2.2 内连接查询

1. 等值连接
   • 连接规则用=表示
   • SELLECT 语句中使用 * 号
2. 自然连接
   相比等值连接，自然连接 SELECT 语句仅列出需要的字段
3. 不等值连接
   ON 之后的连接规则涉及等号以外的运算符，比如 BETWEEN

2.3 外连接查询

2.3.1 左外连接

FROM t1 LEFT OUTER JOIN t2

关键字： LEFT OUTER JOIN
t1中的所有结果都会被包含进新表，而t2仅匹配记录会被包含

2.3.2 右外连接

与左外连接查询反之

2.3.3 全外连接

关键字： FULL OUTER JOIN
所有记录无论是否匹配都会被包含

2.4 交叉连接查询

其实就指[[#1.1 无连接规则|无连接规则]]，但是形式有两种

1. 逗号隔开表名
2. CROSS JOIN 隔开表名
   一种可能的用法：
   表一中存星期一到星期天，表二中存第一节课到第八节课
   交叉连接后可以得到一张一天八节课的课表

2.5连接查询中使用聚合函数

聚合函数也能用于多表连接查询：
多表连接后会产生NULL值，书中的例子通过 COUNT 筛选过的所有NULL值来计算没参加考试的同学

3. UNION 组合查询

组合查询与连接查询比较

3.1 语法格式

3.2 与 OR 语句区别

UNION 会将结果集中相同的记录自动去掉，而 OR 会保留重复相同的记录

若在使用 UNION 时，不想删除重复值，可以使用 UNION ALL 替代即可

3.3 使用 UNION 规则

3.3.1 每个查询语句应有相同数量的字段

字段数不同时，可以用常量或者NULL补位。
上述性质对于 INTERSECT 或者 EXCEPT 语句也适用

3.3.2 每个查询语句相应的字段的类型必须相互兼容

相应位置字段类型不同时，可以用类型转换函数强制转换字符类型

3.4 排序组合查询的结果

• OREDER BY 语句只能出现一次，对最后的结果排序
• 如果没有 ORDER BY 语句时，结果会根据第一个 SELECT 子句中字段名列表升序排序

3.5 查询实例

Q:
A:
较为复杂的例子：

3.6 集合的并，差，交

3.6.1 并集UNION

并集操作用于合并两个集合中的元素，结果集中包含两个集合中的所有==不重复==元素。
[[并集示例]]
假设有两个表 employees 和 contractors，它们包含相同的列 name 和 department。我们想获取所有员工和合同工的名单，但不包括重复的记录

SELECT name, department
FROM employees
UNION
SELECT name, department
FROM contractors;

3.6.2 差集EXCEPT 或 MINUS

差集操作用于从==一个集合中减去另一个集合中存在的元素==，返回不在第二个集合中的元素。
[[差集示例]]
假设我们想找出所有只是员工而不是合同工的人员名单。
（这说明部分人既是员工又是合同工）

SELECT name, department
FROM employees
EXCEPT
SELECT name, department
FROM contractors;

3.6.3 交集INTERSECT

交集操作用于获取两个集合中都存在的元素。
[[交集示例]]
假设我们想找出既是员工又是合同工的人员名单。

SELECT name, department
FROM employees
INTERSECT
SELECT name, department
FROM contractors;

4. 子查询

4.1 使用返回单值的子查询

在 WHERE 语句中，使用诸如等于 = 或者不等于 <> 这样的关系运算符把子查询与主查询连接起来

4.2 子查询与聚合函数配合使用

这是使用子查询场景最多的情况。早期的版本中子查询效率优于内连接查询，而外连接查询还没出现。
下面给出一个实例：

4.5 子查询与 ALL 谓词

主查询中的某字段与子查询中最大的或者最小的比较
一个实例：[[查找工资大于部门ID为2的员工最高工资的所有员工]]

条件	描述
c > ALL(…)	c列中的值必须大于要评估为true的集合中的最大值。
c >= ALL(…)	c列中的值必须大于或等于要评估为true的集合中的最大值。
c = ALL(…)	c列中的值必须等于要评估为true的集合中的任何值。
…	…

这个也可以用集聚函数 MIN() MAX() 等实现

4.4 一些子查询的实例

[[用IN子查询实现]]

查询在2022年8月9日销售的产品信息

USE ShopDB
SELECT *
FROM dbo.product
WHERE Pno IN (SELECT Pno FROM dbo.Ord 
			  WHERE Odate='08/09/2022')

[[用EXISTS子查询实现]]

查询在2022年8月9日销售的产品信息

USE ShopDB
SELECT *
FROM dbo.product AS pro
WHERE EXISTS 
(SELECT 1 
FROM dbo.Ord AS ord 
WHERE Odate='08/09/2022' 
	AND ord.Pno=pro.Pno)

• 使用 EXISTS 时，一般在括号内的子查询里设置与主查询连接条件
• 事实证明语句会返回所有符合条件的存在项，所以子查询里可以设置为 SELECT 1 提高查询效率

[[用连接查询实现]]

查询在2022年8月9日销售的产品信息

USE ShopDB
SELECT pro.*
FROM dbo.product AS pro INNER JOIN dbo.Ord AS ord
ON pro.Pno=ord.Pno
WHERE ord.Odate='08/09/2022'

上述三种方式得到的结果都是一样的