Interested to Buy Any Domain ? << Click Here >> for more details...
What is the difference between normal and corelated subqueries?
- 2 Answers
- 9764 Views
- I also Faced
- E-Mail Answers
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 create a new user account in oracle?
What are the attributes of the cursor?
Please help me how to write a Query to change the primary key constraint from 1 attribute to another attribute in a table
What is the database name in oracle?
you are sorting a table using "order by"(descending) clause of a column which contains a null value...whether that row containing null value will come first or last in the output??
What do you mean by a deadlock?
What is sharded cluster?
21. Using a set operator, display the client number of all clients who have ever placed an order and whose whose name does not contain the string Sm.
Explain the use of tables option in exp command.
Why do I get unexpected characters from 8-bit character sets in weblogic jdriver for oracle?
Assuming that you are an End User How to find that in the payment Batch some of the Invoice was Missing To pay How to find That??
How to define an anonymous block?
Oracle General 
