Oracle 中 JOIN 方式总结

SQL JOIN 归纳起来有下面几种方式，下面一起来梳理一下这些概念。SQL JOIN其实是一个逻辑概念，像NEST LOOP JOIN、 HASH JOIN等是表连接的物理实现方式。

准备工作：

<1> 建两张表

--表一
create table T1
(
name varchar(255),
age int
);

--表二
create table T2
(
name varchar(255),
salary number(4,0)
);

<2> 插入数据

--表一
insert all
into T1 values('张三',25)
into T1 values('李四',24)
into T1 values('王五',34)
into T1 values('赵六',43)
into T1 values('孙七',28)
into T1 values('周八',37)
select * from dual;

--表二
insert all
into T2 values('张三',2500)
into T2 values('李四',2400)
into T2 values('王五',3400)
into T2 values('赵六',4300)
into T2 values('孙七',2800)
into T2 values('周八',3700)
select * from dual;

<3> 查看

--表一
select * from T1;

--表二
select * from T2;

结果如下：

准备完毕，进入正题。

一、内连接（INNER JOIN）

概念：INNER JOIN 它表示返回两个表或记录集连接字段的匹配记录。

SQL INNER JOIN 语法：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

注释：INNER JOIN 与 JOIN 是相同的。

韦恩图：

inner join 实例：

select T1.name,T2.name,T1.age,T2.salary
from T1
inner join T2
on T1.name=T2.name;

执行结果如下：

注释：INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 T1 表中的行在 T2 中没有匹配，则不会列出。

二、外连接：OUTER JOIN

<1>、左连接：LEFT JOIN

概念：LEFT JOIN 它表示从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
换句话说就是，列出左边表全部的，及右边表符合条件的，不符合条件的以空值代替。

SQL LEFT JOIN 语法：

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：LEFT JOIN 可写为 LEFT OUTER JOIN。

韦恩图：

LEFT JOIN 实例：

select T1.name,T2.name,T1.age,T2.salary
from T1
left join T2
on T1.name=T2.name;

执行结果如下：

注释：LEFT JOIN 关键字从左表（T1）返回所有的行，即使右表（T2）中没有匹配。

<2>、右连接：RIGHT JOIN

概念：从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。
换句话就是说，与左连接一样，列出右边表全部的，及左边表符合条件的，不符合条件的用空值替代。

SQL RIGHT JOIN 语法：

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：RIGHT JOIN 可写为 RIGHT OUTER JOIN。

韦恩图：

RIGHT JOIN 实例：

select T1.name,T2.name,T1.age,T2.salary
from T1
right join T2
on T1.name=T2.name;

执行结果如下：

注释：RIGHT JOIN 关键字从右表（T2）返回所有的行，即使左表（T1）中没有匹配。

<3>、全连接：FULL OUTER JOIN

概念：只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行，不符合条件的，则以空值代替。
换句话说，相当于结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

SQL FULL OUTER JOIN 语法：

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

韦恩图：

FULL OUTER JOIN 实例：

select T1.name,T2.name,T1.age,T2.salary
from T1
full outer join T2
on T1.name=T2.name;

执行结果如下：

注释：FULL OUTER JOIN 关键字返回左表（T1）和右表（T2）中所有的行。如果 “T1” 表中的行在 “T2” 中没有匹配或者 “T2” 表中的行在 “T1” 表中没有匹配，也会列出这些行。

总结一下先前使用的不同的 SQL JOIN 类型：

INNER JOIN：如果表中有至少一个匹配，则返回行
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行

三、笛卡尔积：CROSS JOIN

概念：CROSS JOIN就是笛卡尔乘积连接，不需要任何关联条件，实现A*B的结果集，其实这种SQL JOIN方式基本上只在理论上有意义，实际当中，很少有用的CORSS JOIN方式。
注意： cross join跟inner join、outer join等有所不同，不需要关键词on，因为它不需要相关字段做关联。

为避免数据量过大，现重新建两张小表：

--表三
create table T3
(
name varchar(255),
age int
);
--表四
create table T4
(
name varchar(255),
address varchar(255)
);
--表一插入数据
insert into T3 values('杜甫',58);
insert into T3 values('白居易',67);
insert into T3 values('王维',45);
--表二插入数据
insert into T4 values('杜甫','襄阳');
insert into T4 values('李白','长安');
insert into T4 values('王维','蒲州');

CROSS JOIN 语法：

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

注意：笛卡尔积用韦恩图是无法体现出来的。

CROSS JOIN 实例：

select * from T3 cross join T4;

执行结果如下：

四、自然连接：NATURAL JOIN

概念：NATURAL JOIN表示它在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

NATURAL JOIN 语法：

SELECT column_name(s)
FROM table1
NATURAL JOIN table2;

NATURAL JOIN的韦恩图,其实和内连接是一样的。如下所示：

NATURAL JOIN 实例：

select * from T1 natural join T2;

执行结果如下：

五、其他概念

<1>、SEMI JOIN

SEMI JOIN 多在子查询exists中使用，对外部row source的每个键值，查找到内部row source匹配的第一个键值后就返回，如果找到就不用再查找内部row source其他的键值了。

官方介绍案例如下:

Using Semijoins: Example

SELECT * FROM departments
WHERE EXISTS
(
SELECT * FROM employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500 
)
ORDER BY department_name;

<2>、ANTI JOIN

ANTI JOIN多用于!=或not in 等查询；如果找到满足条件（!= not in）的不返回，不满足条件（!= not in）的返回。和join相反。

官方介绍案例如下：

Using Antijoins: Example
SELECT * ROM employees
WHERE department_id NOT IN
(
SELECT department_id FROM departments
WHERE location_id = 1700
)
ORDER BY last_name;

<3>、SELF JOIN

SELF JOIN其实就是某个表和其自身连接，连接方式可以是内连接，外连接，交叉连接。

官方介绍案例如下：

Using Self Joins: Example 

SELECT e1.last_name||' works for '||e2.last_name "Employees and Their Managers"
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id
AND e1.last_name LIKE 'R%';

over.