Thread: subquery

hi
i want to display lowest sal from emp table without using min function and 'ALL'

kindly post what you have tried so far to continue discussion on the topic.

try the below query

select sal from emp where sal=(
select a.sal from(select sal from emp where sal is not null order by sal)a
where rownum=1)

What is difference between subquary and co-related subquary? where we can use co-related subquary?

Subquery is form of select statements it is appear inside another one sql statements (or) nested query ie..parent statements.subquery can be used DML statements .we can also refer to more than one table,returns more than one value we need to use any,all,in and not in operators.No limit on the no of queries included in a where clause.

example:

select* from employee_master where emp_no=(select emp_no from employee_details where emp_name='ramesh');

* Always enclose sub query within the parenthesis.
* Subquery will be evaluated first query followed by the main query.

Originally Posted by rameshkumar.oracle

What is difference between subquary and co-related subquary? where we can use co-related subquary?

In subqueries inner query is executed first. outer query is executed using the results returned by the inner query.

In correlated subqyery inner query is executed once for each row returned by the parent statement.

select * from acc_bank_master where bank_code in(select a.bank_code
from (select bank_code from acc_bank_master Order by bank_code)a where rownum<2);
Try this Query if Column used in Order by in Indexed.

I heard that using the co-related subquery will degrade the performance of the whole query lot. then why we can go for that, is there is any alternate option in oracle to do the same thing as co-related sub query do .

Try this select sal from (select sal from emp order by sal ) group by rownum,sal having rownum=1

table have
------------


SQL> select name,salary from employe order by salary;

NAME SALARY
---------- ----------
MARUTHU 2000
RAJESH 5000
ARUL 8000
MANI 10000
SELVAM 10000
SUNIL 11000
DANIAL 13000
KUMARAN 15000
RAJA 17000
KAMAL 20000
KATHIR 20000

NAME SALARY
---------- ----------
ARUN 25000

12 rows selected.

select name,salary from(
select name,salary,row_number() over(order by salary asc) num
from employe)a where a.num = 1;

NAME SALARY
---------- ----------
MARUTHU 2000

(or)


select name,salary from(
select name,salary,row_number() over(order by salary asc) num
from employe)a where a.num = &n;