Oracle学习笔记 5 -- 多表查询

我们之前的查询都是建立在一张表上，那么如何从两张，或者多张表中查询数据呢？本次笔记来学习多表查询。

    基本的语法：

select *  | (具体的列名，列名1 ，列名2 )

from table 1  别名1 ， table 别名2

where  限定条件

order by 列名(asc , desc )

等值连接：使用‘=’比较符作为连接条件的连接查询，被称为等值连接。使用除‘=’以外其他比较符作为连接条件的连接查询，称为不等连接。

内连接：合并两个表或者多个表相匹配的行,如果其中一个表中的行找不到在另一个表中相匹配的行，则删除这个行。结果集中不包含一个表与另一个表不匹配的行。

外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为

左（或右）外连接:没有匹配的行时,      结果表中相应的列为空(NULL)。外连接的 WHERE 子句条件类似于内部连接, 但连接条件中没有匹配行的表的列后面要加外连接运算符, 即用圆括号括起来的加号(+)。

交叉连接：连接查询的时候不使用连接条件，容易产生笛卡尔积。

自连接：连接查询在一个表上或者是视图上进行，即：表与自己进行连接查询。

 

上面给了各个连接的大体定义，下面我们通过示例具体解释!

内连接：

        连接两个或者是多个表的数据。返回满足连接条件的数据。删除不满足连接条件和匹配列中带有null值的记录。

例：使用多表查询语句同时对emp和dept表进行操作，查询雇员姓名和部门名称。

SQL> select ename ,dname from emp , dept where emp.deptno = dept.deptno ;
ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING
 
14 rows selected

 

   上面查询语句中使用了'='运算符，为等值连接。如果各表之间的列名不相同，那么就不需要在列名钱加表的前缀；如果在各表之间有相同的列名，则

要加上表名做前缀，以此来区分不同表中的相同列名。有时表名可能较长，我们可以给表起一个别名，来简化操作和提高执行效率。

  SQL>select ename ,dname from emp e  , deptd  where e.deptno = d.deptno ;

  上面这条语句的查询结果，与上面结果也是一样的，此处不在赘述。

 

下面使用 between ... on 比较符作为连接条件，来实现不等连接。

在不等连接之前，先来了解soctt下的一个新表,salgrade --工资等级表。

SQL> select * from salgrade ;
 
     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

 

例：查询雇员表中部门号为10的员工的工资等级。

SQL>select ename ,e.deptno , grade ,sal
   2 from emp e ,dept  d , salgrade s
   3 where e.deptno = d.deptno and sal between losal and hisal  and d.deptno = 10 ;
ENAME      DEPTNO      GRADE       SAL
---------- ------ ---------- ---------
MILLER         10          2   1300.00
CLARK          10          4   2450.00
KING           10          5   5000.00

 

 上面的结果中，使用between...on...做为链接条件实现了工资等级的划分。在n个表的连接中，至少要是使用 n-1个连接条件。多个连接条件之间使用and连接。

 

 外连接：

       外连接查询是对内连接的扩展。内连接返回的结果是满足连接条件的，外连接时在内连接的基础上，返回被内连

接删除掉的数据。外连接分为：左外连接，右外连接，全外连接。左外连接，添加会第一个表中不满足连接条件的

行；右外连接，添加回第二个表中不满足连接条件的行。全外连接，添加回两个表中删除的行。

 

例：查询员工姓名 ，部门名称 和 部门编号。

SQL> select  ename , dname ,d.deptno
  2  from emp e ,dept d 
  3  where e.deptno  =  d.deptno  ;
 
ENAME      DNAME          DEPTNO
---------- -------------- ------
SMITH      RESEARCH           20
ALLEN      SALES              30
WARD       SALES              30
JONES      RESEARCH           20
MARTIN     SALES              30
BLAKE      SALES              30
CLARK      ACCOUNTING         10
SCOTT      RESEARCH           20
KING       ACCOUNTING         10
TURNER     SALES              30
ADAMS      RESEARCH           20
JAMES      SALES              30
FORD       RESEARCH           20
MILLER     ACCOUNTING         10
 
14 rows selected

观察上面的结果，发现缺少了编号为40 的部门。对上面的SQL语句进行修改。

SQL> select ename , dname , d.deptno
  2  from emp e , dept d
  3  where e.deptno(+) = d.deptno ;
 
ENAME      DNAME          DEPTNO
---------- -------------- ------
CLARK      ACCOUNTING         10
KING       ACCOUNTING         10
MILLER     ACCOUNTING         10
JONES      RESEARCH           20
FORD       RESEARCH           20
ADAMS      RESEARCH           20
SMITH      RESEARCH           20
SCOTT      RESEARCH           20
WARD       SALES              30
TURNER     SALES              30
ALLEN      SALES              30
JAMES      SALES              30
BLAKE      SALES              30
MARTIN     SALES              30
           OPERATIONS         40
 
15 rows selected

此时我们便查询出了编号为40的部门信息。我们发现，40号部门下并没有员工。上面使用到了右连接。

(+) 在 = 号的左边为右连接。此时返回的是from 语句中第二个表中的记录。

(+)在=号的右边为左连接。此时返回的是from语句中第一个表中的记录。

例：查询雇员表中每一个雇员的经理是谁 ？

SQL语句：

select e.ename ename ,m.ename mname
from emp e , emp m
where e.mgr = m.empno(+)

结果：

ENAME      MNAME
---------- ----------
SMITH      FORD
ALLEN      BLAKE
WARD       BLAKE
JONES      KING
MARTIN     BLAKE
BLAKE      KING
CLARK      KING
SCOTT      JONES
KING       
TURNER     BLAKE
ADAMS      SCOTT
JAMES      BLAKE
FORD       JONES
MILLER     CLARK
 
14 rows selected

 上面使用了左连接，虽然king的值不满足连接条件，依然可以再结果中显示。

自连接：

      上面的查询已经使用了自连接的连接方式。自连接，自已与自己的连接，与两张表的查询方式没有区别，不在赘述。

 

SQL：1999 语法对SQL的支持。

 

 交叉连接：

        cross  join  ，产生笛卡尔积。

SQL语句：

select * from emp cross join dept

没有给出查询条件，使用cross join 来连接两个表。查询结果满足笛卡尔乘积，即，两个表中每一条记录都要连接在一起。

自然连接：

natural join ，自动进行关联字段的匹配。会以两个表中有相同名字的列为条件创建等值连接。但是，两个表中只是列名相同而数据的类型不同，则连接出错。

SQL语句：

SQL> select * from emp natural join dept ;

雇员表和员工表会自动的按照deptno 进行连接。查询结果与语句  select *  from emp e , dept d

where d.deptno = e.deptno ; 相同。但是如果两张表中有多个相同的列名，此时的查询结果就是多个列名连

接后共同作用产生的结果。此时需要去掉natural ，使用using(列名)来指定，表要按照哪一列来连接。也可以使用on来指定连接条件。SQL> select * from emp e join dept d  on(e.deptno = d.deptno);结果都是一样的。

使用on字句创建多表连接语法。

SELECT 列名1, 列名2, 列名3

FROM   table1e

JOIN   table2 d

ON    d.列名 =e.列名

JOIN   table3 l

ON    d.列名 =l.列名;

右外连接、左外连接、满外连接的语法：

SELECT 列名1, 列名2, 列名3

FROM   table1 e

ROGHT OUTER JOIN table2 d

ON   (e.列名=d.列名)

例：

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

 
ENAME      DNAME
---------- --------------
CLARK      ACCOUNTING
KING       ACCOUNTING
MILLER     ACCOUNTING
JONES      RESEARCH
FORD       RESEARCH
ADAMS      RESEARCH
SMITH      RESEARCH
SCOTT      RESEARCH
WARD       SALES
TURNER     SALES
ALLEN      SALES
JAMES      SALES
BLAKE      SALES
MARTIN     SALES
           OPERATIONS
 
15 rows selected

 

SELECT 列名1, 列名2, 列名3

FROM   table1 e

LEFT OUTER JOIN table2 d

ON   (e.列名=d.列名) ;

 

SELECT 列名1, 列名2, 列名3

FROM   table1 e

FULL OUTER JOIN table2 d

ON  (e.列名=d.列名) ;