22-基础SQL-多表查询-连接查询（内连接、外连接、自连接）

多表查询分类

 

 

案例：创建部门表 和 员工表（熟悉多表查询）

-- 部门表
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 "员工表";

-- 部门表数据
INSERT INTO dept(id,name)
VALUES
(1,"研发部"),
(2,"市场部"),
(3,"财务部"),
(4,"销售部"),
(5,"总经办")

-- 员工表数据
INSERT INTO emp(id,name,age,job,salary,entrydate,managerid,dept_id)
VALUES
(1, "员工1", 66, "总裁", 20000, "2000-01-01", null, 5),
(2, "员工2", 35, "经理", 12500, "2007-01-01", 1, 1),
(3, "员工3", 20, "开发", 8300, "2020-01-01", 2, 1),
(4, "员工4", 23, "开发", 10400, "2021-01-01", 2, 1),
(5, "员工5", 26, "开发", 11000, "2021-01-01", 3, 1)

 

连接查询-内连接

内连接 演示：

1） 查询每一个员工的姓名，及关联部门的名称（通过隐式内连接实现）

表结构 emp, dept

连接条件 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;

2）查询每一个员工的姓名，及关联部门的名称（通过显式内连接实现）

表结构 emp, dept

连接条件 emp.dept_id = dept.id

-- 显式内连接
SELECT emp.name, dept.name FROM emp INNER JOIN dept ON emp.dept_id = dept.id;

-- 表名太长时，可以给表起别名
SELECT e.name, d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id;

 

连接查询-外连接

 

 

外连接 演示：

1） 查询emp表的所有数据，和对应的部门信息（通过左外连接实现）

表结构 emp, dept

连接条件 emp.dept_id = dept.id

-- 左外连接
SELECT emp.*, dept.name FROM emp LEFT OUTER JOIN dept ON emp.dept_id = dept.id;

-- 简写
SELECT emp.*, dept.name FROM emp LEFT JOIN dept ON emp.dept_id = dept.id;

-- 表名太长时，可以给表起别名
SELECT e.*, d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;

 注意：左外连接，是以左表为主表，也就是说首先会查询左表的所有数据，再对应右表的数据。假设左边有的数据，右表没有，则会是以下情况

 2）查询dept表的所有数据，和对应的员工信息（通过右外连接实现）

表结构 emp, dept

连接条件 emp.dept_id = dept.id

-- 右外连接
SELECT dept.*, emp.* FROM emp RIGHT OUTER JOIN dept ON emp.dept_id = dept.id;

-- 简写
SELECT dept.*, emp.* FROM emp RIGHT JOIN dept ON emp.dept_id = dept.id;

-- 表名太长时，可以给表起别名
SELECT d.*, e.* FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id;

 注意：右外连接，是以右表为主表，也就是说首先会查询右表的所有数据，再对应左表的数据。假设右边有的数据，左表没有，则会是以下情况

 

在实际的开发过程中，一般是使用左外连接，这是因为右外连接也是可以通过左外连接实现的，就比如上面的案例

查询dept表的所有数据，和对应的员工信息（通过左外连接实现），只要调换一下emp和dept表的位置即可

SELECT dept.*, emp.* FROM dept LEFT JOIN emp ON dept.id = emp.dept_id;

 

连接查询-自连接

内连接 演示：

1）查询员工 及其 所属领导的名字（通过内连接的自连接实现，managerid）

表结构 emp

连接条件 e1.managerid = e2.id

SELECT e1.name as "员工", e2.name as "所属领导" FROM emp e1 JOIN emp e2 ON e1.managerid = e2.id;

2）查询所有员工 emp 及其领导的名字 emp，如果员工没有领导，也需要查询出来（通过左外连接的自连接实现，managerid）

表结构 emp

连接条件 e1.managerid = e2.id

SELECT e1.name as "员工", e2.name as "所属领导" FROM emp e1 LEFT JOIN emp e2 ON e1.managerid = e2.id;

 

这里提示一个经常容易出现的问题：

在做多表查询，或者查询的时候产生新的表的时候会出现这个错误

Every derived table must have its own alias（每一个派生出来的表都必须有一个自己的别名）

-- 这是一个左连接的案例
SELECT emp.*, dept.name as "dept_name" FROM emp LEFT JOIN dept ON emp.dept_id = dept.id;

当做多表查询的时候，我需要将上述外连接派生出来的表，作为一个新表进行查询，如果直接写会出现以下报错：

SELECT * FROM(
SELECT emp.*, dept.name as "dept_name" FROM emp LEFT JOIN dept ON emp.dept_id = dept.id);

这是因为派生出来的表，都必须有一个自己的别名

SELECT * FROM(
SELECT emp.*, dept.name as "dept_name" FROM emp LEFT JOIN dept ON emp.dept_id = dept.id) c;