Oracle数据库入门之多表连接与子查询

核心提示：平时很少使用自连接，但有时确实只有采用自连接的方式才能解决某些问题问题：如何查得每个员工及其上司的工号和姓名举例：select a.empno, a.ename, a.mgr, b.ename from emp a, emp b where a.mgr=b.empno;SQL99连接语法概述：SQL1999规范中规定的

平时很少使用自连接，但有时确实只有采用自连接的方式才能解决某些问题
问题：如何查得每个员工及其上司的工号和姓名
举例：select a.empno, a.ename, a.mgr, b.ename from emp a, emp b where a.mgr=b.empno;



SQL99连接语法
概述：SQL1999规范中规定的连接查询语法。两个以上的表进行连接时应依次分别指定相临的两个表之间的连接条件
语法：select 字段列表
from table1
[cross join table2]|
[natural join table2]|
[join table2 using(字段名)]|
[join table2 on(table1.column_name=table2.column_name)]|
[(left|right|full outer)join table2 on(table1.column_name=table2.column_name)];
[cross join table3]|
[natural join table3]|
[join table3 using(字段名)]|
[join table3 on(table2.column_name=table3.column_name)]|
[(left|right|full outer)join table3 on(table2.column_name=table3.column_name)]...;
说明：如果感觉新语法比较混乱，在没有强制性要求的时候，完全可以使用旧语法，二者不存在效率上的差异



交叉连接(Cross join)
概述：Cross join产生了一个笛卡尔集，其效果等同于在两个表进行连接时未使用WHERE子句限定连接条件
举例：select empno, ename, dname from emp a cross join dept b;--交叉连接实际上没有太大的意义



自然连接(Natural join)
概述：Natural join基于两个表中的全部同名列建立连接。从两个表中选出同名列的值均对应相等的所有行
如果两个表中同名列的数据类型不同，则出错。而且不允许在参照列上使用表名或别名作为前缀
举例：select demno, ename, sal, deptno, dname from emp natural jon dept;



Using子句
概述：如果不希望参照被连接表的所有同名列进行等值连接，自然连接将无法满足要求
可以在连接时使用USING子句来设置用于等值连接的列(参照列)名
同样不允许在参照列上使用表名或别名作为前缀
举例：select empno, ename, sal, deptno, dname form emp join dept using(deptno);



On子句
概述：如果要参照非同名的列进行等值连接，或想设置任意的连接条件，可以使用ON子句
举例：select empno, ename, sal, emp.deptno, dname from emp join dept on(emp.deptno=dept.deptno);



子查询(Sub Query)
概述：子查询在主查询前执行一次，主查询使用子查询的结果。比如查询所有比张三工资高的员工信息
子查询分为单行子查询(返回一行结果)和多行子查询(返回多行结果)两大类
语法：select 字段列表 form table where 表达式 operator(select 字段列表 from table);
比如select * from emp where sal>(select sal from emp where empno=7654);
注意：基于未知值的查询应考虑使用子查询。子查询必须包含在括号内
建议将子查询放在比较运算符的右侧，以增强可读性。除非进行Top—N分析，否则不要在子查询中使用ORDER BY子句
对单行子查询可以使用单行记录比较运算符。而对多行子查询则只能使用多行记录比较运算符
空值：如果子查询未返回任何行，则主查询也不会返回任何结果
比如select * from where sal>(select sal from emp where empno=8888);--不会返回任何结果
多值：如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符
比如select * from emp where sal>(select avg(sal) from group by deptno);--非法



TopN查询(TopN分析)