SQL—多表查询

多表查询

表连接

• 将多个表之间有从属关系的表进行连接
  注意:同一列名多表使用时要写上表名做前缀。
  例如：emp表和dept表中都有的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
KING               10         10 ACCOUNTING
MILLER             10         10 ACCOUNTING
JONES              20         20 RESEARCH
FORD               20         20 RESEARCH
ADAMS              20         20 RESEARCH
SMITH              20         20 RESEARCH
SCOTT              20         20 RESEARCH
WARD               30         30 SALES
TURNER             30         30 SALES
ALLEN              30         30 SALES
JAMES              30         30 SALES
BLAKE              30         30 SALES
MARTIN             30         30 SALES

自连接

• 同一表中具有多列拥有从属关系，那么我们就可以进行自连接
  emp表中empno列和mgr列具有从属关系，那么我们就可以利用二者的从属关系进行自连接
  例：查询员工‘SMITH’的领导姓名

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

ENAME      ENAME
---------- ----------
SMITH      FORD

非等值连接

• 条件筛选时，不是“=”，而是区间范围性的。
  例：查询薪水等级为4的员工信息

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

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

内连接和外连接

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

外连接

因为mgr列有空值，所以这里我们使用外连接。
例：查询员工姓名及领导者姓名

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

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

笛卡尔积

• 笛卡尔积产生条件
  1.连接条件被遗漏
  2.连接条件不全
  3.所有表中所有的⾏互相连接
• 为了避免笛卡尔积，可以在WHERE加有有效的连接条件