Oracle学习第七课-表连接及其应用

表连接

概念: 将多张表的数据合并成一张表,然后进行查询处理.[放在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+depart
2. 对连接后的表进行查询即可
    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_id
2. 查询信息
    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