oracle上下级连接查询,oracle高级查询之连接查询、集合运算

一、集合运算

--minus补集，返回第一个查询结果的记录减去第二个查询结果的记录后剩下的记录

SELECT deptno FROM dept MINUS SELECT deptno FROM emp;

--intersect交集，返回两个查询共有的记录

SELECT deptno FROM dept INTERSECT SELECT deptno FROM emp;

--union并集，返回各个查询的所有记录，不包括重复记录

SELECT deptno FROM dept UNION SELECT deptno FROM emp;

--union all并集，返回各个查询的所有记录，包括重复记录

SELECT deptno FROM dept UNION ALL SELECT deptno FROM emp;

当使用集合操作的时候，要注意：查询所返回的列数以及列的类型必须匹配，列名可以

不同。

二、连接查询

在 SQL Server 中已经学习过内联接(inner join)、外联接(outer join)，外联接又分为左外联

接(left outer join)和右外联接(right outer join)。 Oracle 中对两个表或者若干表之间的外联接用

(+)表示。

--1、内连接,默认情况下，在执行连接查询时如果没有指定任何连接操作符，都属于内连接

例：请查询出工资大于 2000 元的，员工姓名，部门，工作，工资。

由于部门名称在 dept 中，其他的信息在 emp 表中，需要内联接才能完成。

代码演示：内联接

SELECT e.ename,e.job,e.sal,d.dname

FROM emp e,dept d

WHERE e.deptno=d.deptno

AND e.sal>2000;

-----通过在from子句中指定inner join也可以指定内连接，其中inner可以省略不写

SELECT e.ename,e.job,e.sal,dd.dname

FROM emp e INNER JOIN dept dd ON e.deptno=dd.deptno

WHERE e.sal>2000;

-----如果主表的主键列和从表的外部键列名相同，那么还可以使用natural join关键字自动执行内连接操作

SELECT dname,ename FROM dept NATURAL JOIN emp;

--2、左外连接

-----左外连接是通过left[OUTER] join选项来实现的，不仅会返回满足连接条件的所有记录，还会返回不满足连接条件的连接操作符左别表的其它行

例：请查询出每个部门下的员工姓名，工资。

分析：Emp 表用外键 deptno 引用 Dept 表中的 deptno，在 Dept 表中如果有某些部门没有员工，

那么用内联接，没有员工的部门将无法显示，因此必须以 Dept 表为基准的外联接。

下面的(+)操作符以及右外连接也是一样

SELECT a.dname,b.ename FROM dept a LEFT JOIN emp b

ON a.deptno=b.deptno AND a.deptno=10;

--3、右外连接

-----跟左外连接一样

--4、完全外连接

-----通过FULL[OUTER] JOIN选择选项来实现的，不仅会返回满足连接条件的所有记录，还会返回不满足连接条件的所有其它行

SELECT a.dname,b.ename FROM dept a FULL JOIN emp b

ON a.deptno=b.deptno AND a.deptno=10;

--(+):oracle专用的联接符，在条件中出现在左边指右外联接，出现在右边指左外联接

--虽然oracl自身的联接查询语法比较好写，同时容易理解，但是为了程序便于移植，推荐使用OUTER JOIN执行外连接。

--5、使用(+)操作符

----5、1使用(+)操作符执行左外连接

SELECT a.dname,b.ename FROM dept a,emp b

WHERE a.deptno=b.deptno(+)

AND b.deptno(+)=10;

----5、2使用(+)操作符执行右外连接

SELECT a.dname,b.ename FROM dept a,emp b

WHERE a.deptno(+)=b.deptno AND a.deptno(+)=10

ORDER BY a.dname;