MySQL：多表查询

---

一、内连接

内连接查询的是两张表的交集

隐式连接

SELECT 字段列表 FROM 表1,表2 WHERE 查询条件;

显示连接

SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 连接字段;

二、外连接

查询左表或者右表的数据 包含两种表的中间部分。

左外连接

SELECT 字段列表 FROM 表1 LEFT JOIN 表2 ON 连接字段;

右外连接

SELECT 字段列表 FROM 表1 RIGHT JOIN 表2 ON 连接字段;

三、自连接

自连接查询，可以是内连接查询，也可以是外连接查询

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件;

四、联合查询

对于UNION[ALL]查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...
UNION[ALL]
SELECT 字段列表 FROM 表B ...;

对于联合查询多张表的字段与字段类型需要保持一致。
UNION ALL 是对查询结果直接合并，UNION是对查询结果合并去重。

五、子查询

SQL语句嵌套SELECT语句

SELECT * FROM t1 WHERE column1=(SELECT * FROM t2);

子查询分类：

• 标量子查询：子查询结果是一个值
• 列子查询：子查询结果是一列
• 行子查询：子查询结果是一行
• 表子查询：子查询结果为多行多列

子查询的位置：WHERE之后、FROM之后、SELECT之后。

5.1 标量子查询

子查询的结果是单个值（数字、字符串、日期等）常用的符号=、<>、>、>=、<、<=等

SELECT * FROM tb_user WHERE dept_id=(SELECT id FROM tb_dept WHERE name='销售部');

SELECT * FROM tb_user WHERE entrydate >= (SELECT entrydate FROM tb_user WHERE name='张无忌'); 

5.2 列子查询

子查询的结果是一列或多行数据，常用的符号：IN、NOT IN、ANY、SOME、ALL。

操作符	描述
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY一样，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须相等

-- 查询市场部与销售部所有的员工信息
SELECT * FROM tb_user WHERE dept_id IN(SELECT id FROM tb_dept WHERE name='销售部' OR name='市场部');

-- 查询比财务部所有员工工资都高的员工信息
SELECT * FROM tb_user WHERE salary > ALL(SELECT salary FROM tb_user WHERE dept_id=(SELECT id FROM tb_dept WHERE name='财务部'));

-- 查询比研发部其中任意一人工资高的员工信息
SELECT * FROM tb_user WHERE salary > ANY(SELECT salary FROM tb_user WHERE dept_id=(SELECT id FROM tb_dept WHERE name='研发部'));

5.3 行子查询

子查询的结果是一行可以是多列，常用的符号：=、<>、IN、NOT IN

-- 查询与张无忌薪资和直属领导相同的员工信息
SELECT * FROM tb_user WHERE (salary, managerid) = (SELECT salary, managerid FROM tb_user WHERE name='张无忌');

5.4 表子查询

子查询的返回结果是多行多列。常用的符号：IN。常用于将查询结果当作一个临时表与其他表进行连查

-- 查询与“宋远桥”、“鹿仗客”的职位和薪资相同的员工数据
SELECT * FROM tb_user WHERE (job, salary) IN (SELECT job, salary FROM tb_user WHERE name='宋远桥' OR name='鹿仗客');

-- 查询入职时间在“2006-01-01”之后的员工以及对应的部门信息
SELECT u.*, d.* FROM (SELECT * FROM tb_user WHERE entrydate > '2006-01-01') e LEFT JOIN tb_dept d ON e.dept_id=d.id;