本文详细介绍了ORACLE数据库中的JOIN操作，包括内连接、外连接（全连接、左外连接、右外连接）、自然连接以及笛卡尔积的概念、示例和注意事项。
oralce join 连接
在ORACLE数据库中，表与表之间的SQL JOIN方式有多种（不仅表与表，还可以表与视图、物化视图等联结）。SQL JOIN其实是一个逻辑概念，像NEST LOOP JOIN、 HASH JOIN等是表连接的物理实现方式。
注：文字描述参考：https://blog.youkuaiyun.com/zhanglongfei_test/article/details/122563033，测试采用oracle自带的scott表
内连接：INNER JOIN
inner join 表示返回俩个表或记录连接字段的匹配记录。它有三种实现方式
--##inner join
C##SCOTT@LHRCDB> select empno,e.deptno from emp e inner join dept d on e.deptno=d.deptno;
EMPNO DEPTNO
---------- ----------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10
7788 20
7839 10
7844 30
7876 20
7900 30
7902 20
7934 10
14 rows selected.
C##SCOTT@LHRCDB>
----join
--inner join 可以使用简写join方式，如下所示，但是建议使用inner join。
C##SCOTT@LHRCDB> select empno,e.deptno from emp e join dept d on e.deptno=d.deptno;
EMPNO DEPTNO
---------- ----------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10
7788 20
7839 10
7844 30
7876 20
7900 30
7902 20
7934 10
14 rows selected.
C##SCOTT@LHRCDB>
--直接关联，这里的不同关联条件用where，join用on
C##SCOTT@LHRCDB> select empno,e.deptno from emp e,dept d where e.deptno=d.deptno;
EMPNO DEPTNO
---------- ----------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10
7788 20
7839 10
7844 30
7876 20
7900 30
7902 20
7934 10
14 rows selected.
C##SCOTT@LHRCDB>
-------using语法，这里注意，投影列不能使用别名
--错误的语法：
C##SCOTT@LHRCDB> select empno,e.deptno from emp e join dept d using(d.deptno);
select empno,e.deptno from emp e join dept d using(d.deptno)
*
ERROR at line 1:
ORA-01748: only simple column names allowed here
C##SCOTT@LHRCDB>
--正确的语法
C##SCOTT@LHRCDB> select empno,deptno from emp e join dept d using(deptno);
EMPNO DEPTNO
---------- ----------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10
7788 20
7839 10
7844 30
7876 20
7900 30
7902 20
7934 10
14 rows selected.
用韦恩图来表示则更便于理解：
外链接：OUTER JOIN
1.全连接：full join
全连接：包含左、右俩个表的所有行，不管另一表中是否存在与其匹配的行。不符合条件的，则以空值代替。如下所示：
C##SCOTT@LHRCDB> select empno,e.deptno from emp e full join dept d on e.deptno=d.deptno;
EMPNO DEPTNO
---------- ----------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10
7788 20
7839 10
7844 30
7876 20
7900 30
7902 20
7934 10
15 rows selected.
C##SCOTT@LHRCDB>
FULL OUTER JOIN的韦恩图如下：
2.左外连接：LEFT JOIN
左外连接：又叫左连接，意思是包含左边表所有记录，右边所有匹配的记录，如果没有则用空补齐。换句话说就是，列出左边表全部的，及右边表符合条件的，不符合条件的以空值代替。
--LEFT JOIN
C##SCOTT@LHRCDB> select empno,e.deptno from emp e left join dept d on e.deptno=d.deptno;
EMPNO DEPTNO
---------- ----------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10
7788 20
7839 10
7844 30
7876 20
7900 30
7902 20
7934 10
14 rows selected.
C##SCOTT@LHRCDB>
--加号（+)形式
C##SCOTT@LHRCDB> select empno,e.deptno from emp e,dept d where e.deptno=d.deptno(+);
EMPNO DEPTNO
---------- ----------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10
7788 20
7839 10
7844 30
7876 20
7900 30
7902 20
7934 10
14 rows selected.
C##SCOTT@LHRCDB>
LEFT OUTER JOIN （with common data）韦恩图如下：
3.右外连接：RIGHT JOIN
右外连接：又叫右链接，意思是包括右边表所有记录，匹配左边表的记录，如果没有则以空补齐。换句话说，列出右边全部的，及左边符合条件的，不符合条件的则以空值代替。
--right join
C##SCOTT@LHRCDB> select empno,e.deptno from emp e right join dept d on e.deptno=d.deptno;
EMPNO DEPTNO
---------- ----------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10
7788 20
7839 10
7844 30
7876 20
7900 30
7902 20
7934 10
15 rows selected.
C##SCOTT@LHRCDB>
--加号（+)形式
C##SCOTT@LHRCDB> select empno,e.deptno from emp e,dept d where e.deptno(+)=d.deptno;
EMPNO DEPTNO
---------- ----------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10
7788 20
7839 10
7844 30
7876 20
7900 30
7902 20
7934 10
15 rows selected.
笛卡尔积：CROSS JOIN
cross就是笛卡尔乘积连接，不需要任何关联条件，实现M*N的结果集。实际操作中，很少会用到，但要注意在开发中做表之间关联时应避免产生笛卡尔集，否则数据量过大，导致内存溢出。
C##SCOTT@LHRCDB> select count(1) from emp;
COUNT(1)
----------
14
C##SCOTT@LHRCDB> select count(1) from dept;
COUNT(1)
----------
4
C##SCOTT@LHRCDB>
--笛卡尔积是多少呢，14*4=56
--cross join
--其实cross join类似于select ename,e.deptno from emp e,dept d;返回结果都是56条
C##SCOTT@LHRCDB> select ename,e.deptno from emp e cross join dept d;
ENAME DEPTNO
-------------------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
56 rows selected.
C##SCOTT@LHRCDB>
总结：
Oracle 外连接(OUTER JOIN)包括以下：
左外连接(左边的表不加限制) left [outer] join, a.key=b.key(+)
右外连接(右边的表不加限制) righ [outer] join, a.key(+)=b.key
全外连接(左右两表都不加限制) full [outer] join
内连接(左右两表公共部门) [inner] join
笛卡尔积cross join
对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：
（+）操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。
当使用（+）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。 ?
（+）操作符只适用于列，而不能用在表达式上。
（+）操作符不能与OR和IN操作符一起使用。
（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。
用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。
————————————————
版权声明：本文为优快云博主「zhanglongfei1016」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.youkuaiyun.com/zhanglongfei_test/article/details/122563033
自然连接（natural join）
自然连接是在广义笛卡尔积R×S中选出同名属性上符合相等条件元组，再进行投影，去掉重复的同名属性，组成新的关系。即自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。
C##SCOTT@LHRCDB> select ename,deptno from emp natural inner join dept;
ENAME DEPTNO
-------------------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
14 rows selected.
C##SCOTT@LHRCDB>
扫一扫
818
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
