6.多表查询——Oracle数据库学习日记

多表查询

多表连接时的连接类型：

• 内连接
• 外连接
  • 右外连接
  • 左外连接
• 自连接

表连接语法：

SELECT table1.col,table2.col
FROM table1,table2
WHERE table1.col=table2.col

多表连接时限制重复的列名

• 在多表中使用表前缀限制列名
• 使用表前缀可以提高效率
• 使用表别名代替全表明前缀
• 表别名提供一个较短的名称：
  • SQL代码量更少，使用较少的内存
• 在不同表中具有相同列名的列可以用别名加以区分

1、内连接

通过两张不同表之间有相关联系的列进行匹配的查询，可以通过内连接来实现。
示例

select cu.cust_id,cu.cust_first_name||cu.cust_last_name,pro.prod_id,pro.prod_name
from customers cu,products pro,sales sa     # 导入三张表并分别起别名
where cu.cust_id=sa.cust_id     # 通过两张表之间有关系的列进行表连接
and pro.prod_id=sa.prod_id      # 通过两张表之间有关系的列进行表连接
and cu.cust_id=1843;        # 加过滤条件

还可以使用join代替from中的逗号，on代替where子句,达到的效果相同，示例如下：

select cu.cust_id,cu.cust_first_name||cu.cust_last_name,pro.prod_id,pro.prod_name
from sales sa 
join products pro on pro.prod_id=sa.prod_id
join customers cu on cu.cust_id=sa.cust_id
where cu.cust_id=1843;

2、自连接

若要查询同一张表中不同列之间的匹配关系，则需要用到自连接。
示例

SQL> select e.empno,e.ename,e.mgr,m.empno,m.ename
  2  from emp e,emp m
  3  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.

自连接的from子句中将同一个表格设置不同的别名，接下来的语法和内连接相同。

3、外连接

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

=====右外连接
SELECT table1.col,table2.col
 FROM   table1,table2
 WHERE  table1.col(+)=table2.col;
 
 =====左外连接
 SELECT table1.col,table2.col
 FROM   table1.table2
 WHERE  table1.col=table2.col(+);

示例

SQL> select d.dname,e.ename
  2  from emp e,dept d
  3  where e.deptno(+)=d.deptno;

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

15 rows selected.

4、非等值连接

示例

SQL> select s.grade,e.ename,e.sal
  2  from emp e,salgrade s
  3  where e.sal between s.losal and s.hisal
  4  and s.grade=4;

     GRADE ENAME             SAL
---------- ---------- ----------
         4 JONES            2975
         4 BLAKE            2850
         4 CLARK            2450
         4 SCOTT            3000
         4 FORD             3000