Oracle多表查询

Oracle多表查询

内连接查询

Select * 
from 表名1  inner join 表名2  
on  清除笛卡尔积现象的条件;
-- inner关键字可以省略

Select * 
from 表名1  , 表名2 
where  清除笛卡尔积现象的条件;

-- 加条件就可以去除笛卡尔积，一般都是找外键
--两张表之间如果没有必然的联系,即不存在外键约束等.
--如果要相关联这两张表查询数据,那么就只能是用内连接的方式进行查询..

--====什么是笛卡尔积？无条件直接查询多张表数据
select * 
from emp e,dept d;

-- 加条件就可以去除笛卡尔积，一般都是找外键
select * 
from emp e,dept d
where e.deptno = d.deptno;

--===========内连接：条件完全符合，才会显示
--案例：查询员工和他领导的信息
select e.empno,e.ename,m.empno,m.ename
from emp e,emp m
where e.mgr = m.empno;

--案例：在上面的基础上，查询员工的部门信息
select e.empno,e.ename,e.deptno,d.dname as 员工部门,m.empno,m.ename
from emp e,emp m,dept d
where e.mgr = m.empno
      and e.deptno = d.deptno;


--案例：在上面的基础上，查询领导的部门信息
select e.empno,e.ename,e.deptno,d.dname as 员工部门,m.empno,m.ename,m.deptno
from emp e,emp m,dept d,dept dd
where e.mgr = m.empno
      and e.deptno = d.deptno
      and m.deptno = dd.deptno;


--案例：在上面的基础上，查询员工的薪资等级
select e.empno,e.ename,e.deptno,d.dname as 员工部门,
       e.sal,
       s.grade as 员工薪资等级
,m.empno,m.ename,m.deptno
from emp e,emp m,dept d,dept dd,salgrade s
where e.mgr = m.empno
      and e.deptno = d.deptno
      and m.deptno = dd.deptno
      and e.sal between s.losal and s.hisal;

--案例：在上面的基础上，查询领导的薪资等级
select e.empno,e.ename,e.deptno,d.dname as 员工部门,
       e.sal,
       s.grade as 员工薪资等级
,m.empno,m.ename,m.deptno,
       s1.grade as 领导薪资等级
from emp e,emp m,dept d,dept dd,salgrade s,salgrade s1
where e.mgr = m.empno
      and e.deptno = d.deptno
      and m.deptno = dd.deptno
      and e.sal between s.losal and s.hisal
      and m.sal between s1.losal and s1.hisal;

select * from emp;
select * from dept;
select * from salgrade;

--等值连接  连接条件使用等号(=)

select * 
from emp e,dept d 
where e.deptno=d.deptno;

--非等值连接  连接条件使用>、>=、<和<=等符号

select *
from emp e,salgrade s 
where e.sal>= s.losal and e.sal<=s.hisal ;

外连接查询

--外连接：最关键找基准表，如果是基准表，表中的数据全部显示，没有对应的关联数据，以空显示

--      左外连接：select * from tab1 left join tab2 on 条件   ：   tab1是基准表  
--      右外连接：select * from tab1 right join tab2 on 条件  ：   tab2是基准表

-- 案例：查询员工和领导的信息
select * 
from emp e ,emp m
where e.mgr = m.empno;


--使用左外链接来完成上述的功能
select * 
from emp e left join emp m 
on e.mgr = m.empno;

--使用右外链接完成上述的功能
select * 
from emp m right join emp e 
on e.mgr = m.empno;


--oracle特有的外链接：使用 + 号
/*
在内连接的基础上，使用+ 号，加号在那一边，对面的一边就为基准表

推荐使用：通用的左外连接
*/
select * 
from emp e ,emp m
where e.mgr = m.empno(+);

子查询

--在一个查询里面嵌套另外一个查询,即一个sql在执行的时候需要使用到另外一个sql的查询结果作为数据。

--1.如果需要用到子查询的方式来查询数据库中的数据,
--那么就一定先要编写sql语句获取到子查询的返回结果,
--用获取到的结果根据下面的情形来判断条件应放的位置.

--2.如果where后面的条件值有多个,那么就需要用in关键字来进行限制
--例:select * from emp where deptno in (10,20);

--3.查询条件放在from后面时,直接用小括号将条件括起来即可.

--谁的工资比 SCOTT 高
--1)先查询scott的工资
select sal from emp where ename = 'SCOTT';
--2）将上述的查询作为条件
select * 
from emp 
where sal > (select sal from emp where ename = 'SCOTT');



--查询出比雇员 7654 的工资高，同时从事和 7788 的工作一样的员工

--1)先查询7654的工资，7788的工作
select sal from emp where empno=7654;
select job from emp where empno=7788;

--2)将上述的查询作为条件
select * 
from emp
where 
 sal > (select sal from emp where empno=7654)
 and 
 job = (select job from emp where empno=7788);


--===================子查询的空值问题
--单行子查询
--查询与Mike工作相同的员工
select * 
from emp
where job = (select job from emp where ename = 'Mike');


--多行子查询：
--查询不是老板的员工

--1)查询出是老板的员工
select distinct mgr from emp ;

--2) 排除上述的查询结果即可:多行子查询中如果有一个空值，那么返回的所有结果都为空
--下面的查询是没有结果
select * 
from emp
where empno not in(select distinct mgr from emp );

--3）解决第二步没有结果的问题：去空
select * 
from emp
where empno not in(select distinct mgr from emp where mgr is not null)

子查询-Exists 用法

/*
select * from emp where 1=1  :  1=1返回true

exists用法：里面写的是子查询，只要子查询有值，就返回true;子查询没有值，返回false
*/


select * from emp where 1=1 ; --返回的是true，结果全部显示
--等价于：10存在
select * from emp where exists (select * from dept where deptno=10)



select * from emp where 1=2 ; --返回的是false，没有结果显示
--等价于：50不存在
select * from emp where exists (select * from dept where deptno=50)



--范例：查询有员工的部门
select * 
from dept d 
where exists(select * from emp e where d.deptno = e.deptno )


--用in代替
select * 
from dept d
where deptno in( select distinct deptno from emp)


-- exists:  当数据量很大的时候，用这个        百万级别   ，exists效率较高
-- in :     当数据量没那么大的时候，用这个