aspose file tools*
The moose likes Oracle/OAS and the fly likes Correlated Subquery using Group By clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Correlated Subquery using Group By clause" Watch "Correlated Subquery using Group By clause" New topic
Author

Correlated Subquery using Group By clause

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 319


I have to write a query that will list all the employees in each department whose salary is less than highest salary of that department.


It works correctly and give me what I was looking for
ID NAME DEPT_ID SALARY
222 CS Professor 1 40000
333 CS Lecturer 1 30000
555 IT Guest 2 30000
666 IT Reader 2 44000
888 EE Lecturer 3 33000

But when I try Group By it does not give me what I was looking for

This is the result I get
ID NAME DEPT_ID SALARY
111 CS HOD 1 50000
222 CS Professor 1 40000
333 CS Lecturer 1 30000
444 IT HOD 2 55000
555 IT Guest 2 30000
666 IT Reader 2 44000
888 EE Lecturer 3 33000


http://www.mahtabalam.net , Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer
Gene Hilpert
Ranch Hand

Joined: May 22, 2002
Posts: 51

Hi
I'm not too sure what you think the group by is going to do in this case.
The subquery is going to return 60000 and pick records less than that, whitch it looks like its doing.
When you removed the where clause you stoped looking at just those records in the subquery that match the outer query so the max of all records in the table is returned(60000).
The first way you had your query was correct.
Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 319


What I am trying to do in the subquery by using group by , is to group all the rows in the table according to dept_id column and find out the max of that deparment.
Is that subquery with group by is correlated subquery. ?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Correlated subquery:
Someone on Wikipedia wrote:A correlated subquery is a subquery (a query nested inside another query) that uses values from outer query. The subquery is evaluated once for each row processed by the outer query.

Your first query is correct.

Your second query technically is a correlated subquery too, but isn't correct. As mentioned above, the subquery is executed once per every row of the outer query. The value of the e.dept_id is therefore constant for each execution of the subquery, and thus returns you just one row containing the maximum over all records. It would be semantically identical to the following query:
If you wanted to use GROUP BY, you would have to join the original table to the result of the GROUP BY and apply proper WHERE condition. It is possible, but it wouldn't be a correlated subquery, and it might perform better or worse than a correlated subquery (depending on a lot of things):(Yeah, I managed to sneak natural join in! )

And thanks for providing the SQL scripts!
Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 319

I thought it will work like , Since it is a correlated subquery it will group all the rows using that row`s dept_id.
Then calculate max(salary) of that department and then check whether the salary of the row is less than the max salary or not.

Still I did not get Why it is not working like that.
As I am grouping by e.dept_id(dept_id is not same for all the rows) in the subquery not a constant expression.
Why not it first group by e.dept_id .

I know that if I group by some constant it will return me maximum of all the entries.

This Gives 70000

But I want to do this

This gives max(salary) of each dept
70000
30000
45000

Why correlated subquery takes dept_id as a constant.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Mahtab Alam wrote:As I am grouping by e.dept_id(dept_id is not same for all the rows) in the subquery not a constant expression.

Not true. Read once more what Wikipedia says about correlated subqueries:
The subquery is evaluated once for each row processed by the outer query.

The dept_id is not constant in the outer query, but the inner query is executed many times, and although it is executed with different values of e.dept_id, in each execution the value of e.dept_id is constant (and obviously can't be otherwise).

You can think of the subquery as a sort of a subroutine, say, a function, which is called repeatedly with different parameters. We could write it in PL/SQL like this:Do you now see how the p_dept_id is always constant during a single execution of this function?

Now, rewriting it correctly into PL/SQL would give us:This is what your original query does and what gives you the results you need.

Note: don't do this in your applications! Embedding PL/SQL into your SQL when not needed hurts performance and scalability. I've done it only to help you understand how correlated subqueries work.
Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 319

OK
I get it now.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Correlated Subquery using Group By clause