MySQL 多表查询 内连接 和 外连接

连接查询(多表查询)

查询的字段来自于多个表中，这个时候需要连接多个表进行查询。称为连接查询

连接查询的分类
1. 内连接：查询两个表的交集
	   ① 等值内连接
	   ③ 自连接		
2. 外连接
	   ① 左外连接
	   ② 右外连接


注意
	 1. 连接查询 需要建立连接条件
	 2. 如果没有连接条件 引发笛卡尔乘积现象

​

1.隐式内连接

语法：
   select 查询列表
   from 表1 别名1,表2 别名,...
   where 连接条件 
   and 分组前筛选
   group by 分组字段
   having 分组后筛选
   order by 排序字段 asc|desc
   limit 偏移量,个数

等值连接
案例1：查询员工的名字和其对应部门的名字
select e.*, d.department_name from employees e,departments d 
where e.department_id = d.department_id;

案例2：查询有奖金的员工的工种名、名字、邮箱、年薪
select e.last_name 员工名,e.email 邮箱,(salary+commission_pct*salary)*12 年薪,
j.job_title 工种名
from employees e,jobs j
where e.commission_pct is not null
and e.job_id = j.job_id;

案例3： 查询哪个城市的部门个数 > 2
select d.location_id,count(department_id),l.city from departments d,locations l 
where d.location_id = l.location_id
group by d.location_id
having count(department_id) > 2;

案例4：查询每个工种的工种名 和 平均薪资 并且按照平均薪资 降序
select j.job_title,avg(salary) from employees e,jobs j where e.job_id = j.job_id
group by j.job_title order by avg(salary) desc;

案例5： 查询员工的名字和其对应的部门名、工种名
select e.last_name,d.department_name,j.job_title from employees e, departments d, jobs j
where e.department_id = d.department_id
and  e.job_id = j.job_id;

自连接
案例6：查询每个员工名字和对应的领导的名字
select e1.last_name as 下属名字,e2.last_name as 上司名字 from employees e1,employees e2 
where e1.manager_id = e2.employee_id;

2.显式内连接

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

   
等值内连接
#案例1：查询员工的名字和其对应部门的名字
select e.last_name,d.department_name from employees e inner join departments d
on e.department_id = d.department_id;

#案例2：查询有奖金的员工的名字和对应部门的名字
select e.last_name,d.department_name from employees e inner join departments d
on e.department_id = d.department_id 
where commission_pct is not null;

#案例3：查询每个部门的部门名 和员工的最低薪资，只显示员工最低薪资都大于5000 的部门
select department_name,min(salary) from employees e inner join departments d
on e.department_id = d.department_id
group by d.department_name
having min(salary) > 5000;

#案例4：查询每个部门的部门名 和员工的最低薪资，只显示员工最低薪资都大于5000 的部门，
#并且按照最低薪资降序
select department_name,min(salary) from employees e inner join departments d
on e.department_id = d.department_id
group by d.department_name
having min(salary) > 5000
order by min(salary) desc;

#案例5：查询员工的名字和其对应领导的名字和薪资
select e1.last_name as 下属名字,e1.salary 下属薪资,
e2.last_name as 上司名字,e2.salary as 上司薪资 from employees e1 inner join employees e2
on e1.manager_id = e2.employee_id;

#案例6: 查询员工的名字和其对应部门的名字 以及对应工种名字
select e.last_name,d.department_name,j.job_title from employees e 
inner join departments d  on e.department_id = d.department_id
inner join jobs j on e.job_id = j.job_id;

3.外连接

语法：
  select 查询列表
  from 表1 别名1
  left|right 【outer】 join 表2 别名2
  on 连接条件
  where 分组前筛选
  group by 分组字段
  having 分组后筛选
  order by 排序字段 asc|desc
  limit 偏移量,个数


特点：
  1. 将主表的所有数据都会查询出来，对应表有对应数据 查询出来，没有对应数据 显示null
  2. 主表指 left 左表的表为主表 right右边的表为主表
  3. 查询结果 = 内连接的结果 + 主表中有 从表中没有的结果
  
  
#案例1：查询员工的名字和对应部门名字 (使用左外连接查询 员工表为主表) 


#外连接
select * from employees e LEFT JOIN departments d on e.department_id = d.department_id;

select * from employees e right JOIN departments d on e.department_id = d.department_id;

#案例2：查询哪个员工没有部门
select * from employees where department_id is null;

#案例3：查询哪些部门没有员工
select * from departments d  left join employees e
on d.department_id = e.department_id
where employee_id is null;

总结

多表查询总的来分 有两种形式

1.内连接
  内连接可以细分
  	1-1.隐式内连接
  		select * from A,B where 连接条件
  	1-2.显示内连接
  		select * from A inner join B on 连接条件
  		
   两种内连接对查询结果没有实质区别,只能显示符合条件的数据     

2.外连接
  外连接可以细分
  2-1.左外连接
  		select * from A left join B on 连接条件
  		A表作为主表，B表作为连接表(对应表).
  2-2.右外连接
  		select * from A right join B  on 连接条件
  		A表作为连接表，B表作为主表
  		
  主表显示所有数据，连接表显示对应数据，无对应数据使用NUll填充.
  两种外连接对查询结果有实质区别.