| View previous topic :: View next topic |
| Author |
Message |
WHITE
Beginner
Joined: 03 Sep 2004
Posts: 8
Topics: 4
|
 Posted: Wed Jan 26, 2005 7:23 pm Post subject: Co-related subquery |
 |
|
Unable to understand the process of co-related subquery exection.
I knew therotically how it gets executed.
----------------------------------------------------------------------
In a correlated subquery -- for each row in the outer query the inner query will be executed once since the inner query is referencing a value returned by the outer query.
-----------------------------------------------------------------------
ex--
Select e1.salary
from EMP e1
where 1 = ( select salary
from EMP e2
where e2.salary > e1.salary)
The above is the example shown in one of the forum in context of corelated subquery
But I don't understand where is the absolute "where" predicate to execute the outer query which gives input to the inner query.
It looks the outer query of where still depend on the inner query. but again the inner query is also not has any where predicate agianst a constant to get some result.
This is little confusing. could some one explain me with some good examples ? |
|
| Back to top |
|
 |
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Thu Jan 27, 2005 6:32 am Post subject: |
|
|
If you don't issue any additional predicates in the where clause of the outer query, then simply ALL rows of the outer table are considered as qualifying and the subselect is triggered for EVERY row of the outer table.
regards
Christian |
|
| Back to top |
|
|
WHITE
Beginner
Joined: 03 Sep 2004
Posts: 8
Topics: 4
|
| Posted: Thu Jan 27, 2005 2:53 pm Post subject: |
|
|
| Thanks CZerfas. Okay, now what I understood from your explanation is that, the inner query gets evaluated foe every row of the outer query/outer table as there is no predicate. but what is the significance of Where clause on the outer query in which the result of the iiner query is getting matched with 1. |
|
| Back to top |
|
|
WHITE
Beginner
Joined: 03 Sep 2004
Posts: 8
Topics: 4
|
| Posted: Fri Jan 28, 2005 1:05 am Post subject: |
|
|
Okay, this is the query:
------------------------
Select e1.salary
from EMP e1
where 1 = ( select salary
from EMP e2
where e2.salary > e1.salary)
---------------------------------------
Further study on corelated subqueries revealed the following:
The inner query gets each row of the outer tables (though it is the same physical table) salary value.
Example
Table EMP:
| Code: |
Emp-no salary <== These are columns
-------- ------
100 1
101 2
102 1
103 4
105 6
106 7
107 8
--------------------------------------------------
for the purpose of query DB2 will make two copies of the same table giving different names as E1 and E2.
(Ist row of the inner query for the first
qualified outer query (In this example all the rows are
qualified as there is no other predicate with the where
clause))
select salary from EMP e2
where 1> 1 <= Not matching and so no selection here
(For the second row of the inner table with the first
row of the outer table )
Select Salary
from Emp2
where 2 > 1 <== Matching, selected from the inner query.
This way it continues for 7 times with the one row of the first outer query.
Then comes with the second iteration, I mean second row of the first outer query
and agian the inner query is evaluated again for 7 times.
So totally 49 times the match evaluation happens to bring the final result.
The resuts of the inner query for each evaluation would be like this.
First evaluation output of salary - 2,4,6,7,8
Second evalutaion : 4,6,7,8
Third - - 2,4,6,7,8
Fourth one 6,7,8
Fifth one 7,8
6th one 8
7th one 0
But none of the above results are equal to 1 of the WHERE clause on the outer clause, hence the end result is zero. No rows are obtained.
Had the where clause is less than 1 , then the output would be all of above
2
4
6
7
8
4
6
7
8
2
4
6
7
8
6
7
8
7
8
|
---------------------------------------------------------------------
Could some one let me know if my understanding is wrong here ? |
|
| Back to top |
|
|
arshadh
Beginner
Joined: 10 Jan 2007
Posts: 33
Topics: 12
|
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Wed Sep 01, 2010 7:58 am Post subject: |
|
|
Maybe if you think of a non-correlated subquery, you'll have a better understanding of the correlated subquery. For a non-correlated subquery, there is no reference to the outer table. In your case, you might see something like this:
| Code: | Select e1.salary
from EMP e1
where 1 > ( select avg(e2.salary)
from EMP e2) |
DB2 will access the inner table once to evaluate the non-correlated subquery. The correlated subquery would be the opposite situation, where DB2 accesses the outer table and inner table repeatedly. |
|
| Back to top |
|
|
Dibakar
Advanced
Joined: 02 Dec 2002
Posts: 702
Topics: 64
Location: USA
|
| Posted: Wed Sep 01, 2010 5:41 pm Post subject: |
|
|
| Quote: | Okay, this is the query:
------------------------
Select e1.salary
from EMP e1
where 1 = ( select salary
from EMP e2
where e2.salary > e1.salary)
--------------------------------------- |
| Quote: | Example
Table EMP:
| Code: | Emp-no salary <== These are columns
-------- ------
100 1
101 2
102 1
103 4
105 6
106 7
107 8 |
|
WHITE,
Your analysis about the result of inner query is correct but the example you provided is not valid. Inner query must return single row otherwise the inner WHERE would fail giving SQLCODE -811.
I am assuming that the query you got from other forum is not correct, probably the poster had following in mind -
| Code: | Select e1.salary
from EMP e1
where 1 = ( select count(salary)
from EMP e2
where e2.salary > e1.salary) |
This would return max salary if there are no duplicates
_________________
Regards,
Diba |
|
| Back to top |
|
|
Nic Clouston
Advanced
Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home
|
| Posted: Thu Sep 02, 2010 2:20 am Post subject: |
|
|
y'all realize this thread is 5 years old apart from these last 3 posts?!
_________________
Utility and Program control cards are NOT, repeat NOT, JCL. |
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Fri Sep 03, 2010 7:34 am Post subject: |
|
|
 Now I do |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
