MySQL基础学习（四）——多表查询

目录

前提

一、多表查询分类

1、等值连接 和 非等值连接

（1）等值连接

（2）非等值连接

2、自连接 和 非自连接

（1）自连接

（2）非自连接

3、内连接 和 外连接

  3.1 内连接（INNER JOIN）

  3.2 外连接

二、UNION

三、七种JOINS的实现

---

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号 ” 进行关联。

前提

例1：查询location_id为2500的city，department_name，employee_id

这里要注意city属于location表，department_name属于department表，employee_id属于employees表

SELECT
	e.employee_id,
	d.department_name,
	l.city 
FROM
	employees e,
	departments d,
	locations l 
WHERE
	e.department_id = d.department_id 
	AND d.location_id = l.location_id 
	AND l.location_id = 2500

多表查询的主要在于几张表中的关联字段

 一般这一步都是从后往前读入，

先找到location_id为2500的数据，

再通过location的location_id关联department的location_id，

最后通过department的department_id关联到employees的department_id找到所有数据

一、多表查询分类

1、等值连接 和 非等值连接

（1）等值连接

通俗来说就是通过两张表的相同字段进行关联

SELECT e.employee_id 
FROM employees e,departments d
WHERE e.department_id=d.department_id 

（2）非等值连接

 通俗来说就是通过两张表的不同字段进行关联

SELECT e.employee_id ,e.salary
FROM employees e,jobs j
WHERE e.salary BETWEEN j.min_salary AND j.max_salary

2、自连接 和 非自连接

（1）自连接

通俗来说就是自己的两个分身进行连接

SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name 
FROM employees emp,employees mgr

（2）非自连接

这个就不用多说了。我们一直在用

3、内连接 和 外连接

3.1 内连接（INNER JOIN）

合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

使用JOIN...ON子句创建连接的语法

SELECT table1 .column , table2 .column ,table3 .column

FROM table1

JOIN table2 ON table1 和 table2 的连接条件

JOIN table3 ON table2 和 table3 的连接条件

内连接可以省略 INNER

SELECT last_name,department_name
FROM employees e 
JOIN departments d on e.department_id=d.department_id

3.2 外连接

两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的

行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。

如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

通俗来说就是左外连接左表大，右外连接右表大，看个人习惯，在下比较习惯使用左外连接

3.2.1 左外连接

SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
on e.department_id=d.department_id

这里的数据是107条

3.2.2 右外连接

SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
on e.department_id=d.department_id

这里的数据是122条

二、UNION

UNION：操作符返回两个查询的结果集的并集，去除重复记录

UNION ALL：操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

执行 UNION ALL 语句时所需要的资源比 UNION 语句少。

如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL 语句，以提高数据查询的效率。

例2：查询部门编号>90或邮箱包含a的员工信息

SELECT * FROM employees 
WHERE email LIKE '%a%' 
UNION
SELECT * 
FROM employees 
WHERE department_id>90;

三、七种JOINS的实现

需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN代替。