连接查询
含义：又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询

• 笛卡尔乘积现象：表1有m行，表2有n行，结果为m*n行

发生原因：没有有效的连接条件

• 按年代分类：

sql92标准
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

• 按功能分类：

内连接：

• 等值连接
• 非等值连接
• 自连接
  • 外连接：
• 左外连接
• 右外连接
• 全外连接
  • 交叉连接

【sql92标准】

• 多表等值连接的结果为多表的交集部分
• n表连接，至少需要n-1个连接条件
• 多表的顺序没有要求
• 一般需要为表起别名
• 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

1、等值连接
案例一：查询女神名和对应的男神名

select NAME,boyName
from boys,beauty
where beauty.boyfriend_id=boys.id;

案例二：查询员工名和对应的部门名

select last_name,department_name
from employees,departments
where employees.'department_id'=departments.'department_id';

• ①、为表起别名
• 可以提高语句的简洁度，区分多个重名的字段
• 注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

案例三：查询员工名、工种号、工种名

select last_name,e.job_id,job_title
from employees e,jobs j
where e.'job_id'=j.'job_id';

• ②、加筛选条件

案例一：查询有奖金的员工名和部门名

select last_name,department_name
from employees e,departments d
where e.'department_id'=d.'department_id'
and e.'commission_pct' is not null;

案例二：查询城市名中第二个字符为o的对应的部门名和城市名

select department_name,city
from departments d,locations l
where d.'location_id'=l.'location_id'
and city like '_o%';

• ③、加分组条件

案例一：查询每个城市的部门个数

select count(*) 个数,city
from departments d,locations l
where d.'location_id'=l.'location_id'
group by city;

案例二：查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资

select department_name,d.manager_id,min(salary)
from departments d,employees e
where d.'departments_id'=e.'departments_id'
and commission_pct is not null
group by department_name,d.manager_id;

• ④、加排序

案例一：查询每个工种的工种名和员工的个数，并且按员工个数降序排序

select job_title,count(*)
from jobs j,employees e
where j.'job_id'=e.'job_id'
group by job_title
order by count(*) desc;

• ⑤、实现三表查询

案例一：查询员工名、部门名的所在的城市

select last_name,department_name,city
from employees e,departments d,locations l
where e.'department_id'=d.'department_id'
and e.'department_id'=l.'department_id';

2、非等值连接
案例一：查询员工的工资和工资级别

select salary,grade_level
from employees e,job_grades g 
where salary between g.'lowest_sal' and g.'highest_sal';

3、自连接
案例一：查询员工名和上级的名称

select e.last_name,e.manager_id,m.employee_id,m.last_name
from employees e,employees m
where e.'manager_id'=m.'employee_id';

例题：
1、显示员工表的最大工资，工资平均值

select max(salary),avg(salary)
from employees;

2、查询员工表的employee_id，job_id，last_name，按department_id降序，salary升序

select employee_id,job_id,last_name
from employees
order by department_id desc,salary asc;

3、查询员工表中的job_id包含a和e的，并且a在e的前面

select job_id
from jobs
where job_id like '%a%e%';

4、已知表student，里面有id（学号），name，gradeId（年级编号）
已知表grade，里面有id（年级编号），name（年级名）
已知表result，里面有id，score，studentNo（学号）
要求查询姓名、年级名、成绩

select s.name,g.name,score
from student s,grade g,result r
where s.'gradeId'=g.'id'
and s.'id'=r.'studentNo';

5、显示当前日期，以及去前后空格，截取子字符串的函数

select now();
select trim(字符 from ''); #去掉指定的字符
select substr(str,起始索引,字符长度)

【sql99标准】
1、语法

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

2、连接类型

• 内连接（*）：inner
• 外连接（左外:left 【outer】 右外：right 【outer】 全外：full 【outer】）
• 交叉连接：full 【outer】

一、内连接

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

• 1、等值连接
• 可以添加排序、分组、筛选
• inner 可以省略
• 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读

• inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

• 案例一：查询员工名、部门名

  select last_name,department_name
  from employees e
  inner join departments d
  on e.department_id=d.department_id;

• 【添加筛选】案例二：查询名字中包含e的员工名和工种名

  select last_name,job_title
  from employees e
  inner join jobs j
  on e.job_id=j.job_id
  where e.last_name like '%e%';

• 【添加分组+筛选】案例三：查询部门个数>3的城市名和部门个数

  select count(*) 部门个数,city
  from departmentss d
  inner join location l
  on d.location_id=l.location_id
  group by city
  having 部门个数>3

• 【添加排序】案例四：查询哪个部门的部门员工数≥3的部门名和员工个数，并按个数降序

  select count(*) 员工个数,department_name
  from departments d
  inner join employees e
  on d.department_id=e.department_id
  group by d.department_id
  having count(*)>=3
  order by count(*) desc;

• 【三表连接】案例五：查询员工名、部门名、工种名，并按部门名降序

  select last_name,department_name,job_title 
  from employees e
  inner join departments d on e.dpartment_id=d.department_id
  inner join jobs j on e.job_id=j.job_id
  group by department_name desc;

• 2、非等值连接

• 案例一：查询员工的工资级别

  select salary,grade_level
  from employees e
  join job_grades g
  on e.salary between g.lowest_sal and g.highest_sal;

• 案例二：查询每个工资级别个数>20的，并且按照工资级别降序排序

  select count(*) 个数,grade_level
  from employees e
  join job_grades g
  on e.salary between g.lowest_sal and g.highest_sal
  group by grade_level
  having count(*)>20
  order by grade_level desc;

• 3、自连接

• 案例一：查询员工的名字和上级的名字

  select e.last_name,m.last_name
  from employees e
  join employees m on e.manager_id=m.employee_id;

  二、外连接

• 一般用于查找一个表中有，但是另一给表中没有的纪录

• 特点：

1. 外连接的查询结果为主表中的所有记录，若从表中有和他匹配的，则显示匹配值，否则将会显示null
2. 即外连接的查询结果=内连接结果+主表中有而从表中没有的纪录
3. 左外连接，left join左边的是主表；右外连接，right join右边的是主表
4. 左外和右外交换两个表的顺序，可以实现同样的效果
5. 全外连接=内连接的结果+表1中有但表2中没有的+表2中有但是表1中没有的

• 1、左外连接与右外连接
• 案例一：查询男朋友不在男神表的女神名 ```sql select b.name,bo.*

左外连接

from beauty b left outer join boys bo

右外连接

from boys bo right outer join beauty b

on b.boyfriend_id=bo.id;

- 案例二：查询哪个部门没有员工
```sql
select d.*,e.employee_id
from departments d
left outer join employees e
on d.department_id=e.department_id
where e.department_id is not null;

• 2、交叉连接
• 相当于实现笛卡尔乘积

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

  例题：

案例一：查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有则用null填充

select b.id,b.name,bo.*
from beauty b
left outer join boys bo
on b.boyfriend_id=bo.id
where b.id>3;

案例二：查询哪个城市没有部门

select city,d.*
from departments d
right outer join locations l
on d.location_id=l.location_id
where d.department_id is null;

案例三：查询部门名为SAL或IT的员工信息

select e.*,d.department_name
from departments d
left join employees e
on d.department_id=e.department_id
where d.department_name in('SAL','IT');