在计算机领域，数据库一直被誉为是基础软件皇冠上的明珠。程序猿们日常也是调侃自己是只会 CURD 增删查改打螺丝的无情机器，无论再高大上的软件，只要是持久化到数据库，CRUD操作的工具就是 SQL 了。

要想和数据库友好交流，就要对 SQL 语言非常熟练，能写得一手好 SQL 一定是项目组最靓的仔，今天就来学习 SQL 联表查询——JOIN。上面是一张非常经典的图，一图胜千言。可将图下载收藏起来。（来自：CL.Moffatt）

SQL JOIN 子句基于多表之间的共同字段，将多个表的行连接起来。常见 7 种的 SQL JOIN 类型：

1. INNER JOIN：内连接（交集）
2. LEFT JOIN：左外连接（left out join）
3. RIGHT JOIN：右外连接（right out join）
4. OUTER JOIN：全连接（并集|full join/union）
5. LEFT JOIN EXCLUDING INNER JOIN：左连接
6. RIGHT JOIN EXCLUDING INNER JOIN：右连接
7. OUTER JOIN EXCLUDING INNER JOIN：全外连接

INNER JOIN：内连接（交集）

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

实例：

-- join，显式内连接
select * from A join B on A.id = B.id;

-- inner join，显式内连接
select * from A inner join B on A.id = B.id;

-- 隐式内连接，两表的笛卡尔积再做where过滤
select * from A, B where A.id = B.id;

Left JOIN：左外连接（left out join）

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

实例：

-- left join
select * from A left join B on  A.id = B.id;

-- left outer join
select * from A left outer join B on  A.id = B.id;

Right JOIN：右外连接（right out join）

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

实例：

-- right join
select * from A right join B on  A.id = B.id;

-- right outer join
select * from A right outer join B on  A.id = B.id;

Outer JOIN：全连接（并集|full join/union）

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

实例：

-- oracle
select * from A full join B on A.id = B.id;

-- mysql
select * from A left join B on A.id = B.id
union
select * from A right join B on A.id = B.id;

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;

实例：

select * from A left join B on A.id = B.id where B.id 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;

实例：

select * from A right join B on A.id = B.id where A.id is null;

Outer Excluding 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;

实例：

-- oracle
select * from A full join B on A.id = B.id where A.id is null or B.id is null;

-- mysql
select * from A left join B on A.id = B.id where B.id is null;
union
select * from A right join B on A.id = B.id where A.id is null;

Tags：oracle 左连接