xml地图|网站地图|网站标签 [设为首页] [加入收藏]
当前位置: www8029com > 澳门新葡8522最新网站 > 正文

Oracle的内外连接,内连接和外连接

时间:2019-11-11 22:05来源:澳门新葡8522最新网站
简介 等值连接  非等值连接 特殊的自连接都属于内连接 内连接:符合连接的条件的数据被选中,不符合条件的数据被滤去 外连接:外连接的结果集等于内连接的结果集加上匹配不上

简介

等值连接  非等值连接 特殊的自连接都属于内连接

  • 内连接:符合连接的条件的数据被选中,不符合条件的数据被滤去
  • 外连接:外连接的结果集等于内连接的结果集加上匹配不上的记录(一个也不能少)

目录结构:

如何实现外连接

澳门新葡8522最新网站 1

( )  把( )字段对面的表的数据全部被匹配出来

select  distinactt m.id,m.first_name from s_emp e,s_emp m where  e.manager_id( )=m.id;

( )把领导表里的普通员工用NULL匹配(这是连接条件)

找普通员工也要从领导表中找,因为领导表中有绝对条件证明谁是领导,但是员工表里没有这个绝对条件。所以要把领导表中的数据全部匹配出来,所以( )要加到员工表的字段上,这样领导表里的所有数据就都被匹配了(普通员工匹配的是NULL),要找普通员工时,只要找员工表里匹配的manager_id是NULL的就能找到普通员工(这是过滤条件)

select distinct m.id, m.first_name from s_emp e, s_emp m where e.manager_id( )=m.id and e.manager_id is null;

澳门新葡8522最新网站 2

contents structure [-]

演示一:

select  *  from s_dept;
select  *  from s_region;

(1)内连接:要求显示每个部门的id  和部门对应的名字  以及对应的地区名

select d.id, d.name, r.name from s_dept d, s_region r where d.region_id=r.id;

 澳门新葡8522最新网站 3

  显示了12条数据。

 

(2)业务扩展,成立新部门  id为100,name为test  region_id为NULL

 insert into  s_dept  values(100 ,‘test’,NULL);(表中的增加)

 澳门新葡8522最新网站 4 

再用上面的方法查找就只找得到原来的,新增加的找不到,因为region_id为NULL。

 

(3)所以用外连接使没有地区编号的部门也要显示出来,因为要把部门表里的所有部门匹配显示出来,那么就要把( )加在地区表上

select d.id, d.name, r.name from s_dept d, s_region r where d.region_id=r.id( )

澳门新葡8522最新网站 5

。。。。。。

澳门新葡8522最新网站 6 

Oracle的内外连接

演示二:

(1)内连接:统计每个员工的id  salary,并显示工资的工资级别(用到s_emp和salgrade表)

select e.id, e.salary, s.grade from s_emp e, salgrade s where e.salary between s.losal and s.hisal;

澳门新葡8522最新网站 7

  。。。。。。

澳门新葡8522最新网站 8

 

澳门新葡8522最新网站,(2)有一天把老板的工资改为12500,超出了salgrade的统计范围。

update s_emp set salary=12500;

澳门新葡8522最新网站 9

再用上面的方法找,老板的就丢失了!

select e.id, e.salary, s.grade from s_emp e, salgrade s where e.salary between s.losal and s.hisal order by id;

澳门新葡8522最新网站 10

  。。。。。。

  澳门新葡8522最新网站 11

  从结果可以看出,没有了“id=1”的老板的项目。

  

(3)所以要用外连接把超出统计范围的员工信息也要显示出来。因为要把员工信息表的所有信息匹配显示出来,所以要在salgrade对应的的字段上加( ),这里要加两个。

select e.id, e.salary, s.grade from s_emp e, salgrade s where e.salary between s.losal( ) and s.hisal( ) order by id;

澳门新葡8522最新网站 12

内连接

总结

每次写外连接时,可以先写号内连接(写好内连接要搞清楚表和表之间关系以及业务逻辑),再加上( ),就是用NULL匹配。因为外连接的结果集等于内连接的结果集加上匹配不上的记录。

( )字段对面的表的数据全部被匹配出来

( )只针对oracle数据库

但是所有数据库的语法机制是差不多的,只是表现形式有变化 

  • 表连接:
    • 内连接:
      • 等值
      • 非等值
      • 自连接
    • 外连接:
      • 等值
      • 非等值
      • 自连接
  1. 等值连接
  2. 非等值连接
  3. 自连接

 额外补充

外连接

sql99中规定的内外连接

  • 内连接:

    from a表,b表 where 连接条件变为99标准: from a表 join b表 on 连接条件(过滤条件不能写在一起) 或者from a表 inner join b表 on 连接条件

  1. 外连接的特点
  2. 如何实现外连接

演示:列出每个部门的名字和对应的地区名

通常使用的:

select d.name, r.name from s_dept d, s_resgion r where d.region_id=r.id;

澳门新葡8522最新网站 13

 

  99标准的:(一般不怎么用)

select d.name, r.name from s_dept d, join s_region r on d.region_id=r.id;

  澳门新葡8522最新网站 14

  • 外连接

    • 左外连接:a表发起连接,a表的数据全部被匹配出来(通过NULL记录来全部匹配)

    from a表 left outer join b表 on 连接条件;

   oracle使用的( ):

select d.name, r.name from s_dept d, s_region r where d.region_id=r.id( );

  澳门新葡8522最新网站 15

 

99标准的:

select d.name, r.name from s_dept d left outer join s_region r on d.region_id=r.id;

澳门新葡8522最新网站 16

       

    • 右外连接:b表发起连接,b表的数据全部被匹配出来

    from a表 right outer join b表 on 连接条件;

    • 全外连接:实际上只是一个逻辑概念,实际用途中没什么用。全外连接的结果集等于左外连接的结果集加上右外连接的结果集,并且排除重复部分(就是左右两张表都全部匹配,a表b表书写顺序随便)

     oracle无法使用( )实现全外连接

from  a表  full outer  join  b表  on  连接条件;

 澳门新葡8522最新网站 17

SQL99的内外连接

oracle的全外连接

引用两个关键字union和union  all

  • union:可以合并两个结果集,然后排重
  • union  all:可以合并两个结果集(不排重)

比如:

select  id from  s_emp  unoin ; 输出25条数据
select  id  from s_emp  unoin  all ; 输出50条数据

  

  1. SQL99的内连接
  2. SQL99的内连接

1,Oracle中的内外连接

1.1,内连接

内连接就是符合条件的数据被选中,不符合条件的数据被过滤掉。

1.1.1 等值连接

先看看需要使用的表结构(在其它的例子中也可能被用上,其余代码也是):

澳门新葡8522最新网站 18澳门新葡8522最新网站 19

 1 table name:s_emp
 2 Name                Description
 3 ------------------  -------------
 4 ID                   员工编号
 5 LAST_NAME            名称
 6 FIRST_NAME           姓氏
 7 USERID               身份证号
 8 START_DATE           开始时间
 9 COMMENTS             评论内容
10 MANAGER_ID           上级编号
11 TITLE                标题
12 DEPT_ID              部门编号
13 SALARY               薪水
14 COMMISSION_PCT       提成

s_emp表(员工表)结构

澳门新葡8522最新网站 20澳门新葡8522最新网站 21

1 table name:s_dept
2 Name                Description
3 ------------------  -------------
4 ID                  部门编号
5 NAME                部门名称
6 REGION_ID           地区编号

s_dept表(部门表)结构

显示每个员工的编号 姓氏 部门名称

select e.id,e.first_name,d.name
        from s_emp e,s_dept d
            --使用'='号表示等值连接
               where e.dept_id=d.id;

1,1,2 非等值连接

澳门新葡8522最新网站 22澳门新葡8522最新网站 23

1 table name:salgrade
2 Name                Description
3 ------------------  -------------
4 GRADE                薪水级别
5 LOSAL                下区间
6 HISAL                上区间

salgrade表(薪水级别表)结构

显示每个员工的编号 姓氏 薪水 薪水级别

select e.id,e.first_name,e.salary,s.grade
       from s_emp e,salgrade s
            --也可以使用 where e.salary >= s.losal and e.salary <= s.hisal
            where e.salary between losal and hisal;

1,1,3 自连接

显示所有领导的编号 姓氏

在s_emp表中,所有领导者的编号都出现在manager_id一栏,根据这一特点我们来进行如下分析:

澳门新葡8522最新网站 24

所以代码如下:

select distinct m.id,m.first_name
       from s_emp e,s_emp m  
              where e.manager_id=m.id;

1.2 外连接

1.2.1 外连接的特点

读者需要注意外连接和内连接不是完全对立的,它们的关系如下:

外连接的结果集=内连接的结果集 匹配不上的数据。

1,2,2 如何实现外连接

    通过在字段后加上( )来实现

       ( )字段所在的表的对面表的数据全部被选中 。除此之外,内连接中被过滤的数据是通过NULL记录进行的匹配。

    比如还是1,3,1的那个案例,我们已经求出了领导的编号,那么员工的信息又该怎么求呢?

    经过分析我们只需要用外连接得到表中所有员工的信息,然后再减去领导的信息,得到的就是普通员工的信息了。

    澳门新葡8522最新网站 25

 通过上面的动图我们可以看出,( )字段对应表的全部数据都用null匹配出来了,因此内连接也可以通过外连接来表达,比如上面的1,3,1的案例也可以用如下的方式实现:

select distinct m.id,m.first_name
       from s_emp e,s_emp m
               --通过is not null筛选得到实际数据
               where e.manager_id( )=m.id and e.manager_id is not null;

 这样也可以得出领导的信息。下面的代码,可以显示普通员工的信息(下面的代码和上面的代码恰好对立是相反的):

select distinct m.id,m.first_name
       from s_emp e,s_emp m
               where e.manager_id( )=m.id and e.manager_id is null;

2,SQL99的内外连接

为了在各个数据库厂商之间取得更大的统一性,美国国家标准学会(American NationalStandards Institute,ANSI)于1986年发布了第一个SQL标准,并于1989年发布了第二个版本,该版本已经被广泛地采用。ANSI在1992年更新了SQL标准的版本,即SQL92和SQL2,并于1999年再次更新为SQL99和SQL3标准。在每一次更新中,ANSI都在SQL中添加了新特性,并在语言中集成了新的命令和功能。

Oracle的内外连接和SQL99的内外连接可以相互转化。

2.1 SQL99的内连接

内连接只有满足表连接条件的数据才会被选出。

 语法格式:

        from    a表    join    b表   on 表的连接条件  where 过滤条件;
        from    a表   [inner]  join    b表   on 表的连接条件  where 过滤条件;

s_dept表结构:

澳门新葡8522最新网站 26s_dept表(部门表)结构

s_dept表数据:

澳门新葡8522最新网站 27

s_region表结构:

澳门新葡8522最新网站 28澳门新葡8522最新网站 29

table name:s_region
Name                Description
------------------  -------------
ID                  地区编号
NAME                地区名称

s_region(地区信息)表结构

s_region表数据:

澳门新葡8522最新网站 30

下面是内部连接的代码:

select distinct d.id,d.name,r.name
       from s_region r join s_dept d
               on r.id=d.region_id;

结果如下,可以看出结果只有前12行数据被匹配出来:

澳门新葡8522最新网站 31

下面这段代码和上面的一样:

select distinct d.id,d.name,r.name
       from s_region r,s_dept d
             where r.id=d.region_id;

2.2 SQL99的外连接

 2.2.1 语法

from a表  left  [outer]  join   b表   on  表的连接条件    where  过滤条件;
from a表  right  [outer]  join   b表   on  表的连接条件    where  过滤条件;
from a表  full  [outer]  join   b表   on  表的连接条件    where  过滤条件;

left join 形式的连接称为左连接。

查询结果包含join左侧表中的所有记录以及右侧表满足条件的记录。也就是a表中的全部数据都会被匹配出来,b表中符合条件的数据才会被匹配出来。

right join 形式的连接称为右连接。

查询结果包含join右侧表中的所有记录以及左侧表中满足条件的记录。也就是b表中的全部数据都会被匹配出来,a表中符合条件的数据才会被匹配出来。

full join 形式的连接称为全连接。

查询结果包含join左侧和右侧的全部数据。也就是a表和b表的所有数据都会被匹配出来。

 

本文为博主原创文章,转载请注明出处。

编辑:澳门新葡8522最新网站 本文来源:Oracle的内外连接,内连接和外连接

关键词: www8029com

  • 上一篇:没有了
  • 下一篇:没有了