传智播客Java培训--不同sql标准下的表连接和子查询

     表连接和子查询是学习数据库的一个重点，在这方面不同版本的oracle数据库版本所支持的方式是不相同的，新版本里的表连接和子查询是对旧版本不足的改进以及对旧版本优点的增强，比如在oracle8i中使用外连接(+)查询一个表中不满足条件的记录时，+在左边是显示右边表的不满足条件记录，+在右边是显示左边表不满足条件记录，这样做是很不方便的，很容易产生混淆，造成错误，而且还不可以同时查看两张表中不满足条件的记录，所以在oracle9i之后的版本中使用了新标准进行了改良，使用左外连接跟右外连接进行了明确的标识，并增加了全外连接用来实现8i中不能同时查看两张表不满足条件记录的缺陷。虽然9i之后的数据库版本中使用了新标准，但是8i的标准在项目中还是经常使用，所以我这里对8i跟9i的表连接和子查询的操作进行了一些总结,方便记忆:
Oracle 8i以前的表连接支持:
1等值连接 – Equijoin
等值连接是连接操作中最常见的一种，通常是在存在主外键约束条件的多表上建立的，连接条件中的两个字段通过等号建立等值关系。
select empno,ename,sal,emp.deptno,dname from emp,dept  
where emp.deptno = dept.deptno;
可使用AND操作符增加查询条件
使用表别名可以简化查询，使用表名(表别名)前缀可提高查询效率
为了连接n个表,至少需要n-1个连接条件
2非等值连接 -- Non-equijoin
在 EMPLOYEES 表中所有薪水位于JOB_GRADES表最低薪水和最高薪水之间雇员的薪水级别.
select e.first_name,e.salary,e.grade_level,j.lowest_sal,j.highest_sal  from employees e ,job_grades j where salary between j.lowest_sal and j.highest_sal
3外连接 -- Outer join
使用外连接可以看到参与连接的某一方不满足连接条件的记录,连接运算符为(+),传统的外连接分为左外连接和右外连接两种.
select 字段列表  from  table1,table2  
where table1.column1(+)=table2.column2;  看到table2不满足条件记录
select 字段列表  from  table1,table2  
where table1.column1=table2.column2(+);  看到table1不满足条件记录
4自连接 -- Self join
如果需要查询的数据在一张表中，那么我们可以使用自连接把一张表当做两张表来使用。
select a.enpno,a.ename,a.ngr,b.ename from emp a,emp b  
where a.ngr = b.enpno; 
Oracle9之后新引入的连接形式(支持SQL99规范)
1交叉连接(Cross join)
Cross join 产生了一个笛卡尔集，其效果等同于再两个表进行连接时未使用where子句限定连接条件;
select empno,ename,sal,emp.deptno,dname   from emp
cross join dept;
2自然连接(Natural join)
NATURAL JOIN 子句基于两个表中列名完全相同的多个列产生连接，从两个表中选出连接列的值相等的所有行，如果两个列的名称相同,但是具有不同的数据类型，则查询会返回一个错误。
select empno,ename,sal,emp.deptno,dname from emp
natural join dept;
3使用Using子句建立连接
如果不希望参照被连接表的所有同名列进行等值连接,自然连接将无法满足要求，可以在连接时使用USING子句来设置用于等值连接的列(参照列)名.
select empno, dname   from  emp
join dept using(deptno)   --此时的nutural不需要写
4使用on子句建立连接
如果要参照非同名的列进行等值连接,或想设置任意的连接条件,可以使用On子句。
select empno,ename,sal,emp.deptno,dname from emp
join dept on(emp.deptno=dept.deptno); 
5外连接(Outer join):
(1)左外连接(LEFT OUTER JOIN )
两个表在连接过程中除返回满足连接条件的数据外,还返回左表中不满足条件的数据
(2)右外连接（RIGHT OUTER JOIN）
两个表在连接过程中除返回满足连接条件的数据外,还返回右表中不满足条件的数据
(3)全外连接(Full Outer Join)
Oracle9新增的功能,两个表在连接过程中除返回满足连接条件的数据外，还返回两个表中不满足条件的所有数据

子查询
1、子查询在主查询前执行一次
2、主查询使用子查询的结果
使用子查询注意事项
在查询时基于未知时应考虑使用子查询
子查询必须包含在括号内
将子查询放在比较运算符的右侧,以增强可读性.
除非进行Top-N分析,否则不要再子查询中使用Order by子句
在Oracle8i之前的版本中，子查询不能包含Order by子句
对单行子查询使用单行运算符
对多行子查询使用多行运算符
单行子查询
单行子查询只返回一行记录
对单行子查询可使用单行记录比较运算符
=--------------等于
>--------------大于
>=------------大于等于
<--------------小于
<=--------------小于等于
<>--------------不等于
select * from emp  
where sal>(select sal from emp where empno=7000); 
子查询空值/多值问题
如果子查询未返回任何行,则主查询页不会返回任何结果
如果子查询返回单行结果,则为单行子查询,可以在主查询中对其使用相应的单行记录比较运算符
如果子查询返回多行结果,则为多行子查询,此时不允许对其使用单行记录比较运算符
多行子查询
多行子查询返回多行记录
对多行子查询只能使用多行记录比较运算符
in--------------等于列表中的任何一个
any--------------和子查询返回的任意一个值比较
all--------------和子查询返回的所有值比较
select * from emp  
where sal>any(select avg(sal) from emp group by deptno); 
select * from emp  
where sal>all(select avg(sal) from emp group by deptno);