sql 子查询的一些事例_SQL

sql 子查询的一些例子

1、单行子查询
        select ename,deptno,sal
        from emp
        where deptno=(select deptno from dept where loc='NEW YORK')；

     2、多行子查询
        SELECT ename,job,sal
        FROM EMP
        WHERE deptno in ( SELECT deptno FROM dept WHERE dname LIKE 'A%')；

     3、多列子查询
        SELECT deptno,ename,job,sal
        FROM EMP
        WHERE (deptno,sal) IN (SELECT deptno,MAX(sal) FROM EMP GROUP BY deptno)；

     4、内联视图子查询
       (1)SELECT ename,job,sal,rownum
          FROM (SELECT ename,job,sal FROM EMP ORDER BY sal)；
       (2)SELECT ename,job,sal,rownum
          FROM ( SELECT ename,job,sal FROM EMP ORDER BY sal)
          WHERE rownum<=5；

     5、在HAVING子句中使用子查询
        SELECT deptno,job,AVG(sal) FROM EMP GROUP BY deptno,job HAVING AVG(sal)>(SELECT sal FROM EMP WHERE ename='MARTIN')；

有三种基本的子查询。它们是：

●在通过 IN 或由 ANY 或 ALL 修改的比较运算符引入的列表上操作。
●通过未修改的比较运算符引入且必须返回单个值。
●通过 EXISTS 引入的存在测试。

1.带in的嵌套查询

select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal in (select sal from scott.emp where ename=’’WARD’’);
上述语句完成的是查询薪水和WARD相等的员工，也可以使用not in来进行查询。

2.带any的嵌套查询
通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一个进行比较，只要有一次比较的结果为TRUE，则ANY测试返回TRUE。

select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >any(select sal from scott.emp where job=’’MANAGER’’);
等价于下边两步的执行过程：
（1）执行“select sal from scott.emp where job=’’MANAGER’’”
（2）查询到3个薪水值2975、2850和2450，父查询执行下列语句：
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >2975 or sal>2850 or sal>2450;

3.带some的嵌套查询

select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal =some(select sal from scott.emp where job=’’MANAGER’’);
等价于下边两步的执行过程：
（1）子查询,执行“select sal from scott.emp where job=’’MANAGER’’”。
（2）父查询执行下列语句。
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal =2975 or sal=2850 or sal=2450;
带【any】的嵌套查询和【some】的嵌套查询功能是一样的。早期的SQL仅仅允许使用【any】，后来的版本为了和英语的【any】相区分，引入了【some】，同时还保留了【any】关键词。

4.带all的嵌套查询
通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一个进行比较，只要有一次比较的结果为FALSE，则ALL测试返回FALSE。
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >all(select sal from scott.emp where job=’’MANAGER’’);
等价于下边两步的执行过程：
（1）子查询,执行“select sal from scott.emp where job=’’MANAGER’’”。
（2）父查询执行下列语句。
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >2975 and sal>2850 and sal>2450;

5.带exists的嵌套查询

select emp.empno,emp.ename,emp.job,emp.sal from scott.emp,scott.dept where exists (select * from scott.emp where scott.emp.deptno=scott.dept.deptno);

6.并操作的嵌套查询

并操作就是集合中并集的概念。属于集合A或集合B的元素总和就是并集。
(select deptno from scott.emp) union (select deptno from scott.dept);

7.交操作的嵌套查询

交操作就是集合中交集的概念。属于集合A且属于集合B的元素总和就是交集。

(select deptno from scott.emp) intersect (select deptno from scott.dept);

8.差操作的嵌套查询

差操作就是集合中差集的概念。属于集合A且不属于集合B的元素总和就是差集。
(select deptno from scott.dept) minus (select deptno from scott.emp);
注意：并、交和差操作的嵌套查询要求属性具有相同的定义，包括类型和取值范围。

左手边是一个标量表达式列表．右手边可以是一个等长的标量表达式的列表，或者一个圆括弧括起来的子查询，该查询必须返回很左手边表达式书目完全一样的字段．另外，该子查询不能返回超过一行的数量．(如果它返回零行，那么结果就是 NULL．)左手边逐行与右手边的子查询结果行，或者右手边表达式列表进行比较．目前，只允许使用 = 和 <> 操作符进行逐行比较．如果两行分别是相等或者不等，那么结果为真．

通常，表达式或者子查询行里的 NULL 是按照 SQL 布尔表达式的一般规则进行组合的．如果两个行对应的成员都是非空并且相等，那么认为这两行相等；如果任意对应成员为非空且不等，那么该两行不等；否则这样的行比较的结果是未知(NULL)

参考：http://www.360doc.com/content/11/0407/17/5789627_107867377.shtml

http://blog.csdn.net/devercn/article/details/22986

2楼jgsh20124天前 21:44: exists 用的还是蛮多的

1楼emoven4天前 19:16: 带any和same的查询很容易混淆