复杂查询（连接查询、子查询）

-- 二、复杂的查询：  
/* 
1. 连接查询  
1.1内连接  
select 字段列表 from  表1,表2,……  
where 表1.列=表2.列 and  ……  
或  
select 字段列表 from  表1 join 表2  
on 表1.列=表2.列 join 表3  
on……   
*/
  
--  显示smith这位员工的信息以及他所在部门的信息  
 select * from emp,dept  
 where emp.deptno = dept.deptno and ename='SMITH';  
   
 select * from emp join dept  
 on emp.deptno=dept.deptno and ename='SMITH' ;  
   
 -- 显示每位员工的上级领导的姓名------自连接  
 select a.ename 职员,b.ename 领导 from emp a,emp b  
 where a.mgr=b.empno;  
   
 select * from emp,dept-----内连接：将两张表相匹配的显示出来，不匹配的不显示  
 where emp.deptno = dept.deptno ;  
   
-- 1.2外连接  
/* 
(1)右向外连接  
select 字段列表 from  表1 right outer join 表2  
on 表1.列=表2.列  
或：  
select 字段列表 from  表1,表2,……  
where 表1.列(+)=表2.列 and……   
*/
select * from emp,dept-----右向外连接：右表全部显示，左表之显示匹配的  
where emp.deptno (+)= dept.deptno;  
   
select * from emp right outer join dept  
on emp.deptno = dept.deptno;  
/* 
(2)左向外连接  
select 字段列表 from  表1 left outer  join 表2  
on 表1.列=表2.列   
或；  
 select 字段列表 from  表1,表2,……  
where 表1.列=表2.列 and……(+)   
*/
select * from emp,dept-----左向外连接：左表全部显示，右表之显示匹配的  
where dept.deptno = emp.deptno(+);  
   
select * from dept left outer join emp  
on emp.deptno = dept.deptno;   
  
/* 
(3)完全外连接  
select 字段列表 from  表1 full join 表2  
on 表1.列=表2.列   
*/
select * from emp full join dept  
on emp.deptno = dept.deptno;--完整外部联接返回左表和右表中的所有行。  
--当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。  
--如果表之间有匹配行，则整个结果集行包含基表的数据值。     
  
--2.子查询  
/* 
2.1、非关联的子查询  
特点：先由子查询得到一个结果，  
    主查询根据子查询得到的结果进行的查询
*/	
--1)用关系运算符实现子查询（> >=  <  <=  = != <>）  
--特点：子查询得到的结果只有一个值  
--例如：显示高于平均工资的员工信息   

select * from emp  
where sal>(select avg(sal)from emp);  
--2）用in实现的子查询   
--特点：子查询得到的结果可以多个值  
--例如：显示在NEW YORK工作的员工信息  
select * from emp  
where deptno in(select deptno from dept  
where loc='NEW YORK');  
  
/* 
2.2、关联的子查询  
特点：将主查询、子查询作为条件共同查询   
*/
--例如：查询每个部门最高工资的员工信息  
--（1）用exists实现的子查询  
select * from emp  
where exists  
(  
select * from (  
select deptno,max(sal)maxSal from emp  
group by deptno )temp  
where emp.deptno=temp.deptno and emp.sal=temp.maxSal  
);  
--（2）子查询相当于内循环  
select * from emp a  
where (select count(empno) from emp b   
       where a.deptno=b.deptno and b.sal>a.sal)=0;