深入解析MySQL中的七种JOIN查询

create table t_employees(
    id int primary key,
    name varchar(20),
    dept_id int
);

create table t_departments(
    id int primary key,
    name varchar(20)
);

insert into t_departments(id, name) values(1, '设计部');
insert into t_departments(id, name) values(2, '开发部');
insert into t_departments(id, name) values(3, '测试部');

insert into t_employees(id, name, dept_id) values(1, '张三', 1);
insert into t_employees(id, name, dept_id) values(2, '李四', 2);
insert into t_employees(id, name, dept_id) values(3, '王五', 0);

为了方便说明，我们将t_employees表称为A表，t_departments表称为B表。

一、内连接（INNER JOIN）

SELECT * FROM t_employees e INNER JOIN t_departments d ON  e.dept_id = d.id;

二、左外连接（LEFT OUTER JOIN）

SELECT * FROM t_employees e LEFT JOIN t_departments d ON e.dept_id = d.id;

三、右外连接（RIGHT OUTER JOIN）

SELECT * FROM t_employees e RIGHT JOIN t_departments d ON e.dept_id = d.id;

四、全外连接（FULL OUTER JOIN）

SELECT * FROM t_employees e LEFT JOIN t_departments d ON e.dept_id = d.id UNION SELECT * FROM t_employees e RIGHT JOIN t_departments d ON e.dept_id = d.id;

五、左排除连接（LEFT EXCLUDING JOIN）

SELECT * FROM t_employees e LEFT JOIN t_departments d ON e.dept_id = d.id WHERE d.id is null;

六、右排除连接（RIGHT EXCLUDING JOIN）

SELECT * FROM t_employees e RIGHT JOIN t_departments d ON e.dept_id = d.id WHERE e.id is null;

七、双侧排除连接（OUTER EXCLUDING JOIN）

SELECT * FROM t_employees e LEFT JOIN t_departments d ON e.dept_id = d.id WHERE d.id is null
UNION
SELECT * FROM t_employees e RIGHT JOIN t_departments d ON e.dept_id = d.id WHERE e.id is null;