Seven SQL JOIN Types Explained with Example Queries

Author: C.L. Moffatt (source: CodeProject)

This article demonstrates seven common SQL JOIN usages—INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and three variations that exclude inner matches—using diagrams and complete example queries.

1. INNER JOIN

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

2. LEFT JOIN (左连接)

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

3. RIGHT JOIN (右连接)

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

4. FULL OUTER 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 (左连接-内连接)

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 (右连接-内连接)

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

7. FULL OUTER JOIN EXCLUDING INNER 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

(End of article)