oracle连接查询和子查询

查询

1. 连接查询分为内连接、外连接和自连接。
2. 内连接也叫连接，是最早的一种连接。 还可以被称为普通连接或者自然连接， 内连接是从结果表中删除与其他被连接表中没有匹配行的所有行， 所以内连接可能会丢失信息。
   标准sql： select e.*, d.* from emp e inner join dept d on e.deptno = d.deptno;
   另一种写法： select e.*, d.* from emp e, dept d where e.deptno = d.deptno;
3. 外连接分为三种：左外连接，右外连接，全外连接。
   对应SQL：LEFT/RIGHT/FULL OUTER JOIN。
   通常我们省略outer 这个关键字。写成：LEFT/RIGHT/FULL JOIN。
   
4. 在左外连接和右外连接时都会以一张表为基表， 该表的内容会全部显示，然后加上两张表匹配的内容。 如果基表的数据在另一张表没有记录。 那么在相关联的结果集行中列显示为空值（NULL）。
5. 对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：
   
   1. （+）操作符只能出现在where子句中，并且不能与outer join语法同时使用。
   2. 当使用（+）操作符执行外连接时，如果在where子句中包含有多个条件， 则必须在所有条件中都包含（+）操作符。
   3. （+）操作符只适用于列，而不能用在表达式上。
   4. （+）操作符不能与or和in操作符一起使用。
   5. （+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。
   6. 没有(+)操作符的那张表为基表
6. 左外连接举例：
   标准sql：select e.*, d.* from emp e left outer join dept d on e.deptno = d.deptno; 
   用(+)操作符：select e.*, d.* from emp e, dept d where e.deptno = d.deptno(+);
7. 右外连接举例：
   标准sql：select e.*, d.* from emp e right outer join dept d on e.deptno = d.deptno; 
   用(+)操作符：select e.*, d.* from emp e, dept d where e.deptno(+) = d.deptno;
8. 全外连接：左表和右表都不做限制，所有的记录都显示， 两表不足的地方用null 填充。 全外连接不支持（+）这种写法。
9. 全外连接举例：
   标准sql：select e.*, d.* from emp e full outer join dept d on e.deptno = d.deptno;
10. 自连接(self join)是SQL语句中经常要用的连接方式， 使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。
11. 自连接举例：select e1.*, e2.* from emp e1, emp e2 where e1.mgr = e2.empno;
12. 子查询：当一个查询是另一个查询的条件时，称之为子查询。
13. 子查询举例：select * from emp where sal > (select sal from emp where ename = 'JAMES');
14. 多行子查询运算符
    
15. 多行子查询运算符举例：
    in: select * from emp where deptno in (select deptno from dept where loc = 'DALLAS' or loc = 'BOSTOM');
    all: select * from emp where sal > all(select sal from emp where ename = 'JAMES' or ename = 'FORD');
    any: select * from emp where sal > any(select sal from emp where ename = 'JAMES' or ename = 'FORD');
    exists: select * from emp where exists (select sal from emp where ename = 'JAMES' or ename = 'FORD');
    
16. rownum：是一个伪列，在物理上这个列并不存在，只是在查询时才构造出来。 它是 oracle 系统顺序分配为从查询返回的行的编号，第一行为1，第二行为2，依次类推。 在web项目中分页会用到它。
17. rownum举例：select rownum, emp.* from emp where rownum <= 5;
18. 查询结果集合操作:
    union操作用于计算两个结果集的并集，它在取并集时会自动去掉结果集的重复行。
    select * from emp where sal <= 1600 union select * from emp where sal >= 1500;
    intersect操作于计算两个结果集的交集
    select * from emp where sal <= 1600 intersect select * from emp where sal >= 1500;
    minus操作用于计算两个结果集的差集（只返回在第一个结果集中存在，但在第二个集中不存在的数据）
    select * from emp where sal <= 1600 minus select * from emp where sal >= 1500;