{"id":1107,"date":"2019-04-20T21:22:05","date_gmt":"2019-04-20T13:22:05","guid":{"rendered":"https:\/\/freesilo.com\/?p=1107"},"modified":"2019-04-20T21:22:05","modified_gmt":"2019-04-20T13:22:05","slug":"%e4%b8%80%e5%bc%a0%e5%9b%be%e7%9c%8b%e6%87%82-sql-%e7%9a%84%e5%90%84%e7%a7%8d-join-%e7%94%a8%e6%b3%95","status":"publish","type":"post","link":"https:\/\/freesilo.com\/?p=1107","title":{"rendered":"\u4e00\u5f20\u56fe\u770b\u61c2 SQL \u7684\u5404\u79cd join \u7528\u6cd5"},"content":{"rendered":"

\u4f5c\u8005\u4e28C.L. Moffatt<\/p>\n

http:\/\/www.codeproject.com\/Articles\/33052\/Visual-Representation-of-SQL-Joins<\/a><\/p>\n

\u4e0b\u56fe\u5c55\u793a\u4e86 LEFT JOIN\u3001RIGHT JOIN\u3001INNER JOIN\u3001OUTER JOIN \u76f8\u5173\u7684 7 \u79cd\u7528\u6cd5\u3002<\/p>\n

<\/p>\n

\u5177\u4f53\u5206\u89e3\u5982\u4e0b\uff1a<\/p>\n

1.INNER JOIN\uff08\u5185\u8fde\u63a5\uff09<\/p>\n

\"INNER_JOIN.png\"<\/p>\n

\r\nSELECT\r\nFROM\u00a0Table_A\u00a0A\r\nINNER\u00a0JOIN\u00a0Table_B\u00a0B\r\nON\u00a0A.Key\u00a0=\u00a0B.Key\r\n<\/pre>\n
2.LEFT JOIN\uff08\u5de6\u8fde\u63a5\uff09<\/p>\n
\"LEFT_JOIN.png\"<\/p>\n
\r\nSELECT \r\nFROM Table_A A\r\nLEFT JOIN Table_B B\r\nON A.Key = B.Key\r\n<\/pre>\n
3.RIGHT JOIN\uff08\u53f3\u8fde\u63a5\uff09<\/p>\n
\"RIGHT_JOIN.png\"<\/p>\n
\r\nSELECT \r\nFROM Table_A A\r\nRIGHT JOIN Table_B B\r\nON A.Key = B.Key\r\n<\/pre>\n
4.OUTER JOIN\uff08\u5916\u8fde\u63a5\uff09<\/p>\n
\"FULL_OUTER_JOIN.png\"<\/p>\n
\r\nSELECT \r\nFROM Table_A A\r\nFULL OUTER JOIN Table_B B\r\nON A.Key = B.Key\r\n<\/pre>\n
5.LEFT JOIN EXCLUDING INNER JOIN\uff08\u5de6\u8fde\u63a5-\u5185\u8fde\u63a5\uff09<\/p>\n
\"LEFT_EXCLUDING_JOIN.png\"<\/p>\n
\r\nSELECT  \r\nFROM Table_A A\r\nLEFT JOIN Table_B B\r\nON A.Key = B.Key\r\nWHERE B.Key IS NULL\r\n<\/pre>\n
6.RIGHT JOIN EXCLUDING INNER JOIN\uff08\u53f3\u8fde\u63a5-\u5185\u8fde\u63a5\uff09<\/p>\n
\"RIGHT_EXCLUDING_JOIN.png\"<\/p>\n
\r\nSELECT \r\nFROM Table_A A\r\nRIGHT JOIN Table_B B\r\nON A.Key = B.Key\r\nWHERE A.Key IS NULL\r\n<\/pre>\n
7.OUTER JOIN EXCLUDING INNER JOIN\uff08\u5916\u8fde\u63a5-\u5185\u8fde\u63a5\uff09<\/p>\n
\"OUTER_EXCLUDING_JOIN.png\"<\/p>\n
\r\nSELECT \r\nFROM Table_A A\r\nFULL OUTER JOIN Table_B B\r\nON A.Key = B.Key\r\nWHERE A.Key IS NULL OR B.Key IS NULL\r\n<\/pre>\n
Examples<\/strong>
\nSuppose we have two tables, Table_A and Table_B. The data in these tables are shown below:<\/p>\n
TABLE_A
\nPK Value
\n—- ———-
\n1 FOX
\n2 COP
\n3 TAXI
\n6 WASHINGTON
\n7 DELL
\n5 ARIZONA
\n4 LINCOLN
\n10 LUCENT<\/p>\n
TABLE_B
\nPK Value
\n—- ———-
\n1 TROT
\n2 CAR
\n3 CAB
\n6 MONUMENT
\n7 PC
\n8 MICROSOFT
\n9 APPLE
\n11 SCOTCH<\/p><\/blockquote>\n
The results of the seven Joins are shown below:<\/p>\n
\n
\r\n-- INNER JOIN\r\nSELECT A.PK AS A_PK, A.Value AS A_Value,\r\nB.Value AS B_Value, B.PK AS B_PK\r\nFROM Table_A A\r\nINNER JOIN Table_B B\r\nON A.PK = B.PK\r\n<\/pre>\n
A_PK A_Value B_Value B_PK
\n—- ———- ———- —-
\n1 FOX TROT 1
\n2 COP CAR 2
\n3 TAXI CAB 3
\n6 WASHINGTON MONUMENT 6
\n7 DELL PC 7<\/p>\n
(5 row(s) affected)\n<\/p><\/blockquote>\n
\n
\r\n-- LEFT JOIN\r\nSELECT A.PK AS A_PK, A.Value AS A_Value,\r\nB.Value AS B_Value, B.PK AS B_PK\r\nFROM Table_A A\r\nLEFT JOIN Table_B B\r\nON A.PK = B.PK\r\n<\/pre>\n
A_PK A_Value B_Value B_PK
\n—- ———- ———- —-
\n1 FOX TROT 1
\n2 COP CAR 2
\n3 TAXI CAB 3
\n4 LINCOLN NULL NULL
\n5 ARIZONA NULL NULL
\n6 WASHINGTON MONUMENT 6
\n7 DELL PC 7
\n10 LUCENT NULL NULL<\/p>\n
(8 row(s) affected)\n<\/p><\/blockquote>\n
\n
\r\n-- RIGHT JOIN\r\nSELECT A.PK AS A_PK, A.Value AS A_Value,\r\nB.Value AS B_Value, B.PK AS B_PK\r\nFROM Table_A A\r\nRIGHT JOIN Table_B B\r\nON A.PK = B.PK\r\n<\/pre>\n
A_PK A_Value B_Value B_PK
\n—- ———- ———- —-
\n1 FOX TROT 1
\n2 COP CAR 2
\n3 TAXI CAB 3
\n6 WASHINGTON MONUMENT 6
\n7 DELL PC 7
\nNULL NULL MICROSOFT 8
\nNULL NULL APPLE 9
\nNULL NULL SCOTCH 11<\/p>\n
(8 row(s) affected)\n<\/p><\/blockquote>\n
\n
\r\n-- OUTER JOIN\r\nSELECT A.PK AS A_PK, A.Value AS A_Value,\r\nB.Value AS B_Value, B.PK AS B_PK\r\nFROM Table_A A\r\nFULL OUTER JOIN Table_B B\r\nON A.PK = B.PK\r\n<\/pre>\n
A_PK A_Value B_Value B_PK
\n—- ———- ———- —-
\n1 FOX TROT 1
\n2 COP CAR 2
\n3 TAXI CAB 3
\n6 WASHINGTON MONUMENT 6
\n7 DELL PC 7
\nNULL NULL MICROSOFT 8
\nNULL NULL APPLE 9
\nNULL NULL SCOTCH 11
\n5 ARIZONA NULL NULL
\n4 LINCOLN NULL NULL
\n10 LUCENT NULL NULL<\/p>\n
(11 row(s) affected)\n<\/p><\/blockquote>\n
\n
\r\n-- LEFT EXCLUDING JOIN\r\nSELECT A.PK AS A_PK, A.Value AS A_Value,\r\nB.Value AS B_Value, B.PK AS B_PK\r\nFROM Table_A A\r\nLEFT JOIN Table_B B\r\nON A.PK = B.PK\r\nWHERE B.PK IS NULL\r\n<\/pre>\n
A_PK A_Value B_Value B_PK
\n—- ———- ———- —-
\n4 LINCOLN NULL NULL
\n5 ARIZONA NULL NULL
\n10 LUCENT NULL NULL<\/p>\n
(3 row(s) affected)\n<\/p><\/blockquote>\n
\n
\r\n-- RIGHT EXCLUDING JOIN\r\nSELECT A.PK AS A_PK, A.Value AS A_Value,\r\nB.Value AS B_Value, B.PK AS B_PK\r\nFROM Table_A A\r\nRIGHT JOIN Table_B B\r\nON A.PK = B.PK\r\nWHERE A.PK IS NULL\r\n<\/pre>\n
A_PK A_Value B_Value B_PK
\n—- ———- ———- —-
\nNULL NULL MICROSOFT 8
\nNULL NULL APPLE 9
\nNULL NULL SCOTCH 11<\/p>\n
(3 row(s) affected)\n<\/p><\/blockquote>\n
\n
\r\n-- OUTER EXCLUDING JOIN\r\nSELECT A.PK AS A_PK, A.Value AS A_Value,\r\nB.Value AS B_Value, B.PK AS B_PK\r\nFROM Table_A A\r\nFULL OUTER JOIN Table_B B\r\nON A.PK = B.PK\r\nWHERE A.PK IS NULL\r\nOR B.PK IS NULL\r\n<\/pre>\n
A_PK A_Value B_Value B_PK
\n—- ———- ———- —-
\nNULL NULL MICROSOFT 8
\nNULL NULL APPLE 9
\nNULL NULL SCOTCH 11
\n5 ARIZONA NULL NULL
\n4 LINCOLN NULL NULL
\n10 LUCENT NULL NULL<\/p>\n
(6 row(s) affected)\n<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"
\u4f5c\u8005\u4e28C.L. Moffatt http:\/\/www.codeproject.c…
Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-1107","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/1107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1107"}],"version-history":[{"count":5,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/1107\/revisions"}],"predecessor-version":[{"id":1128,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/1107\/revisions\/1128"}],"wp:attachment":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}