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.

What are dml statements in oracle?

1095

---

What is merge statement used for?

1024

---

Can select statements be used on views in oracle?

1027

---

What are the different types of record groups in oracle? Explain each of them

1025

---

WHEN CURSOR MOVES FROM ONE FORM TO ANOTHER FORM, HOW MANY TRIGGER WILL BE FIRED AND WHAT ARE THEIR SEQUENCE?

2311

---

How to rollback the current transaction in oracle?

1054

---

What are the uses of synonyms?

1138

---

What is an external table?

1020

---

src name sex a,male b,female c,male d,female Required output : male female a,b c,d tried pivot but was not successfull select * from src pivot (max(name) for sex in ('MALE','FEMALE'));

1690

---

Is truncate autocommit in oracle?

1087

---

What is the database name in oracle?

1021

---

How to write a query with a right outer join in oracle?

1169

---

What are the differences between blob and clob in oracle?

1089

---

How to omit columns with default values in insert statement in oracle?

1046

---

What are oracle functions?

1030

---