介绍
这是一篇阐述SQL JOINs的文章
背景
我是个不喜欢抽象的人,一图胜千言。我在网上查找了所有的关于SQL JOIN的解释,但是没有找到一篇能用图像形象描述的。
有些是有图片的但是他们没有覆盖所有JOIN的例子,有些介绍实在简单空白得不能看。所以我决定写个自己的文章来介绍SQL JOINs.
详细说明
接下来我将讨论七种你可以从两个关联表中获取数据的方法, 排除了交叉JOIN和自JOIN的情况。 七个JOINs的例子如下:
- INNER JOIN (内连接)
- LEFT JOIN (左连接)
- RIGHT JOIN (右连接)
- OUTER JOIN (外连接)
- LEFT JOIN EXCLUDING INNER JOIN (左连接排除内连接结果)
- RIGHT JOIN EXCLUDING INNER JOIN (右连接排除内连接结果)
- OUTER JOIN EXCLUDING INNER JOIN (外连接排除内连接结果)
为了这个文章更好的描述,我把5,6,7当作LEFT EXCLUDING INNER JOIN, RIGHT EXCLUDING INNER JOIN,OUTER EXCLUDING INNER JOIN来特别说明
有些人可能有不同意见: 5,6,7不是真正的两个表的JOIN; 但是为了方便理解,我仍然把这些作为JOINs, 因为你有可能会在每个查询中使用到这些 JOIN (排除一些有WHERE条件的记录)
INNER JOIN (内连接)
例子
INNER JOIN
SELECT t.tid, t.name, tc.tid AS tcid, tc.description
FROM teacher t
INNER JOIN teacher_card tc
ON t.tid = tc.tid
INNER关键字可以不写
lEFT JOIN
SELECT t.tid, t.name, tc.tid AS tcid, tc.description
FROM teacher t
LEFT OUTER JOIN teacher_card tc
ON t.tid = tc.tid
OUTER关键字可以不写
RIGHT JOIN
SELECT t.tid, t.name, tc.tid AS tcid, tc.description
FROM teacher t
RIGHT OUTER JOIN teacher_card tc
ON t.tid = tc.tid
OUTER关键字可以不写
OUTER JOIN
SELECT t.tid, t.name, tc.tid AS tcid, tc.description
FROM teacher t
FULL OUTER JOIN teacher_card tc
ON t.tid = tc.tid
这样写是不行的,MySQL不支持FULL OUTER JOIN
SELECT t.tid, t.name, tc.tid AS tcid, tc.description
FROM teacher t
LEFT JOIN teacher_card tc
ON t.tid = tc.tid
UNION
SELECT t.tid, t.name, tc.tid AS tcid, tc.description
FROM teacher t
RIGHT JOIN teacher_card tc
ON t.tid = tc.tid
LEFT EXCLUDING JOIN
SELECT t.tid, t.name, tc.tid AS tcid, tc.description
FROM teacher t
LEFT JOIN teacher_card tc
ON t.tid = tc.tid
WHERE tc.tid IS NULL
RIGHT EXCLUDING JOIN
SELECT t.tid, t.name, tc.tid AS tcid, tc.description
FROM teacher t
RIGHT JOIN teacher_card tc
ON t.tid = tc.tid
WHERE t.tid IS NULL
OUTER EXCLUDING JOIN
SELECT t.tid, t.name, tc.tid AS tcid, tc.description
FROM teacher t
FULL OUTER JOIN teacher_card tc
ON t.tid = tc.tid
WHERE t.tid IS NULL
OR tc.tid IS NULL
同理MySQL中不能写成如上形式,可改写为如下
SELECT t.tid, t.name, tc.tid AS tcid, tc.description
FROM teacher t
LEFT JOIN teacher_card tc
ON t.tid = tc.tid
WHERE tc.tid IS NULL
UNION
SELECT t.tid, t.name, tc.tid AS tcid, tc.description
FROM teacher t
RIGHT JOIN teacher_card tc
ON t.tid = tc.tid
WHERE t.tid IS NULL
参考博客
[1]https://www.cnblogs.com/xufeiyang/p/5818571.html
[2]http://kevingo.logdown.com/posts/255422-mysql-joins-methods