【Oracle数据库】多表连接

多表连接概述

关于连接

SELECT table1.column,table2.columnFROM table1,table2WHERE table1.column1=table2.column2在WHERE子句中书写连接条件如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀N个表相连时，至少需要N-1个连接条件
连接的类型
按连接条件分：等值连接、非等值连接按其他连接方法分：外部连接、内部连接
多表连接写法
Oracle写法：Oracle公司提供的写法，绝大多数符合SQL标准，其他关系型数据库也适用ANSI SQL-99写法：ANSI标准提供的写法，所有关系型数据库必须支持
Oracle自有连接写法
笛卡尔积
笛卡尔积是第一个表中的所有行和第二个表中的所有行都发生连接产生情况：连接条件被省略或无效为了避免笛卡尔积的产生，通常需要在WHERE子句中包含一个有效的连接条件
等值连接
用等值连接检索数据
SELECT empno,ename,e.deptno,d.deptno,locFROM emp e,dept dWHERE e.deptno=d.deptno
使用AND运算符增加其他查询条件
SELECT empno,ename,e.deptno,d.deptno,locFROM emp e,dept dWHERE e.deptno=d.deptnoAND loc='NEW YORK'
限制歧义列名
在用到多个表时可以使用表名作前缀来限定列通过使用表前缀可以提高性能，同时使用表的别名可以简化查询语句(一旦在FROM子句中为表指定了表别名，则必须在其他的子句中都是用表别名，而不允许再使用原来的表名)通过使用列的别名可以区分来自不同表但是名字相同的列
非等值连接
用非等值连接检索数据
SELECT ename,sal,gradeFROM emp,salgradeWHERE sal BETWEEN losal AND hisal
多表连接的写法
分析要查询的列都来自于哪些表，构成FROM子句分析这些表之间的关联关系，如果表之间没有直接关联关系，而是通过另一个中间表关联，则也要在FROM子句中补充中间关联表在WHERE子句中补充表之间的关联关系，通常N个表，至少要有N-1个关联关系分析是否还有其他限制条件，补充到WHERE子句的表关联关系之后，作为限制条件根据用户想要显示的信息，补充SELECT子句分析是否有排序要求，如果排序要求中还涉及到其他表，则也要进行第2步补充排序字段所在的表，并且添加表之间的关联关系
外部连接
在多表连接时，可以使用外部连接来查看哪些行，按照连接条件没有被匹配上，外部连接的符号是“(+)”语法：
SELECT table1.column,table2.columnFROM table1,table2WHERE table1.column(+)=table2.column
SELECT table1.column,table2.columnFROM table1,table2WHERE table1.column=table2.column(+)外部连接就好像是为符号(+)所在边的表增加一个“万能”的行，这个行全部由空值组成，它可以和另一边的表中所有不满足连接条件的行进行连接外部连接示例：
SELECT ename,d.deptno,dnameFROM emp e,dept dWHERE e.deptno(+)=d.deptnoORDER BY e.deptno
自身连接
自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样自连接示例：
SELECT worker.ename||' leader is '||manager.enameFROM emp worker,emp managerWHERE worker.mgr=manager.empno
ANSI SQL-99标准连接写法
ANSI SQL-99标准的连接语法
SELECT table1.column,table2.columnFROM table1    [CROSS JOIN table2]    [NATURAL JOIN table2]    [JOIN table2 USING (column_name)]|    [JOIN table2 ON (table1.column=table2.column)]|    [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column=table2.column)]
交叉连接
交叉连接会产生两个表的交叉乘积，和两个表之间的笛卡尔积是一样的交叉连接使用CROSS JOIN子句完成
SELECT empno,ename,sal,d.deptno,locFROM emp eCROSS JOIN dept
自然连接
自然连接是对两个表之间所有相同名字和数据类型的列进行的等值连接如果两个表之间相同名称的列的数据类型不同，则会产生错误在使用自然联结时，不能为列指定表名或表别名，否则Oracle系统会弹出“ORA-25155：NATURAL连接中使用的列不能有限定词”的错误提示自然连接使用NATURAL JOIN子句完成
SELECT empno,ename,sal,deptno,locFROM empNATURAL JOIN dept
USING子句
USING子句可以指定用某个或某几个相同名字和数据类型的列作为连接条件如果有若干个列名称相同但数据类型不同，自然连接子句可以用USING子句来替换，以指定产生等值连接的列如果有多于一个列都匹配的情况，使用USING子句只能指定其中的一列USING子句中用到的列不能使用表名和表别名作为前缀NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用USING子句示例
SELECT empno,ename,sal,deptno,locFROM emp JOIN dept USING (deptno)WHERE deptno=20
内连接
使用关键字INNER JOIN来实现的常用多表关联查询的方式INNER关键字可以省略，即当只使用JOIN关键字时，语句只表示内部连接在使用内连接查询多个表时，必须在FROM子句之后定义一个ON子句使用内连接进行行多表查询时，返回的查询结果中只包含符合查询条件和连接条件的行
ON子句
使用ON子句可以指定任意连接条件或指定要连接的列ON子句可以提高代码的可读性ON子句示例
SELECT e.empno,e.ename,e.deptno,d.deptno,loc,m.enameFROM emp eJOIN dept d ON (e.deptno = d.deptno)JOIN emp m ON (e.mgr = m.empno)
左外连接
左外连接以FROM子句中的左边表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会被显示出来
SELECT ename,e.deptno,locFROM emp eLEFT OUTER JOIN dept d ON (e.deptno = d.deptno)
右外连接
右外连接以FROM子句中的右边表为基表，该表所有行数据按照连接条件无论是否与左边表能匹配上，都会被显示出来
SELECT ename,e.deptno,locFROM emp eRIGHT OUTER JOIN dept d ON (e.deptno = d.deptno)
全外连接
全外连接返回两个表等值连接结果，以及两个表中所有等值连接失败的记录
SELECT ename,e.deptno,locFROM emp eFULL OUTER JOIN dept d ON (e.deptno = d.deptno)