MySQL - 多表查询

多表查询是从多个表中获取数据的操作，通常用于建立表与表之间的关联。根据不同的需求，查询可以分为内连接、外连接和子查询。

1. 内连接查询

内连接查询返回两个表中匹配的记录，即交集部分数据。内连接可以是隐式或显式：

1.1 隐式内连接

SELECT tb_emp.name, tb_dept.name 
FROM tb_emp, tb_dept 
WHERE tb_emp.dept_id = tb_dept.id;

select 字段列表 from 表1 , 表2 where 条件 ... ;

1.2 显式内连接

SELECT tb_emp.name, tb_dept.name 
FROM tb_emp INNER JOIN tb_dept 
ON tb_emp.dept_id = tb_dept.id;

select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;

使用了别名的多表查询：

select emp.name , dept.name
from tb_emp emp inner join tb_dept dept
on emp.dept_id = dept.id;

注意事项:

一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

2. 外连接查询

外连接分为左外连接和右外连接：

2.1 左外连接

返回左表的所有记录，以及匹配的右表记录。

SELECT tb_emp.name, tb_dept.name 
FROM tb_emp LEFT JOIN tb_dept 
ON tb_emp.dept_id = tb_dept.id;

select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件
... ;

2.2 右外连接

返回右表的所有记录，以及匹配的左表记录。

SELECT tb_dept.name, tb_emp.name 
FROM tb_emp RIGHT JOIN tb_dept 
ON tb_emp.dept_id = tb_dept.id;

select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件
... ;

注意事项：

左外连接和右外连接是可以相互替换的，只需要调整连接查询时SQL语句中表的先后顺序就可以 了。而我们在日常开发使用时，更偏向于左外连接。

3. 子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

子查询可以书写的位置：

1. where之后         2. from之后         3. select之后

子查询是查询语句嵌套在另一个查询语句中，常用于复杂查询。根据返回结果的不同，子查询分为以下几类：

3.1 标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。

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

SELECT * 
FROM tb_emp 
WHERE dept_id = (SELECT id FROM tb_dept WHERE name = '教研部');

3.2 列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。

常用的操作符：

SELECT * 
FROM tb_emp 
WHERE dept_id IN (SELECT id FROM tb_dept WHERE name IN ('教研部', '咨询部'));

3.3 表子查询

返回多行多列，通常作为临时表使用。

SELECT e.*, d.* 
FROM (SELECT * FROM tb_emp WHERE entrydate > '2010-01-01') e 
LEFT JOIN tb_dept d 
ON e.dept_id = d.id;