What is the difference between correlated sub queries and
inner queries?
- 4 Answers
- 14451 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
Highlight the limitations of TPUMP Utility.
How do you create a table with an existing structure of another table with data and with no data?
What are the primary characteristics of the Teradata.
Explain how spool space is used.
what is single value and Multivalue ?
what is use of PI(primary index) AND SI(secondary index)
Mention a few of the ETL tools that come under Teradata.
What are default access rights in teradata?
Fast Load Script is failed and error tables are available then how do you restart?
What is the command in bteq to check for session settings ?
Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?
What are the available join types in teradata?
Teradata 
