SQL语句

文章目录

1. 1. SQL的各种JOIN用法
   1. 1.1. Inner JOIN
   2. 1.2. Left JOIN
   3. 1.3. Right JOIN
   4. 1.4. Left Excluding JOIN
   5. 1.5. Right Excluding JOIN
   6. 1.6. Outer JOIN
   7. 1.7. Outer Excluding JOIN
2. 2. SQL 执行顺序
   1. 2.1. 参考

SQL的各种JOIN用法

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

Inner JOIN

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

Left JOIN

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

Right JOIN

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

Left Excluding JOIN

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

Right Excluding JOIN

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

Outer JOIN

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

# MySQL
SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
UNION
SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

Outer Excluding JOIN

# oracle
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

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

SQL 执行顺序

参考

https://www.runoob.com/w3cnote/sql-join-image-explain.html