ORACLE：多表连接查询

最新推荐文章于 2024-07-22 15:27:09 发布

十三妹_

最新推荐文章于 2024-07-22 15:27:09 发布

阅读量4.9k

点赞数 3

分类专栏：数据库文章标签：数据库 oracle sql 运维

本文链接：https://blog.csdn.net/qq_41896822/article/details/127908243

版权

数据库专栏收录该内容

7 篇文章 3 订阅

订阅专栏

注：数据来源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

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

列名相同而类型不同会报错（可以隐式转换也可以执行成功）
多个列名符合都会作为条件

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;

五、外连接

INNER与OUTER外连接

在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.

七、多表关联

多表关联实质通两张表做连接一样，注意区分条件而已

例：
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);