对于SQL的Join,知道有outer的,有left的,Select出来的结果集如下
假设我们有两张表。
- student (Table A) 是左边的表。
id name
1 a
2 b
3 c
4 d
1 a
2 b
3 c
4 d
teacher (
Table B
)
是右边的表。
id name
1 e
2 a
3 f
4 c
1 e
2 a
3 f
4 c
其各有四条记录,其中有两条记录是相同的
1:
产生的结果集中,是A和B的交集
SELECT * FROM student
INNER JOIN teacher ON student.`name` = teacher.`name`
INNER JOIN teacher ON student.`name` = teacher.`name`
id name id name
1 a 2 a
3 c 4 c
![](http://oimagea4.ydstatic.com/image?keyfrom=reader&w=500&url=http%3A%2F%2Fcoolshell.cn%2Fwp-content%2Fuploads%2F2011%2F01%2FInner_Join.png)
2:
产生表A的完全集,而B表中匹配的则有值,没有匹配的则以null值取代
SELECT * FROM student
LEFT JOIN teacher ON student.`name` = teacher.`name`
id name id name
1 a 2 a
2 b null null
3 c 4 c
4 d null null
![](http://oimagea3.ydstatic.com/image?keyfrom=reader&w=500&url=http%3A%2F%2Fcoolshell.cn%2Fwp-content%2Fuploads%2F2011%2F01%2FLeft_Outer_Join.png)
3:
产生在A表中有而在B表中没有的集合
SELECT * FROM student
LEFT JOIN teacher ON student.`name` = teacher.`name`
WHERE teacher.`id` IS NULL
LEFT JOIN teacher ON student.`name` = teacher.`name`
WHERE teacher.`id` IS NULL
id name id name
2 b null null
4 d null null
2 b null null
4 d null null
![](http://oimagea3.ydstatic.com/image?keyfrom=reader&w=500&url=http%3A%2F%2Fcoolshell.cn%2Fwp-content%2Fuploads%2F2011%2F01%2FLeft_Out_Join_2.png)