oracle 仲宏伟,Oracle多表连接与子查询

1：等值连接

迪卡尔集连接

select ename, a.deptno as a_deptno,b.deptno as b_deptno ,b.dname as 部门

from emp a, dept b

等值连接

select ename, a.deptno as a_deptno,b.deptno as b_deptno ,b.dname as 部门

from emp a, dept b

where a.deptno = b.deptno;

2：非等值连接，在emp表和salgrade表中查找员工的姓名，工资，等级，工资上线，工资下线

select ename as 姓名, sal as 工资, grade as 工资等级,losal as 工资上线，hisal as 工资下线

from emp, salgrade

where sal between losal and hisal;

3:外连

右外连

select e.ename, d.dname, e.deptno

from emp e,dept d

where e.deptno = d.deptno(+);

左外连与右外连相反

以hr登陆查询表employees 和departments 表

select  first_name as 姓名, department_name as 部门名称,  d.department_id as 部门编号

from employees e, departments d

where e.department_id= d.department_id(+) ;

4:自连：在同一个表中查询每个员工及上司的工号和姓名

select a.empno  as 员工编号, a.ename as 员工姓名, a.mgr as 上司的员工编号, b.ename as 上司姓名

from emp a, emp b

where a.mgr = b.empno;

5：sql99

交叉连接 cross join------相当于迪卡尔集

select e.ename,d.dname

from emp e cross join dept d;

自然连接 natural join------相当等值连接

select e.ename, d.dname

from emp e natural join dept d;

using子句-----使用同名列查询

select e.ename, d.dname

from emp e join dept d

using (deptno);

on子句 ------当列名不同时用on子句

用on查询两张表

select e.ename, d.dname

from emp e join dept d

on e.deptno = d.deptno;

用on查询多张表

select e.ename, d.dname

from emp e join dept d

on e.deptno = d.deptno

join 第三个表

on 列1 = 列2;

内连接(Inner join)-------内连接只返回满足连接条件的数据

select employee_id, last_name, salary, department_id, department_name

from employees inner join departments using(department_id);

左外连

select employee_id, last_name, salary, department_id, department_name

from employees left join departments using(department_id);

右外连

select employee_id, last_name, salary, department_id, department_name

from employees right join departments using(department_id);

满外连

select employee_id, last_name, salary, department_id, department_name

from employees full outer join departments using(department_id);

子查询------------

查找出工资比scott高的人

select ename, sal from emp

where sal>

(select sal from emp where ename='SCOTT');

查找那些人和scott相同职位的人

select ename, job from emp

where job=

(select job from emp where ename='SCOTT')

and ename <> 'SCOTT';

any的用法  < any意味着小于最大、> any大于最小

select empno, ename, sal, job

from emp

where sal

all的用法 < all：小于所有，即小于最小、> all：大于所有，即大于最大

select empno, ename, sal, job

from emp

where sal