Nesting of queries, i.e., a query which is
inside another query. Here the outer
query is called the parent query, which gets a result from the inner
query. So the inner query gets executed
first and based on its result the outer query gets executed.
Note1:
- Do no add an order by clause to a sub query.
- Use single row operator with single row sub query.
- Use multiple row operators with multiple-row sub query.
- The single row sub query operators are =, >, <, <=, >=, <>, ^=, !=
- The multiple row operators are IN, ANY, ALL
Note: 2
- ‘=any’ means IN and
- ‘!=all’ means NOT IN
Note: 3
- <any means less than maximum
- >any means greater than minimum
- <all means less than minimum
- >all means greater than maximum
SUB-QUERIES:SINGLE
ROW SUB QUERY
SQL> select * from emp where
sal>(select sal from emp where ename='SCOTT');
EMPNO
ENAME JOB HIREDATE SAL DEPTNO
--------- ---------- --------- --------- --------- --- ---------
7839
KING PRESIDENT 17-NOV-81 5000 10
SQL >select * from emp where
sal>=(select sal from emp where ename='SCOTT');
EMPNO
ENAME JOB HIREDATE SAL DEPTNO
--------- ---------- --------- --------- --------- ---------
7788
SCOTT ANALYST 09-DEC-82 3000 20
7839
KING PRESIDENT 17-NOV-81 5000 10
7902
FORD ANALYST 03-DEC-81 3000 20
SQL >select ename,job from emp where
job=(select job from emp where empno=7369);
ENAME JOB
----------
---------
SMITH CLERK
ADAMS CLERK
JAMES CLERK
MILLER CLERK
SQL > select ename,job from emp where
job=(select job from emp where empno=7369)and sal>(select sal from emp where
empno=7876);
ENAME JOB
----------
---------
MILLER CLERK
SQL >select * from emp where
job='CLERK';
EMPNO ENAME JOB
HIREDATE SAL DEPTNO
--------- - --------- --------- --------- --------- --------------------
7369 SMITH CLERK 17-DEC-80 800 20
7876 ADAMS CLERK 12-JAN-83 1100
20
7900 JAMES CLERK 03-DEC-81 950 30
7934 MILLER CLERK
23-JAN-82 1300 10
SUB-QUERIES:
MULTIPLE ROW SUB QUERY
SQL >select empno,ename,sal from emp
where sal=any(select sal from emp,salgrade
where emp.sal between
salgrade.losal and salgrade.hisal);
EMPNO ENAME SAL
---------
---------- ---------
7369 SMITH 800
7900 JAMES 950
7876 ADAMS 1100
7521
WARD 1250
7654 MARTIN 1250
7934 MILLER 1300
7844 TURNER 1500
7499 ALLEN 1600
7782 CLARK 2450
7698 BLAKE 2850
7566 JONES 2975
7788 SCOTT 3000
7902 FORD 3000
7839 KING 5000
14 rows selected.
SQL >select empno,ename,sal from emp
where sal !=all (select sal from emp,salgrade where emp.sal between salgrade.losal and
salgrade.hisal);
no
rows selected
No comments:
Post a Comment