Interested to Buy Any Domain ? << Click Here >> for more details...
What is the difference between normal and corelated subqueries?
- 2 Answers
- 9832 Views
- I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
Is truncate autocommit in oracle?
How to connect to the server with user account: sys?
what is IDE,DMV in sql server?
What is truncate oracle?
What is the difference between I and G in Oracle?
How to find the duplicate rows count from employees table in oracle?
How to sort output in descending order in oracle?
How do I know if oracle is installed on windows?
How to convert a date to char in oracle? Give one example.
What is background process in Oracle?
How to build data dictionary view an new database?
How to use values from other tables in update statements using oracle?
How to assign data of the deleted row to variables?
What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
is there a tool to trace queries, like profiler for sql server?
