[Oracle 学习笔记] 04 子查询和集合操作

　　子查询 根据返回结果不同分为单行子查询、多行子查询、多列子查询。 单行子查询返回一行一列数据给外部的SQL语句；多行子查询返回多行单列数据给外部的主SQL语句；多列子查询返回多列（单行或多行）数据给外部的SQL语句。

　　有时，子查询引用了外部住查询中包含的表列，并且子查询不能再外部查询之前求职，需要依靠外部查询才能获得值，这样的子查询被称为相关子查询。

　　SQL语言允许子查询嵌套，Oracle 10g的嵌套深度允许高达255级。嵌套子查询是查询效率降低，使用时需要加以考虑。

　　单行子查询

　　单行子查询可以用于主句的WHERE子句、HAVING 子句 和 FROM 子句中。

　　WHERE : 可以使用比较符   SELECT * FROM teachers WHERE wage < (SELECT AVG(wage) FROM teachers);

　　HAVING : 子查询结果作为主查询分组条件   SQL> SELECT department_id, AVG(wage) AS "平均工资" FROM teachers;

　　FROM ：该子查询的结果作为主查询的视图。 SQL> SELECT * FROM (SELECT * FROM students WHERE sex='男') WHERE specialty = '计算机';

　　多行子查询

　　返回多行单列数据的子查询被成为多行子查询。当WHERE 子句中使用多行子查询时，必须要使用多行比较符 IN  ANY  ALL  作用如下：

• IN: 匹配于子查询结果的任意一个值，结果为真；否则为假
• ANY:只要符合子查询结果中的任意一个值，结果为真；否则为假
• ALL:必须要符合子查询结果的所有值结果的所有值结果为真；否则为假。

　　SQL> SELECT student_id, name FROM students WHERE student_id IN (SELECT student_id FROM students WHERE name LIKE '王%');

　　SQL> SELECT course_id, course_name FROM courses WHERE course_id NOT IN (SELECT course_id FROM students_grade);

　　SQL> SELECT * FROM teachers WHERE wage < ANY (SELECT AVG(wage) FROM teachers GROUP BY department_id);

　　SQL> SELECT * FROM teachers WHERE wage > ALL (SELECT AVG(wage) FROM teachers GROUP  BY department_id);

　　多列子查询

　　是指返回多列（单行或多行）数据的子查询语句。返回单行多列数据的子查询可以参照单行子查询的例子来编写查询语句；返回多行多列数据的子查询可以参照多行子查询的例子来编写查询语句。

　　SQL> SELECT * FROM students WHERE (specialty, dob) = (SELECT specialty, dob FROM students WHERE name='王天一');

　　相关子查询

　　子查询引用了外部查询中包含的一列或者多列，子查询不能再外部查询之前求职，需要依靠外部查询才能获得值，这样的子查询被称为相关子查询。

　　SQL> SELECT * FROM teachers t1 WHERE wage > (SELECT AVG(wage) FROM teachers t2 WHERE t2.department_id = t1.department_id);

　　在相关子查询中经常使用EXISTS、NOT EXISTS、 IN、 NOT IN等操作符。操作符EXISTS 用于检查子查询返回的记录航是否存在，操作符NOT EXISTS用于检查子查询返回的记录航是否不存在。

　　SQL> SELECT course_id course_name FROM courses c WHERE EXISTS (SELECT 2 FROM students _grade sg WHERE sg.course_id = c.course_id);

 

　　集合操作 并交差三种  UNION (UNION ALL)   INTERSECT   MINUS

　　UNION: 用来得到两个查询结果集的并集，并去除重复的行。

　　UNION ALL: 用于得到两个查询结果集的并集，保留重复的行。

　　INTERSECT： 用于得到两个查询结果的交集，交集以结果的第一列进行排序。

　　MINUS: 用于得到两个查询结果集的差集，差集以结果的第一列进行排序。

　　　　SELECT sentence1 [UNION ALL | UNION | INTERSECT | MINUS] SELECT sentence2；

　　限制如下：

• 对于BLOB\CLOB\BFILE\VARRAY或潜逃表类型的列，不能使用集合操作符。
• 对于LONG类型的列，不能使用集合操作符，UNION\ INTERSECT\ MINUS
• 如果选择的列表包含了表达式，则必须腰围表达式指定列别名。