Oracle笔记_基于样例表的连接查询

自连接

1、查询每个具有经理人的员工的名字以及其经理人的名字

select emp.ename, t.ename from emp, emp t where emp.mgr = t.empno;

select emp.ename, t.ename from emp
join emp t
on emp.mgr = t.empno;

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

已选择13行。

左外连接

left join左侧的表中 满足过滤条件的所有记录，右侧表中无记录则缺省

2、查询每个员工的名字和他的经理人的名字，没有经理人的也要查询出该员工的名字

select emp.ename, t.ename from emp left join emp t on emp.mgr = t.empno;

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

已选择14行。

右外连接 

right join左侧的表中无对应记录则缺省，右侧表中满足过滤条件的所有记录

3、查询所有部门的名称，以及该部门中所有员工的名字

select ename, dname from emp
right join dept
on emp.deptno = dept.deptno;

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

已选择15行。

全连接

full join显示对应表中所有缺省记录，SQL1999标准比92标准新增加的功能