oracle多表连接

 发布日期:2019-03-22 06:39:01  阅读次数:阅读数:115  来源:

多表连接总结:

笛卡尔积(交叉连接)、等值连接、非等值连接、左外连接、右外连接、自身连接、自然连接、全外连接

 

1.笛卡尔积

    第一个表中的所有行和第二个表中的所有行都发生连接。

      SELECT * 
         FROM EMP,DEPT;

 

2.等值连接

  增加两个表相等的条件

--查询员工编号、员工姓名、部门编号、部门名称、部门地点。
SELECT EMP.EMPNO,EMP.ENAME,EMP.DEPTNO,DEPT.DNAME,DEPT.LOC  
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;
--或者用别名SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME,D.LOC  
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

 

    使用AND运算符增加其它查询条件

--查询工作地点在NEW YORK的员工编号、姓名、部门编号、工作地点。
SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.LOC
  FROM EMP E,DEPT D
 WHERE E.DEPTNO=D.DEPTNO AND D.LOC='NEW YORK';
--4.1.4.2  写一个查询,显示所有工作在CHICAGO并且奖金不为空的员工姓名,工作地点,奖金
SELECT EMP.ENAME,DEPT.LOC,EMP.COMM
  FROM EMP,DEPT
 WHERE EMP.DEPTNO=DEPT.DEPTNO AND DEPT.LOC='CHICAGO' AND EMP.COMM IS NOT NULL;

 

3.非等值连接

     分析这些表之间的关系

--查询每个员工的姓名,工资,工资等级
SELECT E.ENAME 姓名,E.SAL 工资,S.GRADE 等级
  FROM EMP E,SALGRADE S
 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
--查询每个员工的编号,姓名,工资,工资等级,所在工作城市,按照工资等级进行升序排序.
SELECT E.EMPNO,E.ENAME,E.SAL,S.GRADE,D.LOC
  FROM EMP E,SALGRADE S,DEPT D
 WHERE E.DEPTNO=D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL
 ORDER BY S.GRADE;

 

4.外部连接( 左外连接 和 右外连接 )

外部连接的符号是(+)

符号(+)所在边的表增加空行

(+)操作符只能出现在where子句中,并且不能与outer join语法同时使用。

(+)操作符只能用于实现左外连接和右外连接,而不能用于实现完全外连接。

--查询员工表和部门表的所有列,包括没有员工的部门也要显示出来。
SELECT *
  FROM EMP E, DEPT D
 --WHERE E.DEPTNO(+)=D.DEPTNO--右外连接,右边的表数据全显示
 WHERE D.DEPTNO=E.DEPTNO(+)--左外连接,左边表数据全显示

 

5. 自身连接 也叫 自连接(内部链接)

    一个表 通过某种条件和本身进行连接的一种方式,

--查询每个员的姓名和直接上级姓名?
SELECT YG.ENAME 员工名,JL.ENAME 经理名
  FROM EMP YG,EMP JL
 WHERE YG.MGR=JL.EMPNO(+)

--查询所有工作在NEW YORK和CHICAGO的员工姓名,员工编号,以及他们的经理姓名,经理编号。
SELECT YG.ENAME 员工姓名,YG.EMPNO 员工编号,JL.ENAME 经理姓名,JL.EMPNO 经理编号
  FROM EMP YG,EMP JL,DEPT D
 WHERE YG.DEPTNO=D.DEPTNO AND D.LOC IN('NEW YORK','CHICAGO')
  AND YG.MGR=JL.EMPNO
--在上一题的基础上,添加没有经理的员工KING,并按照员工编号排序。
SELECT YG.ENAME 员工姓名,YG.EMPNO 员工编号,JL.ENAME 经理姓名,JL.EMPNO 经理编号
  FROM EMP YG,EMP JL,DEPT D
 WHERE YG.DEPTNO=D.DEPTNO AND D.LOC IN('NEW YORK','CHICAGO')
  AND YG.MGR=JL.EMPNO(+)
  ORDER BY YG.EMPNO
--查询所有员工编号,姓名,部门名称,包括没有员工的部门也要显示出来。
SELECT E.EMPNO 员工编号,E.ENAME 员工姓名,D.DNAME 部门名称
  FROM EMP E,DEPT D
 WHERE E.DEPTNO(+)=D.DEPTNO

 

6.SQL标准语法

    交叉连接(笛卡尔乘积)

   多个表交叉相乘

笛卡尔乘积SQL99标准的写法:
--ORACLE
SELECT *
  FROM EMP,DEPT;
--SQL99
SELECT *
  FROM EMP CROSS JOIN DEPT;--cross join 交叉连接  
--【记】cross“可绕死”→交叉的路口可绕死我了【记】join- 表示“结合,连接”

7.自然连接

   两个表之间进行等值连接(条件名字相同,数据类型相同)

   使用所有名称和数据类型相匹配的列作为连接条件

使用SQL99标准 查询员工姓名,工资,部门名称,工作地点。
--ORACLE
SELECT *
  FROM EMP E,DEPT D
 WHERE E.DEPTNO=D.DEPTNO
--SQL99
SELECT * 
  FROM EMP NATURAL JOIN DEPT;--natural join    natural自然   join连接
--自动去匹配两张表中相同的列名(相同的数据类型)

 

8.USING子句

   using子句可以指定用某个或某些相同名字和数据类型的列作为连接条件

使用SQL99标准的USING子句 查询员工姓名,工资,部门名称,工作地点。
--ORACLE
SELECT *
  FROM EMP E,DEPT D
 WHERE E.DEPTNO=D.DEPTNO
--SQL99
SELECT *
  FROM EMP E JOIN DEPT D
 USING(DEPTNO)
--USING子句相当于oracle中where的表的连接条件
--using子句中不能加别名
--natural join和using子句不能同时使用

注意:natural join 和 using 子句相互排斥,不可以同时使用

 

9. ON子句

   指定任意连接条件,或指定要连接的列,

   用on将连接条件和其他检索条件分隔开,其他检索条件写在WHERE子句

   on 后面写等值表达式

使用SQL99标准的ON子句 查询员工姓名,工资,部门名称,工作地点。
--oracle
SELECT *
  FROM EMP E,DEPT D
 WHERE E.DEPTNO=D.DEPTNO
--SQL99
SELECT *
  FROM EMP E JOIN DEPT D
    ON E.DEPTNO=D.DEPTNO --表和表的连接条件
查询工作地点在CHICAGO的员工姓名,工资,部门名称,工作地点。
--SQL99
SELECT *
  FROM EMP E JOIN DEPT D
    ON E.DEPTNO=D.DEPTNO
 WHERE D.LOC='CHICAGO'
使用SQL99标准的ON子句 查询员工的编号、姓名、工作地点、员工经理的姓名
SELECT *
  FROM EMP YG
  JOIN EMP JL
    ON YG.MGR=JL.EMPNO
  JOIN DEPT D
    ON YG.DEPTNO=D.DEPTNO

10.左外连接

    左外连接以from子句中的左边表为基表,这个表所有的行无论是否和右边表能匹配上,左边表都会显示出来

查询员工姓名,工资,部门名称,工作地点
--ORACLE
SELECT *
  FROM EMP E,DEPT D
 WHERE D.DEPTNO=E.DEPTNO(+)
--SQL99
SELECT *
  FROM DEPT D LEFT OUTER JOIN EMP E   --left左  outer外  join连接
    ON D.DEPTNO=E.DEPTNO;

 

11. 右外连接

    右外连接以from子句中的右边表为基表,右边表的内容全部显示

查询员工姓名,工资,部门名称,工作地点
--ORACLE
SELECT *
  FROM EMP E,DEPT D
 WHERE E.DEPTNO(+)=D.DEPTNO
--SQL99
SELECT *
  FROM EMP E RIGHT OUTER JOIN DEPT D   --right右
    ON E.DEPTNO=D.DEPTNO

12. 全外连接

   全外连接返回两个等值连接的结果,以及两个表中所有等值连接失败的记录

查询员工姓名,工资,部门名称,工作地点
--ORACLE
SELECT *
  FROM EMP E,DEPT D
 WHERE D.DEPTNO=E.DEPTNO(+)
 UNION
SELECT *
  FROM EMP E,DEPT D
 WHERE E.DEPTNO(+)=D.DEPTNO
--SQL99
SELECT *
  FROM EMP E FULL OUTER JOIN DEPT D  --full全  outer外  join连接
    ON E.DEPTNO=D.DEPTNO

 

练习难题

4.3.1  显示员工SMITH的姓名,部门名称,直接上级名称
SELECT E.ENAME,D.LOC,JL.ENAME
  FROM EMP E,DEPT D,EMP JL
 WHERE E.DEPTNO=D.DEPTNO
   AND E.MGR=JL.EMPNO
   AND E.ENAME='SMITH'
4.3.2  显示员工姓名,部门名称,工资,工资级别,要求工资级别大于4级
SELECT E.ENAME,D.LOC,E.SAL,S.GRADE
  FROM EMP E,DEPT D,SALGRADE S
 WHERE E.DEPTNO=D.DEPTNO
   AND E.SAL BETWEEN S.LOSAL AND S.HISAL
   AND S.GRADE>4
--4.3.3  显示员工KING和FORD管理的员工姓名及KING和FORD的经理姓名
SELECT XS.ENAME 下属姓名,ZJ.ENAME 自己姓名,JL.ENAME 经理姓名
  FROM EMP ZJ,EMP JL,EMP XS
 WHERE ZJ.MGR=JL.EMPNO(+)
   AND XS.MGR=ZJ.EMPNO
   AND ZJ.ENAME IN('KING','FORD')
4.3.4  显示员工姓名,参加工作时间,经理名,参加工作时间,要求参加工作时间比经理早。
SELECT YG.ENAME 员工姓名,YG.HIREDATE 工作时间,JL.ENAME 经理姓名,JL.HIREDATE 工作时间
  FROM EMP YG,EMP JL 
 WHERE YG.MGR=JL.EMPNO
   AND JL.HIREDATE<YG.HIREDATE

 

 

 

如果您有好的新闻与建议,欢迎点击文章投稿

    发表评论

    电子邮件地址不会被公开。

  • 内容

  • 网名