What is the difference between normal and corelated subqueries?
Answers were Sorted based on User's Feedback
Answer / arun joy
"Correlated" means it is linked back to the main query. For
example:
SELECT * FROM dept d
WHERE EXISTS
( SELECT 1 FROM emp
WHERE deptno = d.deptno );
This means: for each row in DEPT, go and check in EMP for a
row with the same deptno, and if you find one, report
success. Logically you are asking for the EXISTS check to
be repeated for every single row in DEPT (although the
optimizer may turn it into a hash join or similar if it
decides that will be more efficient). The fact that the
WHERE clause of the subquery refers back to the main query
makes it a correlated subquery.
Now if you'd just said:
SELECT * FROM dept d
WHERE EXISTS
( SELECT 1 FROM emp );
then Oracle can perform the subquery once only at the
start. Once it's confirmed that there is at least 1 record
in EMP then it knows not to re-run the subquery for each
row in DEPT.
Another non-correlated example:
SELECT * FROM dept d
WHERE d.deptno IN
( SELECT deptno
FROM emp
WHERE job = 'CLERK' );
Logically you are asking it to first find the set of
DEPTNOs in EMP that meet the condition, then use that to
retrieve the relevant rows from DEPT.
Is This Answer Correct ? | 14 Yes | 1 No |
Answer / deva
corelated subquery is the one where the in-query takes the
input from the outer query. This happens for every value in
the in-query.
Is This Answer Correct ? | 12 Yes | 2 No |
How to Remove the 3rd highest salary person record from table?
What is a Data Dictionary ?
Please explain drop constraint oracle?
What is ASM (Automatic Storage Management) in Oracle?
Does a Before form trigger fire when the parameter form is suppressed ?
consider some table with 4 r 5 columns in that 1 col is DATE type. The data is like that,For each date in that col some 3 fields r there but all the records r having different data. Now i want to display all the columns by performing grouping on the date field ( as SELECTION Operator(*) cannot be used with group function having only one "group by clause". how to do this? can any one help me in finding out the solution plss?
How to find the date and time of last updated table?
am completed mca2011 how get certification in oracle
How a tablespace is related to data files?
4. Display the order number and client number from the ORDER table. Output the result in the format. Client <clientno> ordered <orderno>
Her departmandaki isçilerden empno' su ikinci sirada olan isçilerin empno, deptno, hiredate, sira_no bigilerini döndüren sorguyu yaziniz?
Please explain joins in oracle?