Oracle基础知识4

Oracle多表联合查询之SQL92、99方式简介

多表联合查询

当需要查询的数据分布在多张表中,考虑使用多表联合查询。多表联合查询分为SQL92、SQL99 等方式,两者只是不同年份的不同定义,其使用方式不同,效果可以相同,都可以用。

SQL92方式

笛卡尔积

两个集合相乘的结果。两个分别为n目和m目的关系R和S的笛卡尔积是一个(n+m)列的元组的集合。

在数据库中,将多个表的数据进行一一对应,所得到的结果为多表的笛卡尔积 。结果的数量为所有表元组的乘积。

1
select * from emp,dept;

等值连接

先做表的笛卡尔积,然后筛选,筛选条件为等值筛选。注意:条件为字段的值相同来进行筛选,字段的名字可以相同,也可以不同。注意与下面的自然连接相区分。

1
select * from emp,dept where emp.deptno = dept.deptno;

另外,可以直接在select子句中使用字段直接获取数据,但是效率比较低,建议字段前加上表名。如果是公共字段,则必须加上表名。

1
select emp.ename,emp.job,dept.dname from emp,dept;

给表使用别名,并且使用别名的时候不能够使用as

1
select e.ename,e.job,d.dname from emp e,dept d where e.deptno = d.deptno;

不等值连接

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

自连接

自己与自己做笛卡尔积,然后进行进一步筛选。为了确定连接条件,在连接时应该对表使用别名,而且一旦使用了别名,原名就不再有效。

1
select e1.ename,e1.job,e1.sal,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;

外连接

左外连接、右外连接:可以在等值连接时多显示一些连接条件为空值的元组。

1
2
select * from emp e,dept d where e.deptno = d.deptno(+)  -- 左外连接 允许右边值为空
select * from emp e,dept d where e.deptno(+) = d.deptno -- 右外连接 允许左边值为空

在SQL92中没有全外连接

SQL99方式

  • sql 99 依然可以给表添加别名
  • 如果使用on或者using关键字对结果进行筛选,必须使用inner join作用表与表的连接,其中inner可以省略。
  • 外连接的outer关键字可以省略。
  • 依然可以继续使用分组,having,排序等。

笛卡尔积

在sql99中,笛卡尔积使用cross join关键字,只有这样,才能够将两个表的笛卡尔积可视化,但计算时没什么用。

1
select * from emp cross join dept;

自然连接

使用关键字natural join, 使用格式:

1
2
3
select * from1 natural join2;

select * from emp natural join dept; --示例

自然连接是一种特殊的等值连接。其特点是:底层先进行笛卡尔积,然后按照所有的同名同值字段自动进行等值筛选。这样也会带来如下问题:

问题1:不可以进行按照部分字段结果进行等值筛选。

问题2:按照字段名不同,但是值相同进行等值筛选的效果不可实现!

为解决上面产生的问题,下面给出几种办法。

  • 解决问题1:使用using关键字

    • 指明使用指定的字段对联合查询的结果进行等值筛选

    • 使用格式:

      1
      select 内容 from 表名 inner join 表名 using(字段名,字段名,...);
    • 使用示例:

      1
      select * from emp inner join dept using(deptno)
    • 这样解决了问题1,但是问题2仍然存在。

  • 解决问题2:使用on关键字进行自定义连接条件筛选(等值筛选、不等值筛选)

    • 使用格式:

      1
      select 内容 from 表名 inner join 表名 on 连接条件 where 普通筛选条件;
  • 使用示例:

    1
    2
    3
    -- on后面跟自主字段
    select * from emp inner join dept on emp.deptno = dept.deptno;
    select * from emp e inner join dept d on e.deptno = d.deptno; --可使用别名
  • 等值连接筛选条件使用on,普通筛选条件还是要用where,这样会增加sql语句的可读性。

    1
    select * from emp e inner join dept d on e.deptno = d.deptno where sal>2000;

自连接

  • 自连接是一种特殊的等值连接,不算是一个特别的知识点。
  • 能使用自连接的表中必须保证表中至少有两个字段数据类型相同。
  • 自连接必须为表起别名,以表示区分。

外连接

左外连接、右外连接、全外连接

1
2
3
4
5
6
select * from emp e left join dept d on e.deptno = d.deptno;  --左外连接
select * from emp e left outer join dept d on e.deptno = d.deptno; --左外连接 outer可省略
select * from emp e right join dept d on e.deptno = d.deptno; --右外连接
select * from emp e right outer join dept d on e.deptno = d.deptno; --右外连接 outer可省略
select * from emp e full join dept d on e.deptno = d.deptno; --全外连接
select * from emp e full outer join dept d on e.deptno = d.deptno; --全外连接 outer可省略

三表联合查询

SQL92实现

用法格式如下:

1
2
3
4
5
6
select 内容(别名、连接符、去除重复、Oracle函数、逻辑运算)
from 表名1, 表名2, 表名3...
where 条件(连接条件、普通筛选条件、where子句关键字)
group by 分组字段
having 多行函数筛选
order by 排序字段;

因为where中的条件复杂,所以SQL语句结构不够清晰。下面给出一个示例:

1
select * from emp e,dept d,city c where e.deptno = d.deptno and d.loc = c.cid;

SQL99实现

SQL99难于书写,易于阅读。其用法格式如下:

1
2
3
4
5
6
7
8
9
10
select 内容 from 表名1
inner join 表名2
on 连接条件
inner join 表名3
on 连接条件
... --表示可以加任意个表
where 普通筛选条件(将其看成一个合成表进行单表的筛选即可)
group by 分组
having 多行函数筛选
order by 排序;

从上面的代码结构可以看出,我们是两个两个一起分别做的笛卡尔积,得到结果后在于下一个表做笛卡尔积,而不是像SQL92那样所有表一起做的笛卡尔积,这样在大数据的情况下,无疑大大减少了运算量,增加了运行速度!

您的每一份支持将鼓励我继续创作!
-------------本文结束感谢您的阅读-------------