多表查询-5-自连接

自连接

语法

自连接查询, 可以是内连接查询, 也可以是外连接查询

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

自连接案例演示

0. 数据准备

   下面的SQL语句在前面章节应该已经有给出, 但是为了大家学习方便, 下面也一并给出

   CREATE TABLE employees
   (
       emp_id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',
       emp_name   VARCHAR(20)             NOT NULL COMMENT '员工姓名',
       gender     ENUM ('男','女')        NOT NULL COMMENT '性别',
       birth_date DATE                    NOT NULL COMMENT '出生日期',
       hire_date  DATE                    NOT NULL COMMENT '入职日期',
       salary     DECIMAL(10, 2) UNSIGNED NOT NULL COMMENT '薪资',
       dept_id    INT UNSIGNED            NOT NULL COMMENT '所属部门',
       manager_id INT UNSIGNED COMMENT '直属领导',
   
       CONSTRAINT fk_dept
           FOREIGN KEY (dept_id)
               REFERENCES departments (dept_id),
       CONSTRAINT fk_manager
           FOREIGN KEY (manager_id)
               REFERENCES employees (emp_id)
   ) ENGINE = InnoDB COMMENT ='员工信息表';
   
   -- 插入员工数据（含管理关系）
   INSERT INTO employees (emp_name, gender, birth_date, hire_date, salary, dept_id, manager_id)
   VALUES
   -- 总部管理层
   ('张总裁', '男', '1975-03-12', '2010-05-20', 80000.00, 1, NULL),
   
   -- 技术研发中心
   ('李技术总监', '男', '1980-11-05', '2015-08-15', 60000.00, 2, 1),
   ('王架构师', '男', '1985-07-22', '2016-03-10', 45000.00, 2, 2),
   ('赵工程师', '女', '1990-09-18', '2018-09-01', 35000.00, 2, 3),
   
   -- 产品研发部
   ('钱产品总监', '女', '1983-04-30', '2017-02-14', 55000.00, 3, 2),
   ('孙产品经理', '男', '1992-12-08', '2019-11-05', 38000.00, 3, 5),
   ('周UI设计师', '女', '1995-06-25', '2020-07-15', 32000.00, 3, 6),
   
   -- 测试部
   ('吴测试经理', '男', '1988-01-17', '2016-06-20', 42000.00, 4, 2),
   ('郑测试工程师', '女', '1993-08-11', '2019-04-22', 30000.00, 4, 8),
   ('王自动化工程师', '男', '1994-03-19', '2021-01-10', 33000.00, 4, 8),
   
   -- 销售中心
   ('林销售总监', '女', '1978-09-03', '2012-11-08', 65000.00, 5, 1),
   ('刘大区经理', '男', '1986-05-28', '2018-07-30', 48000.00, 5, 11),
   
   -- 华东销售部
   ('陈区域经理', '男', '1990-02-14', '2019-03-25', 40000.00, 6, 12),
   ('杨销售代表', '女', '1996-07-07', '2022-02-18', 28000.00, 6, 13),
   ('黄销售助理', '女', '1998-11-21', '2022-06-01', 25000.00, 6, 13),
   
   -- 华南销售部
   ('梁区域经理', '女', '1989-12-05', '2020-09-12', 42000.00, 7, 12),
   ('郭销售代表', '男', '1997-04-16', '2022-03-15', 29000.00, 7, 16),
   ('何销售助理', '女', '1999-01-30', '2022-08-20', 24000.00, 7, 16);
   

1. 查询与员工及其所属领导的名字

   这里我们需要使用到自连接, 将表中的emp_id字段与manager_id字段进行连接, 因为我们manager_id设置的外键绑定的是emp_id, 所以自连接的条件便是他们俩相等

   select e1.emp_name as `员工姓名`, e2.emp_name as `所属领导姓名`
   from employees e1
           join employees e2 on e1.manager_id = e2.emp_id;
   

   

   但是要注意, 从上面图片看只有17条数据, 但是我们总共有18个员工, 出现这个情况是因为表中有一个员工没有所属领导, 所以导致了匹配失败, 查询不到他的数据

   

2. 查询所有员工及其领导的名字, 如果员工没有领导, 也需要查询出来

   上面我们使用的是内连接自查询的方法查询出来的数据, 但是缺少了无领导的员工, 这里我们使用外连接, 将没有领导的员工数据也一并查出, 查询出来的数据应该是18条

   select e1.emp_name as `员工姓名`, e2.emp_name as `所属领导姓名`
   from employees e1
           left join employees e2 on e1.manager_id = e2.emp_id;