07、多表查询

目录

1、内连接

1.1 隐式内连接

1.2 显式内连接

2、外连接

2.1 左外连接

2.2 右外连接

3、自连接

4、联合查询

5、子查询

5.1 标量子查询

5.2 列子查询

5.3 行子查询

5.4 表子查询

---

1、内连接

概念：相当于查询A、B表交集的部分数据

1.1 隐式内连接

语法：SELECT 字段列表 FROM 表1,表2 WHERE 条件

-- 例：查询每一个员工的姓名 及关联的部门的名称(隐式内连接的实现)
SELECT emp.name,dept.name from emp,dept WHERE emp.dept_id = dept.id

1.2 显式内连接

语法：SELECT 字段列表 FORM 表1 [INNER] JOIN 表2 ON 连接条件

-- 例：查询每一个员工的姓名 及关联的部门的名称(显式内连接的实现)
SELECT emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id

2、外连接

2.1 左外连接

概念：查询左表所有数据 以及两张表交集的部分数据

语法：SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...

SELECT emp.name,dept.name '部门名称' FROM emp LEFT OUTER JOIN dept ON emp.dept_id = dept.id

2.2 右外连接

概念：查询右表所有数据 以及两张表交集的部分数据

SELECT emp.name, dept.* FROM emp RIGHT OUTER JOIN dept ON emp.dept_id = dept.id

3、自连接

概念：当前表与自身的连接查询，自连接必须使用表别名 可以是内连接查询 也可以是外连接查询

语法：SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件

-- 查询员工所属领导的信息
SELECT a.name '员工',b.name '领导' from emp a JOIN emp b on a.managerid = b.id

4、联合查询

概念： 就是把多次查询的结果合并起来 行程一个新的查询结果集

语法：SELECT 字段列表 FROM 表A... UNION[ALL] SELECT 字段列表 FROM 表B...

-- 将薪资低于5000的员工和年龄大于50的员工全部查找出来
 SELECT * FROM emp WHERE salary < 5000 UNION ALL SELECT * FROM emp WHERE age > 50

-- 去掉ALL可以去重
 SELECT * FROM emp WHERE salary < 5000 UNION SELECT * FROM emp WHERE age > 50

注意：

1. 条件字段列表需要保持一致，字段类型也要保持一致
2. UNION ALL 会将全部数据合并在一起 UNION后会对合并的数据去重

5、子查询

概念：SQL语句中嵌套SELECT语句 称为嵌套查询 又称子查询

语法：SELECT * FROM t1 WHERE column1=(SELECT column1 FROM t2)

5.1 标量子查询

概念：子查询结果为单个值

常用操作符：=、<>、>、>=、< 、<=

-- 查询研发部的所有员工信息
-- a. 查询"销售部" 部分ID
SELECT id FROM dept WHERE name = '研发部'
-- b. 根据销售部部门id 查询员工信息
SELECT * FROM emp WHERE dept_id = 1
-- c. 结合
SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部')

5.2 列子查询

概念：子查询结果为一列

常用操作符：IN、NOT IN、ANY、SOME、ALL

-- 查询“研发部” 和 “市场部” 的所有员工信息
-- a. 查询“销售部” 和 “市场部” 的部门id
SELECT id FROM dept WHERE name = '研发部' OR name = '市场部'
-- b. 根据部门ID，查询员工信息
SELECT * FROM emp WHERE dept_id IN(1,2)
-- c. 结合
SELECT * FROM emp WHERE dept_id IN((SELECT id FROM dept WHERE name = '研发部' OR name = '市场部'))

5.3 行子查询

概念：子查询结果为一行

常用操作符：=、<>、IN、NOT、IN

-- 查询与"晓飞"的薪资及直属领导相同的员工信息
-- a. 查询"晓飞"的薪资与直属领导
SELECT salary,managerid FROM emp WHERE name='晓飞'

-- b. 查询与"晓飞"的薪资及直属领导相同的员工信息
SELECT * FROM emp WHERE (salary,managerid) = (SELECT salary,managerid FROM emp WHERE name='晓飞')

5.4 表子查询

概念：子查询结果为多行多列

常用操作符：IN

-- 例：查询与“赵四”，“王炸” 的职位和薪资相同的员工信息
-- a. 查询赵四和王炸的职位和薪资
SELECT job,salary FROM emp WHERE name IN('赵四','王炸')
-- b. 查询与“赵四”，“王炸” 的职位和薪资相同的员工信息
SELECT * FROM emp WHERE (job,salary) IN (SELECT job,salary FROM emp WHERE name IN('赵四','王炸'))

-- 例：查询入职日期是“2004-01-01”之后的员工信息 及其部门信息
-- a. 查询入职日期是“2004-01-01”之后的员工信息
SELECT * FROM emp WHERE entrydate > '2004-01-01'
-- b. 查询入职日期是“2004-01-01”之后的员工信息 及其部门信息
SELECT e.*,d.* FROM (SELECT * FROM emp WHERE entrydate > '2004-01-01') e LEFT JOIN dept d ON e.dept_id = d.id