What is the difference between correlated sub queries and inner queries?

What is the difference between correlated sub queries and
inner queries?

Question Posted / aquanaveen

4 Answers
14394 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

What is the difference between correlated sub queries and inner queries?..

Answer / vinay sir(datawarehousing tech

Guys,
First of all the question is not proper,it is supposed to
be "the difference between correlated sub queries and
simpe sub queries".

Corelated and Simple, both are Subqueries(having inner and
outer queries) but the way they execute is different.
Simple Subquery:Inner query executed first and then outer
query. Inner query executed only one time.

Corelated Sub query:Outer query is executed first and then
inner query.Outer query is executed many times.

Please find a common example where you can see the
execution difference.

Simple Subquery: select max(sal) from emp where sal not in
(select max(sal) from emp)
Corelated: Select a.sal from emp e where 1=(select count
(distinct(b.sal)) from emp b where a.sal<b.sal)

Is This Answer Correct ?

10 Yes

0 No

What is the difference between correlated sub queries and inner queries?..

Answer / shafi

Correlated Sub Query:
It is a sub-query (a query nested inside another query)
that uses values from the outer query in its WHERE clause.
The sub-query is evaluated once for each row processed by
the outer query

Inner query:
It is a query that is nested inside a SELECT, INSERT,
UPDATE, or DELETE statement or inside another subquery. A
subquery can return a set of rows or just one row to its
parent query. A scalar subquery is a query that returns
exactly one value: a single row, with a single column.

Is This Answer Correct ?

11 Yes

2 No

What is the difference between correlated sub queries and inner queries?..

Answer / yuvaevergreen

As said above, the main difference between a correlated Sub
Query and an inner query is that in a correlated sub query,
the inner query refers back to the column(s) in the outer
query i.e., the inner query is executed for each row of the
outer query.In the below query, the avg(salary) query is
executed for each row in the outer query.
-->>
sel * from employee as e where salary > (sel avg(salary) from
employee as b where e.deptno=b.deptno);

Is This Answer Correct ?

4 Yes

0 No

What is the difference between correlated sub queries and inner queries?..

Answer / girish

I think the process of sub query is other way arround

The inner query give each value got from the SELECT to the
outer query for processing...
So ..
The outer queryis evaluated once for each row processed by
the sub-query.

Is This Answer Correct ?

2 Yes

2 No

Post New Answer