对于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
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
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