A form of an SQL statement that appears inside another SQL statement, also termed as Nested Query
The statement containing a sub-query is called a parent statement
The parent statement uses the rows returned by the sub-query
TYPES
Single row subqueries
Multi row subqueries
Multiple subqueries
Correlated subqueries
SINGLE ROW SUBQUERIES
In single row subquery, it will return one value.
Ex:
SQL> select * from emp where sal > (select sal from emp where empno = 7566);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
MULTI ROW SUBQUERIES
In multi row subquery, it will return more than one value. In such cases we should include operators like any, all, in or not in between the comparision operator and the subquery.
Ex:
SQL> select * from emp where sal > any (select sal from emp where sal between 2500 and
4000);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- -------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL> select * from emp where sal > all (select sal from emp where sal between 2500 and
4000);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------- ------ ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
MULTIPLE SUBQUERIES
There is no limit on the number of subqueries included in a where clause. It allows nesting of a query within a subquery.
Ex:
SQL> select * from emp where sal = (select max(sal) from emp where sal < (select
max(sal) from emp));
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
CORRELATED SUBQUERIES
A Subquery is evaluated once for the entire parent statement where as a correlated subquery is evaluated once for every row processed by the parent statement.
Ex:
SQL> select distinct deptno from emp e where 5 <= (select count(ename) from emp where e.deptno = deptno);
DEPTNO
----------
20
30
Hi Dayakar,
ReplyDeleteGrazie! Grazie! Grazie! Your blog is indeed quite interesting around SQL Subqueries! I agree with you on lot of points!
I have procedure which have cursor and this cursor is loading the data into some collection variables.
When I am executing this procedure and when it is loading the cursor values into collection variables.
I got the following exception:
--FAILED-ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/ora02/oradata/pujty/TEMP_01.dbf.
Even I am unable to see that file at that location also.
Could you please help me to understand this.
Appreciate your effort for making such useful blogs and helping the community.
Grazie,
Morne
Salaam,
ReplyDeleteSo bloody thorough! Ah! So happy and blissed out! I feel redeemed by reading out #topic. Keep up the good work!
I got a list of the INDEXES from DBA Team which are non value added to the Database with the following conditions
Quote:
1:Low selectivity indexes.
2:The number of distinct key values is less than 10 and the number of rows in table in greater than 100000.
3:All these normal(Balances -Tree )indexes.
They are asking the App Team to review these and to drop those indexes.
But when I analyzed those indexes some of the columns are using in the where conditions of the so many queries.
After applying the HINT of those indexes also the cost of query was decreasing.
Here my confusion was,if we really drop those indexes, will it increase the performance?
or
Do we need to convert those as BITMAP as they have low cardinality ?
Please help me to understand the concept behind this .
Thank you very much and will look for more postings from you.
Thanks and Regards
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDelete