欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

内连接和外连接

程序员文章站 2022-06-20 13:37:05
简介 等值连接 非等值连接 特殊的自连接都属于内连接 内连接:符合连接的条件的数据被选中,不符合条件的数据被滤去 外连接:外连接的结果集等于内连接的结果集加上匹配不上的记录(一个也不能少) 如何实现外连接 (+) 把(+)字段对面的表的数据全部被匹配出来 (+)把领导表里的普通员工用NULL匹配(这 ......

简介

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

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

如何实现外连接

内连接和外连接

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

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;

内连接和外连接

演示一:

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;

 内连接和外连接

  显示了12条数据。

 

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

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

 内连接和外连接 

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

 

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

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

内连接和外连接

。。。。。。

内连接和外连接 

演示二:

(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;

内连接和外连接

  。。。。。。

内连接和外连接

 

(2)有一天把老板的工资改为12500,超出了salgrade的统计范围。

update s_emp set salary=12500;

内连接和外连接

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

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;

内连接和外连接

  。。。。。。

  内连接和外连接

  从结果可以看出,没有了“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;

内连接和外连接

总结

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

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

(+)只针对oracle数据库

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

  • 表连接:
    • 内连接:
      • 等值
      • 非等值
      • 自连接
    • 外连接:
      • 等值
      • 非等值
      • 自连接

 额外补充

sql99中规定的内外连接

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

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

通常使用的:

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

内连接和外连接

 

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

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

  内连接和外连接

  • 外连接
    • 左外连接: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(+);

  内连接和外连接

 

99标准的:

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

内连接和外连接

       

    • 右外连接:b表发起连接,b表的数据全部被匹配出来
from  a表  right outer  join  b表  on  连接条件;
    • 全外连接:实际上只是一个逻辑概念,实际用途中没什么用。全外连接的结果集等于左外连接的结果集加上右外连接的结果集,并且排除重复部分(就是左右两张表都全部匹配,a表b表书写顺序随便)

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

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

 内连接和外连接

oracle的全外连接

引用两个关键字union和union  all

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

比如:

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