Oracle 多表查询、查询运算符和集合运算
一、多表查询
1、内连接
一般使用INNER JOIN关键字指定内连接，INNER可以省略，默认表示内连接。查询结果中只包含两表的公共字段值相等的行，列可以是两表中的任意列
2、外连接
包括左外连接、右外连接、全外连接
（1）左外连接 LEFT JOIN
结果集中包括两表连接后满足ON后面指定的连接条件的行，还显示JOIN关键字左侧表中所有满足检索条件的行，如何左表的某行在右表中没有匹配行，则在结果中,右表的所有选择列均为NULL。
（2）右外连接 RIGHT JOIN
是左外连接的反向连接。
（3）完全外连接 FULL JOIN
完全外连接查询的结果集包括两表内连接的结果集和左表与右表中不满足条件的行。
3、交叉连接
下面使用emp和dept表进行多表查询：
1 select ename,dname
2 from emp
3 where ename='SMITH'
执行结果：
改成：
1 select ename,(select dname from dept where deptno=20) as 部门
2 from emp
3 where ename='SMITH'
若不知道SMITH在20号部门，则要改成：
1 select ename,(select dname from dept where deptno=（select deptno from emp where ename='SMITH'）) as 部门
执行结果：
1、内连接
一般使用INNER JOIN关键字指定内连接，INNER可以省略，默认表示内连接。查询结果中只包含两表的公共字段值相等的行，列可以是两表中的任意列
1 select ename,dname
2 from emp,dept
3 where emp.deptno=dept.deptno and emp.ename='SMITH'
等同于：
1 select ename,dname
2 from emp a inner join dept b --a,b分别是emp,dept的别名
3 on a.deptno=b.deptno and a.ename="SMITH'
2、外连接
左外连接
1 select ename,dname
2 from emp a left join dept b --a,b分别是emp,dept的别名 --或者 from emp,dept
3 on a.deptno=b.deptno where a.ename="SMITH" -- a.deptno=b.deptno(+) 也表示左外连接
执行结果都是：
内连接和外连接的区别，可以从以下看出：
A表： B表： C表：
| Aid | Aname |
| A1 | A |
| A2 | AA |
| A3 | AAA |
| A4 | AAAA |
| A5 | AAAAA |
| Bid | Bname |
| B1 | B |
| B2 | BB |
| B3 | BBB |
| B4 | BBBB |
| B5 | BBBBB |
| Cid | Aid | Bid | Cname |
| C1 | A1 | B1 | C |
| C2 | A2 | B2 | CC |
| C3 | A3 | B3 | CCC |
| C4 | null | B4 | CCCC |
| C5 | null | null | CCCC |
------------内连接------------
1 select c.cid,b.bid,a,aid
2 from c,b
3 where c.bid=b.bid and c.aid=a.aid
执行结果：（必须都有，并且全都不为空）
| Cid | Bid | Aid |
| C1 | B1 | A1 |
| C2 | B2 | A2 |
| C3 | B3 | A3 |
------------左外连接 LEFT JOIN ------------
1 select c.cid,b.bid
2 from c,b
3 where c.bid=b.bid(+)
执行结果：（以C表为中心，允许输出NULL）
| Cid | Bid |
| C1 | B1 |
| C2 | B2 |
| C3 | B3 |
| C4 | B4 |
| C5 | null |
------------右外连接 RIGHT JOIN ------------
1 select c.cid,b.bid
2 from c right join b
3 on c.bid=b.bid
执行结果：（以B表为中心，允许输出NULL）
| Cid | Bid |
| C1 | B1 |
| C2 | B2 |
| C3 | B3 |
| C4 | B4 |
| null | B5 |
------------全外连接 FULL JOIN ------------
1 select c.cid,b.bid
2 from c full join b
3 on c.bid=b.bid
执行结果：
| Cid | Bid |
| C1 | B1 |
| C2 | B2 |
| C3 | B3 |
| C4 | B4 |
| null | B5 |
| C5 | null |
3、交叉连接
交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合，交叉连接也称为笛卡尔积。
1 select c.cid,b.bid 2 from c cross join b 3 on c.bid=b.bid
二、查询预算符
Exists：子查询至少返回一行时条件为true。当括号内的查询语句为true，才能继续查询。
1 select * from emp where exists(select * from emp where deptno!=50)
Not Exists：子查询不返回任何一行时条件为true。
In：与子查询返回结果集中某个值相等。
Not In：与子查询返回结果集中任何一个值不相等。
>ANY：比子查询返回结果中的某个值大。
查询任意一个比20号部门工资高的员工（大于20号部门最少的工资）：
1 select ename, sla 2 from emp 3 where deptno!=10 4 and sal>any(select sal from emp where deptno=20)
=ANY：与子查询返回结果中的某个值相等。
<ANY：比子查询返回结果中的某个值小。
>ALL：比子查询返回结果中的所有值都大。
查询比20号部门高于所有员工的工资（大于20号部门最多的工资）：
1 select ename, sla 2 from emp 3 where deptno!=10 4 and sal>all(select sal from emp where deptno=20)
三、集合运算
集合运算就是将两个或者多个结果集组合成为一个结果集。
种类：
并集 union all 返回各个查询的所有记录，包括重复记录。
并集 union 返回各个查询的所有记录，不包括重复记录。
交集 intersect 返回两个查询共有的记录。
补集 minus 返回第一个查询的记录减去第二个查询记录之后剩余的记录。
查询不隶属于30号部门的CLERK：
1 select * from emp where job="CLERK" 2 minus --补集 排除下面的条件 3 select * from emp where deptno=30
操作自增序列:
nextval 每次默认使用 自动加1 不管成功与否
1 select emp_dept.nextval from dual 2 insert into emp(ename) values(null)
currval 自带序列维护 不成功+1
1 select emp_dept.currval from dual
排名: 112 113 123
1 select ename,sal 2 rank() over(order by sal desc) as 排名113, 3 dense_rank() over(order by sal desc) as 排名112, 4 row_number() over(order by sal desc) as 排名123 5 from emp
