表连接

概念: 将多张表的数据合并成一张表,然后进行查询处理.[放在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; ---emp+depart2. 对连接后的表进行查询即可    select e.employee_id,e.first_name,e.salary,e.department_id,d.department_name    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. 对连接后的表查询    select e.employee_id,e.first_name,e.salary,e.department_id,d.department_name    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,地址城市?  [两表连接]① 连接 部门表和地址表 ,规则: 部门表的location_id=地址表.location_id    departments e left join locations c on e.location_id = c.location_id② 对连接后的表查询    select d.department_id,d.department_name,d.location_id,c.city    from departments d left join locations c on d.location_id = c.location_id;      -- 2. 查询员工信息: 工号,名字,薪资,部门id,部门名称,部门所在地址id,所在城市city? [多表连接]① 表连接  员工表   部门表      employees e left join departments d on e.department_id = d.department_id② 连接后的表再连接 地址表    employees e left join departments d on e.department_id = d.department_id    left join locations c on d.location_id = c.location_id③ 查询    select e.employee_id,e.first_name,e.salary,e.department_id,d.department_name,d.location_id,c.city    from         employees e left join departments d on e.department_id = d.department_id        left join locations c on d.location_id = c.location_id​-- 3. 查询员工信息: 工号,名字,薪资,该员工领导的id,领导的名字?  [内连接 --重要]1. 表连接    employees e1 left join employees e2 on e1.manager_id = e2.employee_id2. 查询信息    select e1.employee_id,e1.first_name,e1.salary,e1.manager_id,e2.first_name as 领导名字    from employees e1 left join employees e2 on e1.manager_id = e2.employee_id