首页 小组 文章 相册 留言本 用户 搜索 我的社区 在线学堂 商城 购物车 支付钱包

[打卡]张景凯 内连接、外连接、满外连接

2024-04-17 15:20:23
0
145

#外连接的分类:左外连接、有外连接、满外链接

#满外连接
SELECT employee_id,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
#MYSQL不支持 FULL 进行满外连接

#UNION:会执行去重操作
#UNION ALL:不会执行去重操作
#在实际的开发当中,能用UNION ALL 就不用UNION


#中图:内连接
#SQL99
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
#SQL92
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;


#左上图:左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;


#右上图:右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;


#左中图
#左上图的基础上去掉内连接
SELECT employee_id,department_name
FROM employees e LEFT join departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;


#右中图
#右上图的基础上去掉内连接
SELECT employee_id,department_name
FROM employees e RIGHT join departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;


#左下图:满外链接
#方式1:左上图 UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

#左中图
#左上图的基础上去掉内连接
SELECT employee_id,department_name
FROM employees e LEFT join departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT join departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

SELECT 1+106+16;

#方式2:右上图 UNION ALL 左中图
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e LEFT join departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;


#右下图:左中 UNION ALL 右中 SELECT employee_id,department_name
FROM employees e LEFT join departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT join departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

评论
意见反馈