本文共 4767 字，大约阅读时间需要 15 分钟。

MySQL连接查询（多表连接查询：内连接，外连接，交叉连接详解）

6：多表连接查询

笛卡尔乘积：如果连接条件省略或无效则会出现解决办法：添加上连接条件

连接查询的分类：

1.按年代分类：			1）sql 92标准:仅仅支持内连接			2）sql 99标准 **（推荐使用）** ：支持内连接、外连接（左外 和 右外）、交叉连接2.按功能分类：			1）内连接：								等值连接								非等值连接								自连接			2）外连接：								左外连接								右外连接								全外连接			3）交叉连接

一、传统模式下的连接 ：等值连接——非等值连接

1.等值连接的结果 = 多个表的交集2.n表连接，至少需要n-1个连接条件3.多个表不分主次，没有顺序要求4.一般为表起别名，提高阅读性和性能

二、sql99语法：通过join关键字实现连接

含义：1999年推出的sql语法支持：等值连接、非等值连接 （内连接）外连接交叉连接语法：

select 字段，…

from 表1

【inner|left outer|right outer|cross】join 表2 on 连接条件

【inner|left outer|right outer|cross】join 表3 on 连接条件

【where 筛选条件】

【group by 分组字段】

【having 分组后的筛选条件】

【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

三、自连接

案例：查询员工名和直接上级的名称

sql99

SELECT e.last_name,m.last_nameFROM employees eJOIN employees m ON e.`manager_id`=m.`employee_id`;

sql92

SELECT e.last_name,m.last_nameFROM employees e,employees m WHERE e.`manager_id`=m.`employee_id`;

一、等值连接

1）多表等值连接的结果实际上为多个表的交集部分 2）n张表进行连接，则至少需要 n-1 个连接条件 3）多表的顺序没有要求 4） 一般需要为表起别名，因为可能同一个字段名，会在多张查询的表中存在。即可以为表取别名,区分多个重名的字段。为表使用别名后,只能使用别名去"select"! 5） 等值连接可以搭配排序（order by）、分组(group by)、筛选(where/and/like)来使用

# sql 92# 可以实现三表连接# 查询员工名、部门名、和所在的城市SELECT last_name,department_name,cityFROM employees e, departments d,locations lWHERE e.department_id = d.department_idAND d.location_id = l.location_idAND city LIKE 's%'ORDER BY department_name DESC;# 加分组条件# 查询每个城市的部门个数SELECT COUNT(*) 个数 , cityFROM departments d , locations lWHERE d.location_id = l.location_idGROUP BY city;

# sql 99# 1.等值连接# 查询员工名、部门名SELECT last_name,department_nameFROM employees eINNER JOIN departments dON e.department_id = d.department_id;# 查询名字中包含e的员工名和工种名SELECT last_name , job_title,j.job_idFROM employees eINNER JOIN jobs j on e.job_id = j.job_idWHERE e.last_name LIKE '%e%';# 查询部门个数>3的城市名和部门个数，（添加分组+筛选）SELECT city,COUNT(*) 部门个数FROM locations lINNER JOIN departments dON l.location_id = d.location_idGROUP BY cityHAVING 部门个数>3;# 查询部分的员工个数>3的部门名和员工个数，并按员工个数降序排序SELECT COUNT(*) 个数,department_nameFROM departments dINNER JOIN employees eON e.department_id = d.department_idGROUP BY department_nameHAVING COUNT(*) > 3ORDER BY COUNT(*) DESC;# 查询员工名、部门名、工种名，并按部门名降序排列(三表连接）SELECT last_name,department_name,job_titleFROM employees eINNER JOIN jobs j ON e.job_id = j.job_idINNER JOIN departments d ON e.department_id = d.department_idORDER BY department_name DESC;

sql 99 特点：1、也可添加排序、分组、筛选2、inner 可以省略3、 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读4、inner join连接 和sql92语法中的等值连接效果相同，都是查询多表的交集

二、非等值连接

表与表之间没有相同项（或有相同但是不能用），但是表之间某个或某些项直接存在着一定的关系。

比如说员工信息表，里面包含员工工资，另外一张工资等级表将工资进行等级划分，而员工的工资就可以根据工资等级表来划分为某个等级。

where 列名 BETWEEN … AND …

# sql 92# 非等值连接# 查询员工的工资和工资级别SELECT salary , grade_levelFROM employees e, job_grades gWHERE salary BETWEEN g.lowest_sal AND highest_salORDER BY g.grade_level;# AND g.grade_level = 'A';

# sql 99# 非等值连接# 查询员工的工资级别SELECT salary , grade_levelFROM employees eINNER JOIN job_grades gON e.salary BETWEEN g.lowest_sal AND g.highest_sal;# 查询每个工资级别的员工个数>20的个数，并按工资级别降序排列SELECT salary , grade_level , COUNT(*)FROM employees eINNER JOIN job_grades gON e.salary BETWEEN g.lowest_sal AND g.highest_salGROUP BY grade_levelHAVING count(*) > 20ORDER BY grade_level DESC;

三、自连接 :自己连接自己

自连接相当于等值连接，不同的是等值连接的项在不同的表单当中，而自连接的连接项在同一个表单中。单一表单中某项和另外一项存在着关联关系。

比如说员工信息表中，普通员工A的领导是B，而B本身也是员工，因此也包含着员工信息表中。所以A的manager_id就是B的employee_id

# sql 92# 自连接# 查询员工名 和 上级的名称SELECT e.employee_id , e.last_name , m.employee_id , m.last_nameFROM employees e ,employees mWHERE e.manager_id = m.employee_id;

# sql 99# 自连接# 查询员工的名字、上级的名字SELECT e.last_name , m.last_name FROM employees eINNER JOIN employees mON e.employee_id = m.manager_id;# 查询包含字符k的员工名字、上级的名字SELECT e.last_name , m.last_name FROM employees eINNER JOIN employees mON e.employee_id = m.manager_idWHERE e.last_name LIKE '%k%';

二、外连接

应用场景：用于查询一个表中有，但是另一个表中没有的记录。

特点：

1）外连接的查询结果为主表中的所有记录。如果从表中有和它匹配的（也就是连接条件成立），则显示匹配的值。如果从表中没有和它匹配的，则显示null。 外连接查询的结果 = 内连接查询的结果 + 主表中有而从表中没有的记录 2）左外连接，left join 左边的是主表 右外连接，right join 右边的是主表

# sql 99# 外连接# 查询男朋友 不在男神表中的 女神名USE girls;SELECT * FROM boys;# 左外连接SELECT b.name , bo.*FROM beauty bLEFT OUTER JOIN boys boON b.boyfriend_id = bo.idWHERE bo.id IS NULL;# 右外连接SELECT b.name , bo.*FROM boys boRIGHT OUTER JOIN beauty bON b.boyfriend_id = bo.idWHERE bo.id IS NULL;  # 查询哪个部门没有员工# 左外USE myemployeesSELECT d.* , e.employee_idFROM departments dLEFT OUTER JOIN employees eON d.department_id = e.department_idWHERE e.employee_id IS NULL;# 右外SELECT d.* , e.employee_idFROM employees eRIGHT OUTER JOIN departments dON d.department_id = e.department_idWHERE e.employee_id IS NULL;

3）左外和右外交换两个表的顺序时，可以实现同样的效果

4） 全外连接 = 内连接的结果 + 表1中有但表2中没有的结果 + 表2中有但表1中没有的结果

# 全外use girlsSELECT b.* ,BOTH.*FROM beauty bFULL OUTER JOIN boys bo ON b.boyfirend_id = bo.id;

5）交叉连接(笛卡尔积)

# 交叉连接(笛卡尔积)USE girlsSELECT b.* ,bo.*FROM beauty bCROSS JOIN boys bo;

常见连接类型总结：