Oracle多表查询

Oracle多表查询

Oracle

SELECT ename, sal, dname FROM emp, dept WHERE emp.deptno=dept.deptno；

其中DEPTNO是DEPT表中的主键，同时它又是emp表中的外键，在这个查询语句中连接的条件是它们相等，条件emp.deptno=dept.deptno的意思是在emp表中查询每个员工所在的部门号，然后根据部门号在dept表中查询对应的部门名称，凡是不满足这个条件的部门名称都将被过滤掉。

SELECT ename, sal, dname FROM emp a, dept b WHERE a.deptno=b.deptno；

在这个SELECT语句中，为了书写方便，为表emp和dept分别定义了别名a和b，这样在其他子句中就可以使用这样的别名了。在构造查询语句时，首先要仔细分析这个查询要涉及哪些表，以及这些表通过哪些列进行连接，然后在SELECT语句中指定所有涉及的表，在WHERE子句中指定连接条件。

SELECT ename AS姓名，sal AS工资，grade AS工资级别，dname AS部门名称
FROM emp a, dept b, salgrade c
WHERE a.deptno=b.deptno
AND（a.deptno=10 or a.deptno=20）
AND（sal＞=c.losal and sal＜=c.hisal）

##外连接
是一种特殊的连接方式。假设有两个表A和B，用相等连接查询可以返回表A中的所有行，而表B中的部分行因为不满足相等条件，所以是不会被查询到的，但是利用外连接可以返回表B中的所有行。对于表A和B来说，外连接的条件表达式的格式为：

WHERE A.列名（+）=B.列名

SQL＞SELECT ename, dname FROM emp a, dept b WHERE a.deptno（+）=b.deptno；--显示所有部门

##自连接
是一种特殊的相等连接。相等连接一般涉及多个不同的表，自连接也涉及多个表，但是它们是同一个表。例如，在表emp中，每个员工都有一个顶头上司的编号，而这个上司同时也是该公司的员工。如果要查询每个员工的上司姓名，首先要确定上司的编号，然后根据这个编号再查询emp表，利用相等连接确定上司的姓名，这就相当于两个表的连接。能够完成这个查询的SELECT语句为：

SQL＞SELECT a.ename, b.ename AS manager
FROM emp a, emp b
WHERE a.mgr=b.empno；

为了在查询中将所有员工姓名都列出来，可以在上述查询的基础上再使用外连接。用于完成这个查询的SELECT语句为：

SQL＞SELECT a.ename, b.ename AS manager
FROM emp a, emp b
WHERE a.mgr=b.empno（+）；

##子查询
要查询所有在部门RESEARCH工作的员工姓名。如果使用常规的查询方法，要进行两次查询，首先查询dept表，确定该部门的部门号，然后根据这个部门号在emp表中查询属于这个部门的员工。也就是说，需要两条SELECT语句：

SELECT deptno FROM dept WHERE dname='RESEARCH'；
SQL＞SELECT ename FROM emp WHERE deptno=20；（部门RESEARCH的部门号）

SELECT ename FROM emp
WHERE deptno=（SELECT deptno FROM dept WHERE dname='RESEARCH'）；

##子查询中的运算符

exists S --如果集合不为空，条件表达式为真，否则为假。
in S  --如果在集合中，为真，否则为假
= S  --如果与集合中的值相等为真
> < >= <= --相应的关系运算符
any -- 与集合中的任意元素比较
all -- 与集合中所有元素比较

其中，集合S为另一个select语句的查询结果。

SELECT ename FROM emp
WHERE exists（SELECT deptno FROM dept WHERE deptno=0）；

SELECT ename FROM emp
WHERE exists（SELECT deptno FROM dept WHERE deptno=0）；

SELECT dname FROM dept
WHERE deptno=any（SELECT distinct deptno FROM emp）；

SQL＞SELECT ename FROM emp
WHERE sal＞all（SELECT sal FROM emp WHERE deptno=30）

SQL＞SELECT ename FROM emp
WHERE sal＞any（SELECT sal FROM emp WHERE deptno=30）；

SQL＞SELECT ename FROM emp
WHERE sal＞（SELECT avg（sal）FROM emp）；

SELECT dname FROM dept
WHERE deptno=（SELECT deptno FROM emp a
GROUP BY deptno
HAVING avg（sal）＞all（SELECT avg（sal）FROM emp
WHERE deptno！=a.deptno
GROUP BY deptno））