第8章多表连接与子查询

多表连接查询与子查询1多表连接查询1.1等值连接和非等值连接1.2外连接1.3自连接1.4SQL99表连接语法2子查询单行子查询、多行子查询、子查询中的空值问题、TopN查询分析什么是多表连接查询笛卡尔集Oracle表连接SQL/Oracle使用表连接从多个表中查询数据语法格式：select字段列表fromtable1,table2wheretable1.column1=table2.column2;说明：在Where子句中指定连接条件当被连接的多个表中存在同名字段时，必须在该字段前加上表名.作为前缀。连接的类型Oracle8i之前的表连接：等值连接（Equijoin）非等值连接（Non-Equijoin）外连接（Outerjoin）左外连接右外连接自连接（Selfjoin）Oracle9i新引入的的连接形式（支持SQL99规范）：交叉连接（Crossjoin）自然连接（Naturaljoin）使用Using子句建立连接使用On子句建立连接外连接（Outerjoin）左外连接右外连接全外连接等值连接(Equijoin)什么是等值连接？selectempno,ename,sal,emp.deptno,dnamefromemp,deptwhereemp.deptno=dept.deptno;多表连接中：可使用AND操作符增加查询条件；使用表别名可以简化查询使用表名（表别名）前缀可提高查询效率；为了连接n个表，至少需要n-1个连接条件非等值连接(Non-Equijoin)外连接(Outerjoin)使用外连接可以看到参与连接的某一方不满足连接条件的记录。外连接运算符为(+)传统的外连接分为左外连接和右外连接两种语法格式：select字段列表fromtable1,table2wheretable1.column1(+)=table2.column2;select字段列表fromtable1,table2wheretable1.column1=table2.column2(+);自连接(Selfjoin)SQL99连接语法SQL1999规范中规定的连接查询语法select字段列表fromtable1[crossjointable2]|[naturaljointable2]|[jointable2using(字段名)]|[jointable2on(table1.column_name=table2.column_name)]|[(left|right|fullouter)jointable2on(table1.column_name=table2.column_name)];交叉连接(Crossjoin)Crossjoin产生了一个笛卡尔集，其效果等同于在两个表进行连接时未使用WHERE子句限定连接条件;举例：selectempno,ename,sal,emp.deptno,dnamefromempcrossjoindept;自然连接(Naturaljoin))Naturaljoin基于两个表中的全部同名列建立连接从两个表中选出同名列的值均对应相等的所有行如果两个表中同名列的数据类型不同，则出错不允许在参照列上使用表名或者别名作为前缀举例：selectempno,ename,sal,deptno,dnamefromempnaturaljoindept;Using子句如果不希望参照被连接表的所有同名列进行等值连接，自然连接将无法满足要求，可以在连接时使用USING子句来设置用于等值连接的列（参照列）名。举例：selectempno,ename,sal,deptno,dnamefromempjoindeptusing(deptno);不允许在参照列上使用表名或者别名作为前缀On子句如果要参照非同名的列进行等值连接，或想设置任意的连接条件，可以使用ON子句。举例：selectempno,ename,sal,emp.deptno,dnamefromempjoindepton(emp.deptno=dept.deptno);多表连接使用SQL99连接语法，两个以上的表进行连接时应依次/分别指定相临的两个表之间的连接条件。select字段列表fromtable1[crossjointable2]|[naturaljointable2]|[jointable2using(字段名)]|[jointable2on(table1.column_name=table2.column_name)]|[(left|right|fullouter)jointable2on(table1.column_name=table2.column_name)][crossjointable3]|[naturaljointable3]|[jointable3using(字段名)]|[jointable3on(table2.column_name=table3.column_name)]|[(left|right|fullouter)jointable3on(table2.column_name=table3.column_name)]……;内连接和外连接内连接（InnerJoin）在SQL99规范中，内连接只返回满足连接条件的数据。外连接（OuterJoin）左外联接（LeftOuterJoin）两个表在连接过程中除返回满足连接条件的行以外，还返回左表中不满足条件的行，这种连接称为左外联接。右外联接（RightOuterJoin）两个表在连接过程中除返回满足连接条件的行以外，还返回右表中不满足条件的行，这种连接称为右外联接。满外联接（FullOuterJoin）Oracle9i开始新增功能，两个表在连接过程中除返回满足连接条件的行以外，还返回两个表中不满足条件的所有行，这种连接称为满外联接。子查询(SubQuery)问题引入如何查得所有比张三工资高的员工的信息子查询子查询在主查询前执行一次主查询使用子查询的结果语法格式：select字段列表fromtablewhere表达式operator(select字段列表fromtable);子查询的种类使用子查询注意事项在查询是基于未知值时应考虑使用子查询子查询必须包含在括号内建议将子查询放在比较运算符的右侧，以增强可读性。除非进行Top-N分析，否则不要在子查询中使用ORDERBY子句。对单行子查询使用单行运算符对多行子查询使用多行运算符单行子查询单行子查询只返回一行记录对单行子查询可使用单行记录比较运算符举例：select*fromempwheresal(selectsalfromempwhereempno=7566);子查询空值/多值问题如果子查询未返回任何行，则主查询也不会返回任何结果select*fromempwheresal(selectsalfromempwhereempno=8888);如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符select*fromempwheresal(selectsalfromempwhereempno=7566);如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符select*fromempwheresal(selectavg(sal)fromempgroupbydeptno);//非法多行子查询多行子查询返回多行记录对多行子查询只能使用多行记录比较运算符举例：select*fromempwheresalany(selectavg(sal)fromempgroupbydeptno);select*fromempwheresalall(selectavg(sal)fromempgroupbydeptno);select*fromempwherejobin(selectjobfromempwhereename='MARTIN'orename='SMITH');TopN查询在ORACLE中通常采用子查询的方式来实现TOPN查询语法格式：select字段列表from(select字段列表fromtableorderby排序字段)whererownum=n;select*from(select*fromemporderbysaldesc)whererownum=5;