关于sql joins的图形解说 .
关于sql joins的图形解说 .原文：http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
翻译：
我喜欢这种查询方式，现在让我们来看看它是如何工作的。假设我们有如下的两张表。表A在左边，表B在右边。我们将用下面的四条记录进行互相的组装查询。
view plaincopyprint?01.id name idname
02.-- ---- ------
03.1<span style="color:#cc0000;">Pirate</span> 1 Rutabaga
04.2Monkey 2 <span style="color:#cc0000;">Pirate
05.</span>3<span style="color:#cc0000;">Ninja</span> 3 Darth Vader
06.4Spaghetti4 <span style="color:#cc0000;">Ninja</span>
id name idname
-- ---- ------
1<span style="color:#cc0000;">Pirate</span> 1 Rutabaga
2Monkey 2 <span style="color:#cc0000;">Pirate
</span>3<span style="color:#cc0000;">Ninja</span> 3 Darth Vader
4Spaghetti4 <span style="color:#cc0000;">Ninja</span> 让我们用不同的方式根据name这个字段join这些表进行查询，来看看是否我们能得出一个能够和这些漂亮的图解相吻合的结论。
view plaincopyprint?01.SELECT * FROM TableA02.INNER JOIN TableB03.ON TableA.name = TableB.name04.05.idname id name06.------ -- ---- 07.1 Pirate 2 Pirate08.3 Ninja 4 NinjaSELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name
idname id name
------ -- ----
1 Pirate 2 Pirate
3 Ninja 4 Ninja
Inner join 查询得到的结果是匹配上表A和表B都相同的部分
view plaincopyprint?01.SELECT * FROM TableA02.FULL OUTER JOIN TableB03.ON TableA.name = TableB.name04.05.id name id name06.-- ---- -- ---- 07.1 Pirate 2 Pirate08.2 Monkey nullnull09.3 Ninja 4 Ninja10.4 Spaghettinullnull11.nullnull 1 Rutabaga 12.nullnull 3 Darth VaderSELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey nullnull
3 Ninja 4 Ninja
4 Spaghettinullnull
nullnull 1 Rutabaga
nullnull 3 Darth Vader
Full outer join 查询的结果是表A和表B的所有记录。,记录含有两边都匹配的.如果没有值相匹配的一边将会用null来填充.
view plaincopyprint?01.SELECT * FROM TableA02.LEFT OUTER JOIN TableB03.ON TableA.name = TableB.name04.05.idname id name06.------ -- ---- 07.1 Pirate 2 Pirate08.2 Monkey nullnull09.3 Ninja 4 Ninja10.4 SpaghettinullnullSELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
idname id name
------ -- ----
1 Pirate 2 Pirate
2 Monkey nullnull
3 Ninja 4 Ninja
4 Spaghettinullnull
Left outer join 查询结果是表A的完整记录, 记录含有和表B相匹配的记录. 没有匹配的记录那么右部将会用null来填充。
view plaincopyprint?01.SELECT * FROM TableA02.LEFT OUTER JOIN TableB03.ON TableA.name = TableB.name04.WHERE TableB.id IS null05.06.idname id name07.------ -- ---- 08.2 Monkey null null09.4 Spaghettinull nullSELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null
idname id name
------ -- ----
2 Monkey null null
4 Spaghettinull null
Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.
view plaincopyprint?01.SELECT * FROM TableA02.FULL OUTER JOIN TableB03.ON TableA.name = TableB.name04.WHERE TableA.id IS null 05.OR TableB.id IS null06.07.id name id name08.-- ---- -- ---- 09.2 Monkey nullnull10.4 Spaghettinullnull11.nullnull 1 Rutabaga12.nullnull 3 Darth VaderSELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null
id name id name
-- ---- -- ----
2 Monkey nullnull
4 Spaghettinullnull
nullnull 1 Rutabaga
nullnull 3 Darth Vader
Full outer joinTo produce the set of records unique to Table A and Table B, we perform the same full outer join, thenexclude the records we don't want from both sides via a where clause.
还有一个cross join查询，这个就不用图解了： view plaincopyprint?01.SELECT * FROM TableA02.CROSS JOIN TableBSELECT * FROM TableA
CROSS JOIN TableB这种连接查询是把每个记录都查出来的并且叠加匹配，结果12行，很多原始数据集合。如果你做这种匹配查询，你会发现为什么对于查询大数据时是一种危险的连接了。
谢谢分享
页:
[1]