一张图看懂 SQL 的各种 join 用法

作者丨C.L. Moffatt

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

具体分解如下：

1.INNER JOIN（内连接）

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

2.LEFT JOIN（左连接）

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

3.RIGHT JOIN（右连接）

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

4.OUTER JOIN（外连接）

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

5.LEFT JOIN EXCLUDING INNER JOIN（左连接-内连接）

SELECT  
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（右连接-内连接）

SELECT 
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（外连接-内连接）

SELECT 
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

Examples
Suppose we have two tables, Table_A and Table_B. The data in these tables are shown below:

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

The results of the seven Joins are shown below:

-- 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)