MySQL连接查询Join

分类：

按年代分类：
		 SQL92标准：仅支持内连接
		 SQL99标准【推荐】：支持内连接 + 外连接（左外和右外）+ 交叉连接

按功能分类：
		内连接：
				等值连接
				非等值连接
				自连接
		外连接：
				左外连接
				右外连接
				全外连接
		交叉连接

一、SQL92标准：

支持所有内连接：
1、等值连接：
#查询城市名中第二个字符为o的部门名和城市名：

select departmentName, cityName
from departments d, locations l
where d.locationId = l.locationId
and cityName like '_o%';

# 三表连接：
select lastName, departmentName, cityName
from employees e, departments d, locations l
where d.locationId = l.locationId
and cityName like 's%';

总结：
① 多表等值连接的结果为多表的交集部分；
② n表连接，至少需要n-1个连接条件；
③ 多表的顺序无要求；
④ 一般需要为表起别名；
⑤ 可以搭配排序、分组、筛选等使用。
2、非等值连接：
#查询员工的工资和工资级别：

select salary, salaryLevel
from employees e, salaryGrades g
where salary between g.lowestSalary and g.highestSalary;

3、自连接：
#查询员工名和上级的名称；

select e.employeeId, e.lastName, m.employeeId, m.lastName
from employees e, employees m
where e.managerId = m.employeeId

二、SQL99语法：

语法：

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类：

内连接（*）：innner
外连接：left 【outer】
		左外连接（*）：right 【outer】
		右外连接（*）：full 【outer】
		全外：full 【outer】
交叉连接：cross

（一）、内连接：

语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件；

分类：
等值连接、非等值连接、自连接。
特点：
① 可添加排序、分组、筛选；
② inner可以省略；
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读；
④ inner join连接与SQL92语法中的等值连接效果是一样的，都是查询多表的交集。
1、内连接：
#查询员工名、部门名

select lastName, departmentName
from departments d
inner join employees e
on e.departmentId = d.departmentId;

#查询名字中包含e的员工名和工种名（添加筛选）

select lastName, jobTitle
from employees e
inner join jobs j
on e.jobId = j.jobId
where e.lastName like  '%e%';

#查询部门个数>3的城市名和部门个数（添加分组+筛选）

select cityName, count(*) 部门个数
from departments d
inner join locations l
on d.locationId = l.locationId
group by cityName
having count(*) > 3;

#查询员工个数>3的部门名和员工个数，并按个数降序（添加排序）
① 查询每个部门的员工个数

select departmentName, count(*)
from employees e
inner join departments d
on e.departmentId = d.departmentId
group by departmentName

② 在①的基础上筛选员工个数>3的记录，并排序

select departmentName, count(*)
from employees e
inner join departments d
on e.departmentId = d.departmentId
group by departmentName
having count(*) > 3
order by count(*) desc;

#查询员工名、部门名、工种名，并按部门名降序（添加三表连接）

select lastName, departmentName, jobTitlesName
from employees e
inner join departments d
on e.departmentId = d.departmentId
inner join jobTitles j on e.jobId = j.jobId
order by departmentName desc;

2、非等值连接：
#查询员工的工资级别

select salary, gradeLevel
from employees e
join jobGrades g
on e.salary between g.lowestSalary and highestSalary;

#查询工资级别个数>20的个数，并按工资级别降序排序

select count(*), gradeLevel
from employees e
join jobGrades g
on e.salary between g.lowestSalary and highestSalary
group by gradeLevel
having count(*) > 20 
order by gradeLevve desc

3、自连接：
#查询员工的名称、上级的名称

select e.lastName, m.lastName
from employees e
join employees m
on e.managerId = m.employeeId

（二）、外连接：

用于查询一个表中有，另一张表中无的记录。
特点：
① 外连接的查询结果为主表中的所有记录：

	若从表中有和它匹配的记录，则显示匹配的值；
	若从表中没有和它匹配的记录，则显示null；
	外连接查询结果=内连接查询结果+主表中有而从表中没有的记录

② 左外连接，left join左边的是主表；右外连接，right join右边的是主表;
③ 左外和右外交换两个表的顺序，可实现同样的效果。
#引入：查询男朋友不在男生表中的女生名

#左外
select b.name, bo.*
from beauty b
left outer join boys bo
on g.boyfriendId = bo.id
where bo.id is null;

#右外
select b.name, bo.*
from boys bo
right outer join beauty b
on g.boyfriendId = bo.id
where bo.id is null;

（二）、全外连接（mysql不支持）：

全外连接 = 内连接结果 + 表1中有但表2中无 + 表2中有但表1中无的记录

（三）、交叉连接：

返回结果为两个表的笛卡尔乘积。

select b.*, bo.*
from beauty b
cross join boys bo;

总结：