表连接:
概念:将多张表的数据合并成一张表,然后进行查询处理。(放在from后面)
内连接:
关键词:[inner] join
语法: 表1 join 表2 on 表1.xx字段=表2.xx字段
--查询员工id,名字,薪资,部门id,部门名称?
思路:
1.表连接员工表和部门表,为一张表,确定连接依据:部门表.部门id=员工表.部门id
employees emp join departments dept on emp.department_id = dept.department_id;
2.对连接后的表进行查询
1 select e.employee_id,e.first_name,e.salary,e.department_id,d.department_name 2 from employees e join departments d on e.department_id = d.department_id;
特点:
- 1.合并左表中存在且右表中与之对应的数据
- 2.左表中存在,但右表中没有与之对应的数据,直接舍弃
外连接
左外连 (常用)
关键词: left [ outer ] join
语法: 表1 left join 表2 on 连接规则
-- 查询员工id,名字,薪资,部门id,部门名称 思路: 1.表连接
employees e left join departments d on e.department_id = d.department_id;
2.对连接后的表查询
1 select e.employee_id,e.first_name,e.salary,e.department_id,d.department_name 2 from employees e left join departments d on e.department_id = d.department_id;
特点:
1.左表中存在,且右表中有与之对应的数据,合并保留。
2.左表中存在,且右表中没有与之对应的数据,保留,不存在的数据补空
右外连(了解)
语法:表1 right [ outer ] join 表2 on 连接规则
特点:保留右表存在,但左表中不存在的数据
表连接特殊应用
--1.查询部门信息:id,名称,部门地址id,地址城市
1.连接 部门表和地址表
departments e left join locations c on e.location_id = c.location_id
2.对连接后的表查询
1 select d.department_id,d.department_name,d.location_id,c.city 2 from departments d left join locations c on e.location_id = c.location_id
--2.查询员工信息:工号,名字,薪资,部门,部门名称id,名称,部门地址id,地址城市
1.表连接 员工表 部门表
employees e left join departments d on e.department_id = d.department_id
2.连接后的表再连接 地址表
1 employees e left join departments d on e.department_id = d.department_id 2 left join locations c on d.location_id = c.location_id
3.查询
1 select 2 e.employee_id,e.first_name,e.salary,e.department_id,d.department_name,d.location_id,c.city 3 from 4 employees e left join departments d on e.department_id = d.department_id 5 left join locations c on d.location_id = c.location)id
--3.查询员工信息:工号,名字,薪资,该员工领导的id,领导的名字(重要),
一张表当2张表用
1.表连接
emoloyees e1 left join employees e2 on e1.manager_id = e2.employee_id
2.查询信息
1 select e1.employee_id,e1.first_name,e1.salary,e1.manager_id,e2.first_name 领导名字 2 from employees e1 left join employees e2 on e1.manager_id = e2.employee_id