oracle连接查询(父子表、内连接、左/右外连接、(+)的使用、自连接、非等值连接)与子查询(单行子查询、多行子查询、多列子查询)

连接查询

通常建立在有相互关系的父子表之间
主要包括内连接、外连接和自连接三种
连接查询通常需要使用表别名，以缩减语句的长度。
别名一经定义，在整个的查询语句中就只能使用别名而不能使用表名。
别名只在所定义的查询语句中有效
查询语句的执行顺序：from子句最先执行，然后是where子句，最后是select子句
别名：列名后面加一个空格，然后空格后面加一个名称，该名称就是别名。

父子表：就是含有主键和外键的两个表。（有外键肯定是涉及两个表。）
含有主键的就是父表(主表)，含有外键的就是子表(从表)。

比如，上图就是一个emp表，emp中有主键empno和外键deptno，而deptno在dept表中是主键。
因为deptno字段在表1中是主键，在表2中是外键，所以表1就是主表，表2就是从表。那么对于emp表和dept表来说dept表是主表，emp表是子表。

笛卡尔积：
select emp.*,dept.* from emp,dept;

所有的连接都会先做笛卡尔积，然后再从里面筛选出符合条件的记录。
例如：
select emp.*,dept.* from emp,dept where emp.deptno=dept.deptno;
这就是有意义的连接，我们称之为：内连接

内连接

根据指定的连接条件进行连接查询，只有满足连接条件的数据才会出现在结果集中。
一般使用inner join来实现。其中inner关键字可以省略
通常在from子句之后定义一个on子句，用来指定连接条件
也可以通过using 子句指定连接条件
还可以通过where子句指定连接条件
如果多个表之间存在同名的列，则必须使用表别名来限定列的引用

指定连接条件有三种方式：
1、join on。2、using。3、where。

查询部门编号为10的员工编号和姓名，及所在的部门名称和地点:
第一种方式：使用on连接
select ename,job,dname,loc from emp e join dept d on e.deptno=d.deptno where e.deptno=10;
别名一经定义，在整个的查询语句中就只能使用别名而不能使用表名。
第二种连接方式：使用using子句（关联字段名称相同，也有join，不能有表别名）
select empno,ename,deptno,dname from emp join dept USING(deptno);
关联字段名称相同就是，连接的两个字段名是相同的。
在select后面的条件中不能出现表别名，在using()的括号中不能出现表别名，如果有筛选条件，在using()后面的where语句中也不能出现表别名。
第三种连接方式：使用where子句
select e.ename,e.job,d.dname,d.loc from emp e ,dept d where e.deptno=d.deptno and e.deptno=10;

外连接

外连接扩展了内连接的结果集。
不只列出与连接条件匹配的行，还能够列出左表（左外连接）、右表（右外连接）或两个表（完全外连接）中所有符合搜索条件的数据行。
左外连接(左边的表不加限制)：关键字为left outer join或left join
右外连接(右边的表不加限制)：关键字为right outer join或right join
完全外连接(左右两边的表不加限制)：关键字为full outer join 或　full join


例：INSERT into emp(empno,ename, job) values(9001,‘EATY’,‘CLERK’);
SELECT e.empno,e.ename,d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno;

SELECT e.empno,e.ename,d.deptno,d.dname from emp e right join dept d on e.deptno=d.deptno;

SELECT e.empno,e.ename,d.deptno,d.dname from emp e full join dept d on e.deptno=d.deptno;

若是左外连接，那么主表就是左表。
若是右外连接，那么主表就是右表。

（+）的使用:
（+）操作符要放在非主表的一方（左外连接中左表为主表，右外连接中右表为主表），并且需要使用where子句，不能存在outer join关键字。
select ename,sal,dname from emp e,dept d where e.deptno=d.deptno(+);——左外连接
select ename,sal,dname from emp e,dept d where e.deptno(+)=d.deptno;——右外连接

(+)不能表示完全连接，只能表示左/右外连接。

方便简洁，但是不推荐使用。

自连接

主要用在自参考表上显示上下级关系或层次关系。
自参考表是指在同一张表的不同列之间具有参照关系或主从关系的表

例：显示雇员对应的管理者
select m.ename 领导者, e.ename 雇员 from emp e join emp m on e.mgr= m.empno;

emp表如下：

非等值连接例题：

例：显示各个员工的姓名、工资以及工资等级：
select ename,sal,grade from emp e join salgrade s on sal between losal and hisal;

select ename,sal,grade from emp,salgrade where sal between losal and hisal;

子查询(嵌套查询)：

指嵌入在其他sql语句中的select语句，也叫嵌套查询
常见形式：
select 字段列表　from 表　where　=(select……)

分类:
单行子查询
多行子查询

所有的子查询必须使用括号括起来。

单行子查询

只返回一行数据的子查询语句
例：查询与SMITH同部门的所有员工？
思路：
（1） 查询出SMITH的部门号 select deptno from emp WHERE ename = ‘SMITH’;
（2）显示 SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = ‘SMITH’);

多行子查询

返回多行数据的子查询，其运算符包含：in all any

例1：查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
思路：
（1）查询部门10的工作
Select job from emp where deptno=10;
（2）显示
Select ename,job,sal,deptno from emp where job in(Select job from emp where deptno=10);


例2：查询工资比任意一个部门的平均工资高的员工信息。
Select * from emp where sal>any(select avg(sal) from emp group by deptno);

例3：查询工资比所有部门的平均工资都高的员工信息
Select * from emp where sal>all(select avg(sal) from emp group by deptno);

多列子查询

在子查询的结果中返回多个列

如果返回的是一行，则使用单行比较操作符（= >= <= <>）
如果返回的是多行，则使用多行比较操作符（in any all）

例：查询和scott相同部门相同职位的员工
select ename,job,deptno from emp where (deptno,job)=(select deptno,job from emp where ename=‘SMITH’);

例：查询每个部门最低薪水的员工
select empno,ename,job,sal,deptno from emp where (deptno,sal) in (select deptno,min(sal) from emp group by deptno);

练习专区：

1. 查询所有部门及其员工信息，包括那些没有员工的部门
select dname,d.deptno,e.* from emp e right join dept d on e.deptno=d.deptno;

2. 查询所有员工及其部门信息，包括那些还不属于任何部门的员工
select e.*,d.* from emp e left join dept d on  e.deptno=d.deptno;

3. 查询所有工种为CLERK的员工的姓名及其部门名称
select ename,dname from emp e join dept d on e.deptno=d.deptno;

4. 显示部门号为10的部门名、员工名和工资
select dname,ename,sal,d.deptno from emp e join dept d on e.deptno=d.deptno and e.deptno=10;

5. 显示雇员名，雇员工资及所在部门的名字，并按部门排序
select ename,sal,dname from emp e join dept d on  e.deptno=d.deptno order by e.deptno;

6. 列出在部门“SALES”（销售部）工作的员工姓名、基本工资、雇佣日期、部门名称
select ename,sal,hiredate,dname from emp e join dept d on  e.deptno=d.deptno and dname='SALES';

7. 显示各个员工的姓名，工资及工资的级别
select ename,sal,grade from emp e join salgrade s on sal between losal and hisal;

select ename,sal,grade from emp,salgrade where sal between losal and hisal;