Difference between sub query and nested query ?
- 19 Answers
- 62431 Views
- Keane India Ltd, TCS, I also Faced
- E-Mail Answers
Answer Posted / sunil
A correlated Subquery runs for the rows selected from the outer query. It takes the value from the outer query
and execute the inner query for that value
example:
select * from emp e
where e.deptno in(select d.deptno from dept d
where e.deptno = d.deptno);
in this query emp table's deptno will be passed into the inner query(select deptno from dept d where e.deptno = d.deptno).
And the inner query will execute only for that value from the outer query.
That's why it is called correlated subquery
In Nested subquery the inner query runs only once and pass the result set to the outer query.
example
select * from emp e
where e.deptno in(select d.deptno from dept d);
Here the inner query (select d.deptno form dept d) will run first and fetches all the rows from the dept table
and the outer query will select only the records that has the matching deptno in the result set fetched by the
inner query. The outer query will act as a nesting query and that is why this is called nested subquery.
Here in correlated subquery, the outer query executes first and the inner query will execute second.
But in Nested subquery, the inner query executes first and the outer query executes second.
Hope this helps.
thanks to binosh who helped me to understand this concept before posting here...
| Is This Answer Correct ? | 5 Yes | 0 No |
Post New Answer View All Answers
What are the parameters that we can pass through a stored procedure?
How to define a variable of a specific record type?
How to use windows user to connect to the server?
State all possible different index configurations a table can possibly have?
What is a snapshot in oracle database?
How to unlock the sample user account in oracle?
What is ceil and floor in oracle?
How do you find out from the RMAN catalog if a particular archive log has been backed-up?
What is oracle datasource?
State and explain the different types of data models?
How to define a data source name (dsn) in odbc manager?
What is the scope of a local variable?
Explain the blob datatype?
What is meant by joins? List out the types of joins.
What happens if the update subquery returns multiple rows?
