图解 SQL JOIN

2016-11-09 - 1,979 Views - 0 Goods - Nothing

介绍
这只是一个简单的图解SQL 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 JOIN,RIGHT EXCLUDING JOIN,和OUTER EXCLUDING JOIN。或许有人认为,5、6、7不连接两个表,但为了简单起见,我仍然加入这些,因为您使用SQL查询中加入这些(但是排除一些记录。WHERE条件)。

    Inner JOIN

    inner_join
    这是最简单、最明白,最常见的。该查询将返回所有记录在表(a)中的具有匹配记录的表(表B)。这种结合会被写成如下:


SELECT <select_list> 
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

Left JOIN

left_join
该查询将返回所有的记录在表(a)不论是否有这些记录具有匹配的表(表B)。它还将返回任何匹配记录的表。这种结合会被写成如下:


SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

Right JOIN

right_join
该查询将返回所有的记录的表(表B),而不管是否有任何匹配的记录的表(表A)。它还会返回任何匹配记录的表。这种结合会被写成如下:


SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

Outer JOIN

full_outer_join
这种加入也可以称为FULL OUTER JOIN或FULL JOIN。该查询将返回所有在记录从两个表中,记录从连接表(左)匹配记录的表,从两个表(表B)。这种结合会被写成如下:


SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key

Left Excluding JOIN

left_excluding_join
该查询将返回所有记录在表(a)在任何不匹配记录的表(表B)。这种结合会被写成如下:


SELECT  
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

Right Excluding JOIN

right_excluding_join
该查询将返回所有的记录的表(表B),在任何不匹配记录的表(表A)。这种结合会被写成如下:


SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL

Outer Excluding JOIN

outer_excluding_join
该查询将返回所有在记录在表中的表(左)和所有记录的表(表B)不匹配。我还不需要使用这种类型的连接,但是所有其它的,我相当频繁。这种结合会被写成如下:


SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

实例

假设有两个表,表A和表B_在这些表中的数据如下所示:


表A
  PK Value
---- ----------
   1 FOX
   2 COP
   3 TAXI
   6 WASHINGTON
   7 DELL
   5 ARIZONA
   4 LINCOLN
  10 LUCENT

表B_
  PK Value
---- ----------
   1 TROT
   2 CAR
   3 CAB
   6 MONUMENT
   7 PC
   8 MICROSOFT
   9 APPLE
  11 SCOTCH

加入的结果如下所示:


--INNERJOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
       B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7

(5 row(s) affected)

-- LEFT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
  10 LUCENT     NULL       NULL

(8 row(s) affected)

-- RIGHT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(8 row(s) affected)

-- OUTER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(11 row(s) affected)

-- LEFT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK
WHERE B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
  10 LUCENT     NULL       NULL
(3 row(s) affected)

-- RIGHT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(3 row(s) affected)


-- OUTER EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(6 row(s) affected)

visual_sql_joins_v2
上面说的不太好,你可以上维基百科获取更多信息

如果喜欢,可以转载请保留地址:https://www.songliguo.com/visual-representation-of-sql-joins.html

转载请注明原文链接:首页 -> 技术交流 -> 图解 SQL JOIN
  • 支付宝打赏
  • 微信打赏

China.BeiJing

如果说人生是自我编写的程序,那么青春就是其中意味深长的代码