多表连接

多表连接

减少冗余，规范表产生问题：如何在多个表中得到所需要的信息呢？Oracle使用连接Join来完成查询的。

Oracle提供了4种类型的连接：相等链接（Equijoin）、自连接（Selfjoin）、不等连接（Nonequijoin）和外连接（Outerjoin）

 

相等链接：

SELECT empno,ename,sal,emp.deptno,loc

FROM emp,dept

WHERE emp.deptno=dept.deptno

相等连接，即两个表中的记录的deptno值完全相等时才进行连接。通常这种连接查询涉及到主键和外键。相等连接也叫简单连接或者内连接。

一些指南：

要连接的表都要放在FROM子句中，表名用逗号分开

连接的条件放在WHERE子句中

要进行n个表的连接至少n-1个连接条件

如果多个表中有相同名的列的时候，在这些列的前面要冠以表名来区分它们，表明和列名之间使用点分隔开

可以使用表的别名替代表的全名

SELECT emp.empno, emp.ename, emp.sal, emp.deptno, dept.loc

FROM emp,dept

WHERE emp.deptno=dept.deptno

AND sal >= 1500

除了连接条件外，我们还可以在WHERE子句中加入其它的条件，这些条件由AND运算符组合起来。

 

连接表中别名的使用

如果一个表的名字很长，在列前冠以表名需要大量的输入。Oracle提供了一种简便的方法，就是使用表的别名。

SELECT e.empno, e.ename, e.sal, e.deptno, e.loc

FROM emp e,dept d

WHERE e.deptno=d.deptno

AND e.sal >= 1500

表使用别名的一些指南：

表的别名在FORM子句中定义，别名放在表名之后，它们之间用空格隔开

别名一经定义，在整个查询语句中就只能使用表的别名而不能再使用表名

表的别名只在所定义的查询语句中有效

应该选择有意义的别名，表的别名最长为30个字符，但越短越好

 

笛卡尔乘积（乘积连接）

如果您在前面的查询语句中忘记了WHERE子句，那么会得到什么样的而结果呢？

SELECT e.empno, e.ename, e.sal, e.deptno, d.loc

FROM emp e,dept d

查询得到的记录数变成了两个表中记录的乘积

笛卡尔乘积（乘积连接）的形成条件：

查询语句中漏掉了连接条件（join condition）

查询语句中两个表中的所有行都满足连接条件（join condition）

查询语句中的连接条件无效

 

自连接（Selfjoin）

查询所有分析员的头的信息

SELECT w.empno, w.ename, w.mgr, m.ename "M_Name", m.job "M_job"

FROM emp w, emp m

WHERE w.mgr=m.empno

AND w.job LIKE 'ANA%'

 

两个以上表的连接：

SELECT w.empno "W_Number", w.ename "W_Name", w.job "W_Job", w.sal "W_Salary", m.empno "M_Number", m.ename "M_Name", d.loc "Location"

FROM emp w, manager m, dept d

WHERE w.mgr= m.empno

AND m.deptno=d.deptno

AND w.job IN('CLERK','ANALYST')

查询的思路：在员工表emp中找到经理号w.mgr，再利用员工经理号在经理表manager中找到经理的信息，再利用经理的部门号deptno在部门表dept中找到经理所在部门的经理

把员工的职位不是文员CLERK或分析员ANALYST的员工排斥在查询结果之外

 

不等连接

在上面的连接中连接运算符都为等号（=）。我们也可以使用其他的运算符，其他的运算符所产生的连接叫做不等连接

SELECT e.empno, e.ename, e.job, e.sal, s.grade

FROM emp e, salgrade s

WHERE e.sal BETWEEN  s.losal AND s.hisal

AND s.grade >2

 

外连接

为了介绍外连接，先查看一下dept表中的信息

SELECT * FROM dept;

我们可以发现有些信息没有显示在相等连接的结果中，这是因为相等连接只显示与deptno相等的查询结果，有时候我们需要显示一些不满足的记录

SELECT empno， ename, sal, emp.deptno, dept.deptno, loc

FROM emp, dept

WHERE emp.deptno(+) = dept.deptno;

外连接的连接运算符为(+).该连接运算符既可以放在等号的左边也可以放在等号的右边。

注意：放置在进行比较的列的后面，并且一定要放在缺少对应信息的那一面

 

using子句在连接的使用

可以使用using关键字来简化连接查询，但是只是在查询满足下面两个条件时，才能使用using关键字进行简化。

1、查询必须是等值连接

2、等值连接中的列必须具有相同的名称和类似数据类型

SELECT * 

FROM ASSESSOR_INFO ai

JOIN OPERATOR_INFO oi using(OPERATOR_ID)

达到的效果和自然连接的效果相同。

使用using简化连接的时候需要注意几点：

1、在using子句和select子句都不能为using使用的列指定表名或者别名

2、如果再连接查询中使用了两个表中相同的多个列，那么可以在using子句中指定多个列名，形如：

SELECT ... FROM table1 INNER JOIN table2

USING (column1,column2)

 

自然连接natural join

表明两表进行自然连接，基于两表中的所有同名字段,也是等值连接

SELECT * 

FROM ASSESSOR_INFO ai

NATURAL JOIN OPERATOR_INFO oi

 

乘积连接cross join

得到笛卡尔积的结果

SELECT * 

FROM ASSESSOR_INFO

CROSS JOIN OPERATOR_INFO;

在所引用的列中药使用表名或别名

相等连接条件放在ON子句中

 

使用ON子句的连接

SELECT e.empno, e.ename, e.sal, e.deptno, d.loc

FROM emp e

JOIN dept d

ON  (e.deptno =d.deptno)

与前面的相等连接的结果完全相同，在使用ON子句的连接时要注意以下的约定。

在所引用的列中药使用表名或者列名

相等连接条件放在ON子句中。

 

使用ON子句的多表连接和附加条件

SELECT w.empno "W_Number", w.ename "W_Name", w.job "W_Job", w.sal "W_Salary", m.empno "M_Number", m.ename "M_Name", d.loc "Location"

FROM emp w

JOIN dept d

ON m.deptno =d.deptno

WHERE w.job IN ('CLERK','ANALYST')

 

 

左外连接

SELECT empno, ename, sal, emp.deptno, dept.deptno, loc

FROM dept

LEFT OUTER JOIN emp 

ON (emp.deptno =dept.deptno)

与前面(+)放在右边的使用形成的结果相同，不过使用的是SQL1999的写法。

OUTER可以省略，可以变成这样：

SELECT empno, ename, sal, emp.deptno, dept.deptno, loc

FROM dept

LEFT JOIN emp

ON (emp.deptno =dept.deptno)

 

右外连接

SELECT e.empno， e.sal， e.deptno， d.deptno， d.loc

FROM emp e

RIGHT OUTER JOIN dept d

ON (e.deptno = d.deptno);

也和(+)放在左边只是写法的区别，OUTER可以省略，也表示右外连接

 

全外连接

SELECT empno, sal, manager.deptno, dept.deptno, loc

FROM manager

FULL OUTER JOIN dept

ON (manager.deptno = dept.deptno);

两个表中的记录都要表现出来，没有使用NULL

多表连接减少冗余，规范表产生问题：如何在多个表中得到所需要的信息呢？Oracle使用连接Join来完成查询的。Oracle提供了4种类型的连接：相等链接（Equijoin）、自连接（Selfjoin）、不等连接（Nonequijoin）和外连接（Outerjoin）
相等链接：SELECT empno,ename,sal,emp.deptno,locFROM emp,deptWHERE emp.deptno=dept.deptno相等连接，即两个表中的记录的deptno值完全相等时才进行连接。通常这种连接查询涉及到主键和外键。相等连接也叫简单连接或者内连接。一些指南：要连接的表都要放在FROM子句中，表名用逗号分开连接的条件放在WHERE子句中要进行n个表的连接至少n-1个连接条件如果多个表中有相同名的列的时候，在这些列的前面要冠以表名来区分它们，表明和列名之间使用点分隔开可以使用表的别名替代表的全名SELECT emp.empno, emp.ename, emp.sal, emp.deptno, dept.locFROM emp,deptWHERE emp.deptno=dept.deptnoAND sal >= 1500除了连接条件外，我们还可以在WHERE子句中加入其它的条件，这些条件由AND运算符组合起来。连接表中别名的使用如果一个表的名字很长，在列前冠以表名需要大量的输入。Oracle提供了一种简便的方法，就是使用表的别名。SELECT e.empno, e.ename, e.sal, e.deptno, e.locFROM emp e,dept dWHERE e.deptno=d.deptnoAND e.sal >= 1500表使用别名的一些指南：表的别名在FORM子句中定义，别名放在表名之后，它们之间用空格隔开别名一经定义，在整个查询语句中就只能使用表的别名而不能再使用表名表的别名只在所定义的查询语句中有效应该选择有意义的别名，表的别名最长为30个字符，但越短越好笛卡尔乘积（乘积连接）如果您在前面的查询语句中忘记了WHERE子句，那么会得到什么样的而结果呢？SELECT e.empno, e.ename, e.sal, e.deptno, d.locFROM emp e,dept d查询得到的记录数变成了两个表中记录的乘积笛卡尔乘积（乘积连接）的形成条件：查询语句中漏掉了连接条件（join condition）查询语句中两个表中的所有行都满足连接条件（join condition）查询语句中的连接条件无效自连接（Selfjoin）查询所有分析员的头的信息SELECT w.empno, w.ename, w.mgr, m.ename "M_Name", m.job "M_job"FROM emp w, emp mWHERE w.mgr=m.empnoAND w.job LIKE 'ANA%'两个以上表的连接：SELECT w.empno "W_Number", w.ename "W_Name", w.job "W_Job", w.sal "W_Salary", m.empno "M_Number", m.ename "M_Name", d.loc "Location"FROM emp w, manager m, dept dWHERE w.mgr= m.empnoAND m.deptno=d.deptnoAND w.job IN('CLERK','ANALYST')查询的思路：在员工表emp中找到经理号w.mgr，再利用员工经理号在经理表manager中找到经理的信息，再利用经理的部门号deptno在部门表dept中找到经理所在部门的经理把员工的职位不是文员CLERK或分析员ANALYST的员工排斥在查询结果之外不等连接在上面的连接中连接运算符都为等号（=）。我们也可以使用其他的运算符，其他的运算符所产生的连接叫做不等连接SELECT e.empno, e.ename, e.job, e.sal, s.gradeFROM emp e, salgrade sWHERE e.sal BETWEEN  s.losal AND s.hisalAND s.grade >2外连接为了介绍外连接，先查看一下dept表中的信息SELECT * FROM dept;我们可以发现有些信息没有显示在相等连接的结果中，这是因为相等连接只显示与deptno相等的查询结果，有时候我们需要显示一些不满足的记录SELECT empno， ename, sal, emp.deptno, dept.deptno, locFROM emp, deptWHERE emp.deptno(+) = dept.deptno;外连接的连接运算符为(+).该连接运算符既可以放在等号的左边也可以放在等号的右边。注意：放置在进行比较的列的后面，并且一定要放在缺少对应信息的那一面using子句在连接的使用可以使用using关键字来简化连接查询，但是只是在查询满足下面两个条件时，才能使用using关键字进行简化。1、查询必须是等值连接2、等值连接中的列必须具有相同的名称和类似数据类型SELECT * FROM ASSESSOR_INFO aiJOIN OPERATOR_INFO oi using(OPERATOR_ID)达到的效果和自然连接的效果相同。使用using简化连接的时候需要注意几点：1、在using子句和select子句都不能为using使用的列指定表名或者别名2、如果再连接查询中使用了两个表中相同的多个列，那么可以在using子句中指定多个列名，形如：SELECT ... FROM table1 INNER JOIN table2USING (column1,column2)自然连接natural join表明两表进行自然连接，基于两表中的所有同名字段,也是等值连接SELECT * FROM ASSESSOR_INFO aiNATURAL JOIN OPERATOR_INFO oi乘积连接cross join得到笛卡尔积的结果SELECT * FROM ASSESSOR_INFOCROSS JOIN OPERATOR_INFO;在所引用的列中药使用表名或别名相等连接条件放在ON子句中使用ON子句的连接SELECT e.empno, e.ename, e.sal, e.deptno, d.locFROM emp eJOIN dept dON  (e.deptno =d.deptno)与前面的相等连接的结果完全相同，在使用ON子句的连接时要注意以下的约定。在所引用的列中药使用表名或者列名相等连接条件放在ON子句中。
使用ON子句的多表连接和附加条件SELECT w.empno "W_Number", w.ename "W_Name", w.job "W_Job", w.sal "W_Salary", m.empno "M_Number", m.ename "M_Name", d.loc "Location"FROM emp wJOIN dept dON m.deptno =d.deptnoWHERE w.job IN ('CLERK','ANALYST')左外连接SELECT empno, ename, sal, emp.deptno, dept.deptno, locFROM deptLEFT OUTER JOIN emp ON (emp.deptno =dept.deptno)与前面(+)放在右边的使用形成的结果相同，不过使用的是SQL1999的写法。OUTER可以省略，可以变成这样：SELECT empno, ename, sal, emp.deptno, dept.deptno, locFROM deptLEFT JOIN empON (emp.deptno =dept.deptno)右外连接SELECT e.empno， e.sal， e.deptno， d.deptno， d.locFROM emp eRIGHT OUTER JOIN dept dON (e.deptno = d.deptno);也和(+)放在左边只是写法的区别，OUTER可以省略，也表示右外连接全外连接SELECT empno, sal, manager.deptno, dept.deptno, locFROM managerFULL OUTER JOIN deptON (manager.deptno = dept.deptno);两个表中的记录都要表现出来，没有使用NULL