ORACLE:多表连接查询

这篇具有很好参考价值的文章主要介绍了ORACLE:多表连接查询。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

目录

一、表连接

 二、笛卡尔积(交叉连接)

三、自然连接NATURAL JOIN

四、非等连接

五、外连接

左外连接 

 右外连接

全外连接

 六、自连接

七、多表关联


注:数据来源oracle默认用户Scott中的表

#测试数据
select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK	          7902 17-DEC-80	    800 		       20
      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		       20
      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		       30
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		       10
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		       20
      7839 KING       PRESIDENT 	   17-NOV-81	       5000 		       10
      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	   0	   30
      7876 ADAMS      CLERK	          7788 23-MAY-87	   1100 		       20
      7900 JAMES      CLERK	          7698 03-DEC-81	    950 		       30
      7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		       20
      7934 MILLER     CLERK	          7782 23-JAN-82	   1300 		       10

14 rows selected.

select * from dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	      DALLAS
	30 SALES	      CHICAGO
	40 OPERATIONS	  BOSTON
select * from salgrade;

     GRADE	LOSAL	   HISAL
---------- ---------- ----------
	 1	  700	    1200
	 2	 1201	    1400
	 3	 1401	    2000
	 4	 2001	    3000
	 5	 3001	    9999

一、表连接

表达式:

SELECT table1.column,table2.column

FROM table1,table2

WHERE table1.column1=table2.column2

  • where中写连接条件
  • 如果不同表中存在相同列名,需要在列名前加上表名作前缀
select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept where emp.deptno = dept.deptno;

ENAME	       DEPTNO	  DEPTNO DNAME
---------- ---------- ---------- --------------
CLARK		   10	      10 ACCOUNTING
KING		   10	      10 ACCOUNTING
MILLER		   10	      10 ACCOUNTING
JONES		   20	      20 RESEARCH
FORD		   20	      20 RESEARCH

使用别名

上述等连接的SQL1999表达式等价于

select emp.ename,emp.deptno,dept.deptno,dept.dname from emp inner join dept
on ( emp.deptno=dept.deptno);
还等价于:
select emp.ename,deptno,dept.dname from emp inner join dept using (deptno);
使用using时,前面列名相同的不再进行区分
select e.ename,e.deptno,d.deptno,d.dname from emp e,dept d  where e.deptno = d.deptno;

ENAME	       DEPTNO	  DEPTNO DNAME
---------- ---------- ---------- --------------
CLARK		   10	      10 ACCOUNTING
KING		   10	      10 ACCOUNTING
MILLER		   10	      10 ACCOUNTING
JONES		   20	      20 RESEARCH
FORD		   20	      20 RESEARCH
ADAMS		   20	      20 RESEARCH
SMITH		   20	      20 RESEARCH

 二、笛卡尔积(交叉连接)

笛卡尔积的出现条件

  • 连接条件遗漏、不正确
  • 表中所有行互相连接
  • 避免笛卡尔积使用where条件
#emp共14行数据,dept共4行数据,笛卡尔积出现14*4=56行数据
select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept;

ENAME	       DEPTNO	  DEPTNO DNAME
---------- ---------- ---------- --------------
SMITH		   20	      10 ACCOUNTING
ALLEN		   30	      10 ACCOUNTING
WARD		   30	      10 ACCOUNTING
...........
56 rows selected.

三、自然连接NATURAL JOIN

自然连接会以两表中具有相同名字的列为条件创建等值连接

  1. 列名相同而类型不同会报错(可以隐式转换也可以执行成功)
  2. 多个列名符合都会作为条件
select emp.ename,deptno,dept.dname from emp natural join dept;

ENAME	       DEPTNO DNAME
---------- ---------- --------------
CLARK		   10 ACCOUNTING
KING		   10 ACCOUNTING
MILLER		   10 ACCOUNTING
JONES		   20 RESEARCH
FORD		   20 RESEARCH
......

四、非等连接

等连接=,如emp.deptno = dept.deptno

非等连接>,>=,<,<=,!>,!<,<>

#查询员工的工资以及对应的工资等级
select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal;

     EMPNO ENAME	     SAL      GRADE
---------- ---------- ---------- ----------
      7369 SMITH	     800	  1
      7900 JAMES	     950	  1
      7876 ADAMS	    1100	  1
      7521 WARD 	    1250	  2
      7654 MARTIN	    1250	  2
      7934 MILLER	    1300	  2
      7844 TURNER	    1500	  3
      7499 ALLEN	    1600	  3
      7782 CLARK	    2450	  4
      7698 BLAKE	    2850	  4
      7566 JONES	    2975	  4
      7788 SCOTT	    3000	  4
      7902 FORD 	    3000	  4
      7839 KING 	    5000	  5

14 rows selected.

上述查询语句等价于:

select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal; 

五、外连接

INNEROUTER外连接

  • 在SQL1999中,两表连接只返回匹配的行,成为内连接
  • 两表连接,除了返回匹配的行,还返回左/右表不满足条件的行,称为左/右外连接
  • 两表连接,除了返回匹配的行,还返回两表中所有不满足的行,称为全外连接

外连接的符号为+

右外连接:

SELECT table1.column,table2.column

FROM table1,table2

WHERE table1.column1(+)=table2.column2

左外连接:

SELECT table1.column,table2.column

FROM table1,table2

WHERE table1.column1=table2.column2(+)

左外连接 

#在emp中准备一条数据
insert into emp(empno,ename) values(8000,'DEBUG');
#左外连接
select e.ename,e.deptno,d.deptno,d.dname from emp e,dept d  where e.deptno = d.deptno(+);

ENAME	       DEPTNO	  DEPTNO DNAME
---------- ---------- ---------- --------------
CLARK		   10	      10 ACCOUNTING
KING		   10	      10 ACCOUNTING
MILLER		   10	      10 ACCOUNTING
SMITH		   20	      20 RESEARCH
JONES		   20	      20 RESEARCH
SCOTT		   20	      20 RESEARCH
ADAMS		   20	      20 RESEARCH
FORD		   20	      20 RESEARCH
ALLEN		   30	      30 SALES
WARD		   30	      30 SALES
MARTIN		   30	      30 SALES
BLAKE		   30	      30 SALES
TURNER		   30	      30 SALES
JAMES		   30	      30 SALES
DEBUG
#注意最后一行没有部门号
15 rows selected.

 其SQL1999语法等价于:

select e.ename,e.deptno,d.deptno,d.dname from emp e left outer join dept d on (e.deptno = d.deptno);

 右外连接

#右外连接
select e.ename,e.deptno,d.deptno,d.dname from emp e,dept d  where e.deptno(+) = d.deptno;

ENAME	       DEPTNO	  DEPTNO DNAME
---------- ---------- ---------- --------------
CLARK		   10	      10 ACCOUNTING
MILLER		   10	      10 ACCOUNTING
KING		   10	      10 ACCOUNTING
JONES		   20	      20 RESEARCH
SMITH		   20	      20 RESEARCH
SCOTT		   20	      20 RESEARCH
FORD		   20	      20 RESEARCH
ADAMS		   20	      20 RESEARCH
WARD		   30	      30 SALES
TURNER		   30	      30 SALES
ALLEN		   30	      30 SALES
JAMES		   30	      30 SALES
MARTIN		   30	      30 SALES
BLAKE		   30	      30 SALES
			              40 OPERATIONS
#注意最后一行该部门没有员工

上述右外连接SQL1999等价于:

select e.ename,e.deptno,d.deptno,d.dname from emp e right outer join dept d on (e.deptno = d.deptno);

全外连接

#全外连接SQL1999语法
select e.ename,e.deptno,d.deptno,d.dname from emp e full outer join dept d on (e.deptno = d.deptno);

ENAME	       DEPTNO	  DEPTNO DNAME
---------- ---------- ---------- --------------
SMITH		   20	      20 RESEARCH
ALLEN		   30	      30 SALES
WARD		   30	      30 SALES
JONES		   20	      20 RESEARCH
MARTIN		   30	      30 SALES
BLAKE		   30	      30 SALES
CLARK		   10	      10 ACCOUNTING
SCOTT		   20	      20 RESEARCH
KING		   10	      10 ACCOUNTING
TURNER		   30	      30 SALES
ADAMS		   20	      20 RESEARCH
JAMES		   30	      30 SALES
FORD		   20	      20 RESEARCH
MILLER		   10	      10 ACCOUNTING
DEBUG
			              40 OPERATIONS

16 rows selected.

 六、自连接

在emp表中,mgr为员工的领导工号,所以可以对emp自连接来查询每个员工的领导及其工号

自连接中必须使用别名,其select查询必须要前缀

自连接可看做两张表的等值连接,只不过这两张表实质为一张表。

select e.empno,e.ename,e.mgr,m.empno,m.ename from emp e ,emp m where e.mgr=m.empno;

     EMPNO ENAME	     MGR      EMPNO ENAME
---------- ---------- ---------- ---------- ----------
      7902 FORD 	    7566       7566 JONES
      7788 SCOTT	    7566       7566 JONES
      7844 TURNER	    7698       7698 BLAKE
      7499 ALLEN	    7698       7698 BLAKE
      7521 WARD 	    7698       7698 BLAKE
      7900 JAMES	    7698       7698 BLAKE
      7654 MARTIN	    7698       7698 BLAKE
      7934 MILLER	    7782       7782 CLARK
      7876 ADAMS	    7788       7788 SCOTT
      7698 BLAKE	    7839       7839 KING
      7566 JONES	    7839       7839 KING
      7782 CLARK	    7839       7839 KING
      7369 SMITH	    7902       7902 FORD

13 rows selected.

七、多表关联

多表关联实质通两张表做连接一样,注意区分条件而已 文章来源地址https://www.toymoban.com/news/detail-412153.html

例:
select e.ename,d.dname,s.grade from emp e,dept d,salgrade s where e.sal between s.losal and s.hisal and e.deptno=d.deptno;

ENAME	   DNAME	       GRADE
---------- -------------- ----------
KING	   ACCOUNTING		   5
FORD	   RESEARCH		   4
SCOTT	   RESEARCH		   4
JONES	   RESEARCH		   4
BLAKE	   SALES		   4
CLARK	   ACCOUNTING		   4
ALLEN	   SALES		   3
TURNER	   SALES		   3
MILLER	   ACCOUNTING		   2
MARTIN	   SALES		   2
WARD	   SALES		   2
ADAMS	   RESEARCH		   1
JAMES	   SALES		   1
SMITH	   RESEARCH		   1

14 rows selected.
select e.ename,d.dname,s.grade from emp e,dept d,salgrade s where (e.sal between s.losal and s.hisal) and (e.deptno=d.deptno);

到了这里,关于ORACLE:多表连接查询的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 大数据开发之Hive案例篇10-大表笛卡尔积优化

    需求描述: 表概述: 需要实现的需求 SQL代码: 运行日志: 从日志可以看到,数据倾斜了,redcue一直卡在99%不动,过一段时间就被断开了。 web页面日志: 从web页面可以看到,reduce被kill的原因是Container被ApplicationMaster给kill掉了 过一段时间整个Job都被kill掉了 因为reduce卡在了99%,所以

    2024年02月09日
    浏览(84)
  • 【Python】实战:生成多层嵌套笛卡尔积组合问卷 csv《感知觉与沟通评估表》

    目录 一、适用场景 二、业务需求  (1)原产品需求  (2)需求分析 

    2024年02月02日
    浏览(42)
  • 机器人在笛卡尔空间和关节空间的多项式轨迹规划以及matlab代码(三次、五次、七次)

    三次多项式轨迹规划就是s(t)相对于时间t的变化满足三次多项式变化,其表达式如下:                      如前文所述:t的取值范围是[0,T],s(t)的取值范围是[0,1], 又因为初始速度和末速度都为0,所以: S(t)的一阶导数表达式为: 从而可以计算出对应的系数: 将

    2024年01月17日
    浏览(45)
  • Mysql表关联简单介绍(inner join、left join、right join、full join不支持、笛卡尔积)

    A 是1、2、3 B是2、3、4 A、B的交集是A∩B = 2、3 A、B的并集是 AUB = 1、2、3、4 A、B的差集是 A-B = 1 B、A的差集是 B-A = 4 造数据 select A. ,B. from xin_stu_t_bak A inner join xin_teach_t_bak B on A.relation_id = B.id order by A.id; select distinct A. ,B. from xin_stu_t_bak A inner join xin_teach_t_bak B on A.relation_id = B.id

    2024年01月17日
    浏览(53)
  • 体验文心一言AI大模型生成天津师范大学、中国科学技术大学、中北大学、伊利诺伊大学厄巴纳-香槟分校和巴黎笛卡尔大学报告

    天津师范大学是天津市属重点大学,位于天津市西青区宾水西道393号,简称“天师大”。天津师范大学入选教育部“卓越教师培养计划”、国家“特色重点学科项目”、”国家建设高水平大学公派研究生项目“、首批“新工科研究与实践项目”、“国家级大学生创新创业训练

    2024年02月16日
    浏览(38)
  • Cesium:CGCS2000坐标系的xyz坐标转换成WGS84坐标系的经纬高度,再转换到笛卡尔坐标系的xyz坐标

    作者:CSDN @ _乐多_ 本文将介绍使用 Vue 、cesium、proj4 框架,实现将CGCS2000坐标系的xyz坐标转换成WGS84坐标系的经纬高度,再将WGS84坐标系的经纬高度转换到笛卡尔坐标系的xyz坐标的代码。并将输入和输出使用 Vue 前端框架展示了出来。代码即插即用。 网页效果如下图所示, 一、

    2024年02月06日
    浏览(45)
  • MySQL---多表联合查询(上)(多表关系、外键约束、学生成绩多表关系、交叉连接查询)

    1. 多表关系 MySQL 多表之间的关系可以概括为: 一对一:        比如:一个学生只有一张身份证;一张身份证只能对应一学生。        实现原则:在任一表中添加唯一外键,指向另一方主键,确保一对一关系。         一般一对一关系很少见,遇到一对一关系的表最好是

    2024年02月05日
    浏览(56)
  • ORACLE:多表连接查询

    目录 一、表连接  二、笛卡尔积(交叉连接) 三、自然连接NATURAL JOIN 四、非等连接 五、外连接 左外连接   右外连接 全外连接  六、自连接 七、多表关联 注:数据来源oracle默认用户Scott中的表 表达式: SELECT table1.column,table2.column FROM table1,table2 WHERE table1.column1=table2.column

    2023年04月13日
    浏览(37)
  • 8.Oracle中多表连接查询方式

    表连接分类: 内连接、外连接、交叉连接、自连接 1 内连接 内连接是一种常见的多表关联查询方式,一般使用INNER JOIN来实现。其中,INNER可以省略,当只使用JOIN时,语句只表示内连接操作。在使用内连接查询多个表时,必须在FROM子句之后定义一个ON子句

    2024年02月10日
    浏览(39)
  • 连接查询(多表查询)——MySQL

    又称多表查询,当 查询的字段涉及多个表 的时候,就要用到连接查询 分类: 为表起别名: 提高语句的简洁度 区分多个重名字段 注意 :如果为表起了别名,则查询的字段就 不能使用原来的别名去限定 查询A、B 交集部分数据 语法: 隐式内连接 select 字段列表 from 表1,表

    2024年02月04日
    浏览(49)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包