Oracle 多表查询

1. 多表查询

• 使用等值和不等值链接，在select语句中查询多个表的数据
• 使用自连接
• 使用外连接查询，不满⾜连接条件的数据
• 笛卡尔积

2. 连接类型

2.1 Natural joins(自然连接）

• natural join子句
• using子句
• on子句

2.2 Outer joins(外连接）

• left outer jion (左外连接）
• right outer join (右外连接）
• full outer join (全外连接）

2.3 Cross join（交叉连接）

3. 语法

1. 限制重复的列名

select e.ename,e.deptno,d.deptno,d.dname
  from emp e,dept d
where e.deptno=d.deptno;

2. ⾃然连接

• NATURAL JOIN⼦句，会以两个表中具有相同名字的列为条件创建等值连接。
• 在表中查询满足等值条件的数据
• 如果只是列名相同而数据类型不同，则会产⽣错误
• location表通过LOCATION_ID列连接到DEPARTMENT表，这是两个表中惟⼀同名的列。如果存在其他公共列，联接就会全部使用它
  

3.1 使用USING子句

如果多个列具有相同的名称，但自然连接的数据类型又不匹配，则可以使用using子句来指定，使用一个等值的列。

当有多个列匹配时，用using子句匹配唯一的列

natural join 和using子句互斥

使用USING子句检索

• 不能对USING子句中的列，进⾏限制。
• 对于USING子句中的列，如果同⼀个列在SQL语句中其他位置被使用到，则不能对其进行别名
  
  

3.2 使用ON子句

表连接语法

SELECT table1.col , table2.col
  FROM table1 JOIN table2
  ON (table1.col = table2.col)

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

JOIN的附加条件

表连接的语法示例

select * from emp;
select * from dept;
select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept 
	where emp.deptno = dept.deptno;

3.3 表的自连接

表⾥有从属关系需求:查询员工的姓名以及其经理的姓员⼯的经理号 = 经理的员工

select * from emp;
select ename,mgr from emp;
select empno,ename from emp;
select e.empno,e.ename,e.mgr,m.empno,m.ename
from emp e,emp m
where e.mgr=m.empno;

3.4 非等值连接

多表查询(两种方式)

select e.ename,d.dname,s.grade
from emp e join dept d on ( e.deptno=d.deptno )
  join salgrade s on (e.sal between s.losal and s.hisal) ;

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

3.5 INNER & OUTER JOIN 内连接和外连接

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

3.6 左/右外连接

可以使用外连接来查出在⼀个表中，不匹配连接条件的行

外连接的符号是 （+）

例子

• 右外连接
  右外连接:列出所有部⻔的员⼯信息，包括没有员⼯的部⻔。
  e.deptno(+)=d.deptno;
  right outer join dept d on (e.deptno=d.deptno);SQL:1999 连接语法

  SELECT table1.col, table2.col 
  FROM table1 , table2
  WHERE table1.col(+) = table2.col ;
  

• 左外连接
  左外连接:列出所有员⼯的部⻔信息，包括没有部⻔的员⼯。
  e.deptno=d.deptno(+); 传统 oracle 连接语法
  left outer join dept d on (e.deptno=d.deptno); SQL:1999 连接语法

  SELECT table1.col, table2.col
  FROM table1 , table2
  WHERE table1.col= table2.col(+)；
  

3.7 全外连接

full outer join dept d on (e.deptno=d.deptno);

4. 笛卡尔积

笛卡尔积产生条件

• 连接条件被遗漏
• 连接条件不全
• 所有表中所有的⾏=行互相连接
  为了避免笛卡尔积，可以在WHERE加⼊有效的连接条件

4.1 交叉连接

使用CROSS JOIN子句使连接的表产省生交叉集
交叉集也被称为在两个表之间的笛卡尔积