当前位置：首页 > news >正文

SQL多表查询实战：7种JOIN详解

news 2026/6/1 0:21:47

多表查询（关联查询）是指从多个表中获取数据的查询操作，通过表之间的关联关系（一对一、一对多）将数据连接起来。例如员工表和部门表，通过部门编号进行关联。

1、笛卡尔积的理解

案例：查询员工的编号以及部门的名称

SELECT employee_id,department_name FROM employees,departments;

查询结果：出现2889条数据，明显出现错误（每个员工跟所有的部门进行匹配）

错误原因：

省略了多个表的连接条件（关联条件）
连接条件（关联条件）无效
所有表中的所有行互相连接

正确示例：在 WHERE 加入有效的连接条件

SELECT employee_id,department_name FROM employees,departments WHERE employees.department_id = departments.department_id;

2、多表查询的讲解

等值连接

（1）区分重复的列名

案例：查询员工的编号、部门的名称以及部门编号

SELECT employee_id,department_name,department_id FROM employees,departments WHERE employees.department_id = departments.department_id;

说明：department_id在员工表以及部门表中都有，如果没有指明此字段所在的表就会报错

从优化的角度考虑，多表查询时尽量每个查询字段都指明所在的表

SELECT employees.employee_id,departments.department_name,departments.department_id FROM employees,departments WHERE employees.department_id = departments.department_id;

（2）别名的使用

在上个案例中我们发现查询语句实在是太冗余，尤其是select字段，此时我们可以采用别名

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

使用别名可以简化查询。
列名前使用表名前缀可以提高查询效率。

！！！注意：如果采用了表的别名，在查询字段（SELECT）、过滤条件（WHERE）中只能采用别名进行替代不能再使用原来的表名，会报错。

（3）练习：查询员工的编号、姓名、部门名称以及城市

SELECT e.employee_id,e.last_name,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 操作符
连接 n 个表至少需要 n -1个连接条件

非等值连接

案例：查询员工姓名、薪资以及等级

连接条件：员工薪资在某个等级的最低与最高薪资之间（非等值）

SELECT e.last_name,e.salary,j.grade_level FROM employees e,job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接

相同表之间的自我引用叫做自连接

案例：查询员工编号、员工姓名及其管理者编号与姓名（都在employees表）

SELECT emp.employee_id,emp.last_name,mag.employee_id,mag.last_name FROM employees emp,employees mag WHERE emp.manager_id = mag.employee_id;

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。

然后两个表再进行内连接，外连接等查询。

非自连接

不同表之间的连接操作，以上示例大部分都是非自连接。

内连接

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

也就是严格执行过滤条件，上述案例都是内连接

外连接

外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

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

案例：查询所有的员工的姓名以及部门名称

在SQL92标准中使用（+）代表从表的位置，也就是说在左外或右外连接中（+）代表哪个是从表

# 左外连接 SELECT e.last_name,d.department_name FROM employees e,departments d WHERE e.department_id = d.department_id(+); # 右外连接 SELECT e.last_name,d.department_name FROM employees e,departments d WHERE e.department_id(+) = d.department_id;

MySQL 不支持92版本的🔗规则

在SQL99标准中使用JOIN ON子句建立连接，也可以解决内外连接的问题，MySQL 支持该规则。具体规则如下章所示

3、SQL99多表查询

（1）内连接实现

基本语法结构：

SELECT table1.column, table2.column,table3.column FROM table1 JOIN table2 ON table1 和 table2 的连接条件 JOIN table3 ON table2 和 table3 的连接条件

连接条件在 ON 子句之后，具有更佳的易读性
JOIN、INNER JOIN、CROSS JOIN都表示内连接

示例：查询员工姓名、部门名称以及城市

SELECT e.last_name,d.department_name,l.city FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id;

（2）外连接实现

外连接用 OUTER JOIN 实现
左外连接（LEFT OUTER JOIN）右外连接（RIGHT OUTER JOIN）全外连接（FULL OUTERR JOIN）
基本语法：

#实现查询结果是A SELECT 字段列表 FROM A表 LEFT OUTER JOIN B表 ON 关联条件 WHERE 等其他子句;

案例：查询所有的员工的姓名以及部门名称

# 左外连接 107条 SELECT last_name,department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id; # 右外连接 122条 SELECT last_name,department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;

查询结果：107条数据

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据
MySQL 不支持FULL JOIN，可以使用左外连接 UNION 右外连接实现

（3）UNION 的使用

使用 UNION 关键字将查询结果组合成单个结果集，可以考虑 UNION 或者 UNION ALL

两个表对应的列数和数据类型必须相同，并且相互对应
UNION 会执行去重操作，返回两个查询的并集
UNION ALL 会重复计算交集部分
执行UNION ALL语句时所需要的资源比UNION语句少，尽量使用UNION ALL 语句

语法格式：

SELECT column,... FROM table1 UNION [ALL] SELECT column,... FROM table2

案例：查询中国用户中男性的信息以及美国用户中年男性的用户信息

SELECT id,cname FROM t_chinamale WHERE csex='男' UNION ALL SELECT id,tname FROM t_usmale WHERE tGender='male';

4、七种SQL JOINS 实现

A---> employees 表 B---> departments 表

图1：内连接

# 106条 SELECT e.employee_id,d.department_name FROM employees e JOIN departments d ON e.department_id=d.department_id;

图2：左外连接

# 107条 SELECT e.employee_id,d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;

图3：右外连接

# 122条 SELECT e.employee_id,d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;

图4：左外连接-内连接

# 1条 SELECT e.employee_id,d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.department_id IS NULL;

图5：右外连接-内连接

# 16条 SELECT e.employee_id,d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.department_id IS NULL;

图6：满外连接（左外连接）+（右外连接-内连接）

# 123条 SELECT e.employee_id,d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id UNION ALL SELECT e.employee_id,d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.department_id IS NULL;

图7：（左外连接-内连接）+（右外连接-内连接）

# 17条 SELECT e.employee_id,d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.department_id IS NULL UNION ALL SELECT e.employee_id,d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.department_id IS NULL;

5、SQL99语法新特性

（1）自然连接（NATURAL JOIN）

自然连接类似于等值连接，会自动查询两张表的相同字段，然后进行等值连接。

# SQL92中写法 SELECT e.employee_id,e.last_name,d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id AND e.manager_id = d.manager_id; # SQL99中写法 SELECT e.employee_id,e.last_name,d.department_name FROM employees e NATURAL JOIN departments d;

（2）USING连接

使用 USING 连接指定数据表里的同名字段进行等值连接。

只能配合 JOIN 一起使用
要求两个关联字段在关联表中名称一致

# 写法一 SELECT e.employee_id,e.last_name,d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; # 写法二 USING SELECT e.employee_id,e.last_name,d.department_name FROM employees e JOIN departments d USING(department_id);

查看全文

http://www.cnnetsun.cn/news/69264.html