Yvan - Blogging about life

SQL中关于join的知识

INNER JOIN

INNER JOIN 一般被译作内连接————求交集，两表共有
内连接查询能将左表（表 A）和右表（表 B）中能关联起来的数据连接后返回。

SELECT A.PK AS A_PK, B.PK AS B_PK,  
       A.Value AS A_Value, B.Value AS B_Value  
FROM Table_A A  
INNER JOIN Table_B B  
ON A.PK = B.PK;

LEFT JOIN

LEFT JOIN 一般被译作左连接，也写作 LEFT OUTER JOIN————类似差集，仅左表数据
左连接查询会返回左表（表 A）中所有记录，不管右表（表 B）中有没有关联的数据。在右表中找到的关联数据列也会被一起返回

SELECT A.PK AS A_PK, B.PK AS B_PK,  
       A.Value AS A_Value, B.Value AS B_Value  
FROM Table_A A  
LEFT JOIN Table_B B  
ON A.PK = B.PK;  

RIGHT JOIN

RIGHT JOIN 一般被译作右连接，也写作 RIGHT OUTER JOIN————类似差集，仅右表数据
右连接查询会返回右表（表 B）中所有记录，不管左表（表 A）中有没有关联的数据。在左表中找到的关联数据列也会被一起返回。

SELECT A.PK AS A_PK, B.PK AS B_PK,  
       A.Value AS A_Value, B.Value AS B_Value  
FROM Table_A A  
RIGHT JOIN Table_B B  
ON A.PK = B.PK;  

FULL OUTER JOIN

FULL OUTER JOIN 一般被译作外连接、全连接，实际查询语句中可以写作 FULL OUTER JOIN 或 FULL JOIN————全集
外连接查询能返回左右表里的所有记录，其中左右表里能关联起来的记录被连接后返回。

SELECT A.PK AS A_PK, B.PK AS B_PK,  
       A.Value AS A_Value, B.Value AS B_Value  
FROM Table_A A  
FULL OUTER JOIN Table_B B  
ON A.PK = B.PK;  

LEFT JOIN EXCLUDING INNER JOIN

返回左表有但右表没有关联数据的记录集————差集

SELECT A.PK AS A_PK, B.PK AS B_PK,  
       A.Value AS A_Value, B.Value AS B_Value  
FROM Table_A A  
LEFT JOIN Table_B B  
ON A.PK = B.PK  
WHERE B.PK IS NULL;  

RIGHT JOIN EXCLUDING INNER JOIN

返回右表有但左表没有关联数据的记录集————差集

SELECT A.PK AS A_PK, B.PK AS B_PK,  
       A.Value AS A_Value, B.Value AS B_Value  
FROM Table_A A  
RIGHT JOIN Table_B B  
ON A.PK = B.PK  
WHERE A.PK IS NULL;  

FULL OUTER JOIN EXCLUDING INNER JOIN

返回左表和右表里没有相互关联的记录集————对称差集

 SELECT A.PK AS A_PK, B.PK AS B_PK,  
       A.Value AS A_Value, B.Value AS B_Value  
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;  

avatar

references： SQL | Join (Inner, Left, Right and Full Joins)