oracle多表联查之连接(4)

来源：互联网发布：星星知我心歌词意思编辑：程序博客网时间：2024/05/22 16:38

4.外连接(左外，右外，全连接)

1)左外：用左边的表作为主表，右表对它依次匹配，得出结果
SQL>select ename,dname from emp left join dept onemp.deptno=dept.deptno;

ENAME      DNAME
---------- --------------
MILLER     ACCOUNTING
KING       ACCOUNTING
CLARK      ACCOUNTING
FORD       RESEARCH
ADAMS      RESEARCH
SCOTT      RESEARCH
JONES      RESEARCH
SMITH      RESEARCH
JAMES      SALES
TURNER     SALES
BLAKE      SALES
MARTIN     SALES
WARD       SALES
ALLEN      SALES

14 rows selected

2)右外：用右表的表作为主表，左边对它依次匹配，得出结果
SQL>select ename,dname from emp right join dept onemp.deptno=dept.deptno;

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING
                OPERATIONS

15 rows selected