MySQL-内连接、外连接、自连接以及联合查询的一个示例

首先是我们今天要用到的数据集：

create table dept(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
)comment '部门表';

create table emp(
    id  int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '姓名',
    age  int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID'
)comment '员工表';


alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
            (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),

            (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
            (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
            (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
            (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
            (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),

            (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
            (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
            (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),

            (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
            (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
            (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
            (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),

            (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
            (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
            (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
            (17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

1. 内连接：

-- 内连接演示
-- 1.每一个员工的姓名以及关联部门名称 隐式连接
-- 2.连接条件 emp.dept_id = dept.id
select emp.name , dept.name from emp ,dept where emp.dept_id = dept.id;
-- 起别名更简单
select e.name , d.name from emp e , dept d where e.dept_id  = d.id;
-- 3.显式连接
select e.name , d.name from emp e inner join dept d on e.dept_id = d.id;
-- 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;

2. 外连接：

-- 左外连接 表以及两表交集均查询 包含所有左表的数据 join左边是左表
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id
select e.* , d.name from emp e left outer join dept d on e.dept_id = d.id;

-- 右外连接 join右边是右表
select d.* , e.* from emp e right outer join dept d on e.dept_id = d.id;

3. 自连接：

-- 查询员工以及所属领导的名字
select *from emp a , emp b where a.managerid = b.id;

-- 查询所有员工及其领导的名字没有领导也要查询出来
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

4.联合查询：

--  联合查询  将几次查询的结果联合起来 要查询相同的字段
select * from emp where emp.salary < 5000
union all
select * from emp where emp.age > 50;
--  联合查询  将几次查询的结果联合起来并去重 要查询相同的字段
select * from emp where emp.salary < 5000
union
select * from emp where emp.age > 50;