关于SQL中join的各种用法总结

2019-4-17 / 0 评论 技术文章 / Mrxn

本文共计 3970 字,感谢您的耐心浏览与评论.

首先声明:文章来源于国外的 codeproject 我这里只是由于复习SQL的时候需要就Google搜索[可以用我个人搭建的Google搜索供大家搜索文章学习使用]了一下,找到这篇文章,再次做个简单的记录同时也方便以后的有缘人,如有侵权的地方还请来信注明,感谢原文的作者的勤劳付出,留下如此详细全面的关于SQL的join的用法。
codeproject是国外一个免费的可以公开自己写的代码与程序的优秀网站有点类似于GitHub只不过是社区版,在这个网站所有用户都可以发布自己写过的代码,程序,或者是详细的文档说明。比国内的cnblog、csdn都要好,如果要说缺点的话,就是全英文的,当然大部分还是比较容易理解的。但是codeproject也有中文区: https://www.codeproject.com/Forums/1580230/General-Chinese-Topics.aspx ,感兴趣的可以去注册玩。
我们先用一张图来看一下 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 等七种 join 相关的用法:
Visual_SQL_JOINS_orig.jpg
下面分开说明这七种用法,先说第一种:
1.INNER JOIN(内连接)
INNER_JOIN.png

这是最简单,最容易理解的Join,也是最常见的。此查询将返回左表(表A)中右表(表B)中都具有匹配记录的所有记录(共同拥有的相同的部分)。此Join用法的代码大致如下:
SELECT <select_list> 
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key
2.LEFT JOIN(左连接)
LEFT_JOIN.png
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。此Join用法的代码大致如下:
SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
3.RIGHT JOIN(右连接)
RIGHT_JOIN.png 
RIGHT JOIN 关键字从右表(table2)返回所有的行,即使左表(table1)中没有匹配。如果左表中没有匹配,则结果为 NULL。此Join用法的代码大致如下:
SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
4.OUTER JOIN(外连接)
FULL_OUTER_JOIN.png 
FULL OUTER JOIN 关键字只要左表(table1)和右表(table2)其中一个表中存在匹配,则返回行. FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。此Join用法的代码大致如下:
SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
5.LEFT JOIN EXCLUDING INNER JOIN(左连接-内连接)
LEFT_EXCLUDING_JOIN.png 
此查询将返回左表(表A)中与右表(表B)中的任何记录都不匹配的所有记录。此Join的编写如下:
SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL
6.RIGHT JOIN EXCLUDING INNER JOIN(右连接-内连接)
RIGHT_EXCLUDING_JOIN.png 
此查询将返回右表(表B)中与左表(表A)中的任何记录都不匹配的所有记录。此Join的编写如下:
SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL
7.OUTER JOIN EXCLUDING INNER JOIN(外连接-内连接)
OUTER_EXCLUDING_JOIN.png 

此查询将返回左表(表A)中的所有记录以及右表(表B)中都不匹配的所有记录。我还没有需要使用这种类型的Join,但是其他的类型,我经常使用。此Join的编写如下:
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
下面举一些例子:
假设我们有两个表,Table_A和Table_B。这些表中的数据如下所示:
TABLE_A
  PK Value
---- ----------
   1 FOX
   2 COP
   3 TAXI
   6 WASHINGTON
   7 DELL
   5 ARIZONA
   4 LINCOLN
  10 LUCENT

TABLE_B
  PK Value
---- ----------
   1 TROT
   2 CAR
   3 CAB
   6 MONUMENT
   7 PC
   8 MICROSOFT
   9 APPLE
  11 SCOTCH
这七种连接方式的结果如下所示:
-- INNER 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
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)
注:原文连接:https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

标签: 编程 代码 分享 SQL MySQL 数据库

转载:转载请注明原文链接 - 关于SQL中join的各种用法总结


0条回应:“关于SQL中join的各种用法总结”


发表评论

{view_code_no}