Interested to Buy Any Domain ? << Click Here >> for more details...
What is the difference between correlated sub queries and
inner queries?
- 4 Answers
- 15661 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 a few of the important components of Teradata?
any one answer me how they can analyzing the project using data ware housing?
what are the uses of fact table and dimension table in banking project?
Is PK concept available in Teradata. If it is how can we create Primary Key for a table in TD
What is differnce between Error code and Error Level in Bteq
we have two tables emp,dept.emp has eno,ename,sal and dept has deptno,dname.how to find maxsal of each dept wise.which join used for joining.
5 Answers Accenture, Cognizant,
What is node? How many nodes and amps used in your previous project?
what is the query for retrieve the unique values(not display the duplicates values) from single table?
what are the day to day activities of teradata DBA ?
Discuss the advantages of using partitioned primary index in a query?
What is meant by MATCHTAG in multiload utility?
What are the various indexes in teradata?
Teradata 
