MySQL连接类型

MySQL连接类型

1、连接

1.1 数据构造

CREATE TABLE departments (  
    department_id INT PRIMARY KEY,  
    department_name VARCHAR(255)  
);  
  
CREATE TABLE employees (  
    employee_id INT PRIMARY KEY,  
    employee_name VARCHAR(255),  
    department_id INT
);

INSERT INTO departments (department_id, department_name) VALUES (1, 'Sales');  
INSERT INTO departments (department_id, department_name) VALUES (2, 'Engineering');  
INSERT INTO departments (department_id, department_name) VALUES (3, 'Marketing');  

INSERT INTO employees (employee_id, employee_name, department_id) VALUES (1, 'John Doe', 1);  
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (2, 'Jane Smith', 2);  
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (3, 'Bob Johnson', 1);
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (4, 'Tom', 4);

select * from departments;
select * from employees;

department_id	department_name
1	Sales
2	Engineering
3	Marketing

employee_id	employee_name	department_id
1	John Doe	1
2	Jane Smith	2
3	Bob Johnson	1
4	Tom	4

1.2 内连接(Inner Join)

匹配 2 张表中相关联的记录。

# join或inner join
# 使用inner join,使用where
select employees.employee_id,employees.employee_name,departments.department_id from employees inner join departments where employees.department_id = departments.department_id;
# 等价,使用inner join,使用where代替on
select employees.employee_id,employees.employee_name,departments.department_id from employees inner join departments on employees.department_id = departments.department_id;
# 等价,使用join
select employees.employee_id,employees.employee_name,departments.department_id from employees join departments where employees.department_id = departments.department_id;
# 等价,去掉join或者是inner join
select employees.employee_id,employees.employee_name,departments.department_id from employees,departments where employees.department_id = departments.department_id;
# 等价,使用cross join,条件只能用where不能用on
select employees.employee_id,employees.employee_name,departments.department_id from employees cross join departments where employees.department_id = departments.department_id;

employee_id	employee_name	department_id
1	John Doe	1
2	Jane Smith	2
3	Bob Johnson	1

1.3 左(外)连接(Left Join)

左外连接(Left Outer Join)：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示。

# left join或者left outer join
# left join
select employees.employee_id,employees.employee_name,departments.department_id from employees left join departments on employees.department_id = departments.department_id;
# left outer join
select employees.employee_id,employees.employee_name,departments.department_id from employees left outer join departments on employees.department_id = departments.department_id;

employee_id	employee_name	department_id
1	John Doe	1
2	Jane Smith	2
3	Bob Johnson	1
4	Tom

1.3 右(外)连接(Right Join)

除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示。

# right join或者right outer join
# right join
select employees.employee_id,employees.employee_name,departments.department_id from employees right join departments on employees.department_id = departments.department_id;
# right outer join
select employees.employee_id,employees.employee_name,departments.department_id from employees right outer join departments on employees.department_id = departments.department_id;

employee_id	employee_name	department_id
1	John Doe	1
3	Bob Johnson	1
2	Jane Smith	2
		3

1.4 全(外)连接(Full Join)

完整外部连接返回左表和右表中的所有行。

当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。

如果表之间有匹配行，则整个结果集行包含基表的数据值。

在MySQL中，full join 通常被称为 full outer join，但需要注意的是，MySQL 原生并不直接支持 full

outer join 语法。不过，你可以使用 union 和 left join 和 right join 的组合来模拟 full outer

join 的效果。

select employees.employee_id,employees.employee_name,departments.department_id from employees left join departments on employees.department_id = departments.department_id
union
select employees.employee_id,employees.employee_name,departments.department_id from employees right join departments on employees.department_id = departments.department_id;

employee_id	employee_name	department_id
1	John Doe	1
2	Jane Smith	2
3	Bob Johnson	1
4	Tom
		3

1.5 交叉连接(Cross Join)

没有WHERE子句的交叉连接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔

积结果集的大小。

# cross join
select employees.employee_id,employees.employee_name,departments.department_id from employees cross join departments;
# 等价
select employees.employee_id,employees.employee_name,departments.department_id from employees,departments;

employee_id	employee_name	department_id
1	John Doe	1
1	John Doe	2
1	John Doe	3
2	Jane Smith	1
2	Jane Smith	2
2	Jane Smith	3
3	Bob Johnson	1
3	Bob Johnson	2
3	Bob Johnson	3
4	Tom	1
4	Tom	2
4	Tom	3

1.6 总结

1、内连接与外连接的区别是什么?

内连接：只返回两个表中联结字段相等的数据。

外连接：返回包括左/右表中的所有记录和右/左表中联结字段相等的记录。

2、左外连接和右外连接的区别是什么?

左外连接也称左连接。以左表为基表，在FROM子句中使用关键字“LEFT OUTER JOIN”或关键字“LEFT JOIN”来连接

两张表。

右外连接也称右连接。以右表为基表，在FROM子句中使用关键字“RIGHT OUTER JOIN”或关键字“RIGHT JOIN”来

连接两张表。

3、概括

内连接就是取交集的部分。

左连接就是左表全部的数据加上交集的数据。

右连接就是右表全部的数据加上交集的数据。

交叉连接就是数据全都要。