obsidian/技术支持/MySql/康师傅笔记/多表JOIN连接+UNOIN.md

---

notion-id: 850ec1a5-5504-460c-91b3-8aa88ddccc

![[IMG-20260516155654659.png]]

7中JOIN的实现

左连接
Select <select_list>
FROM table A
LEFT JOIN table B
ON A.KEY = B.KEY

右连接
Select <select_list>
FROM table A
RIGHT JOIN table B
ON A.KEY = B.KEY

内连接
Select <select_list>
FROM table A
INNER JOIN table B
ON A.KEY = B.KEY

左连接【去除右表数据】
Select <select_list>
FROM table A
LEFT JOIN table B
ON A.KEY = B.KEY
Where B.KEY IS NULL

右连接【去除左表数据】
Select <select_list>
FROM table A
RIGHT JOIN table B
ON A.KEY = B.KEY
Where A.KEY IS NULL

#满内连接【mysql不支持】 可使用左连接+右连接【去除左表数据】 UNION ALL 右连接+左连接【去除右表数据】
Select <select_list>
FROM table A
FULL JOIN table B
ON A.KEY = B.KEY

#可使用UNION ALL来拼接
Select <select_list>
FROM table A
LEFT JOIN table B
ON A.KEY = B.KEY
UNION ALL
Select <select_list>
FROM table A
RIGHT JOIN table B
ON A.KEY = B.KEY
Where A.KEY IS NULL;

#满外连接【mysql不支持】 可使用左连接【去除右表数据】 UNION ALL右连接【去除左表数据】实现
Select <select_list>
FROM table A
FULL OUTER JOIN table B
ON A.KEY = B.KEY
WHERE A.KEY IS NULL
OR B.KEY IS NULL

#使用UNION ALL拼接