• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Correlated Subquery using Group By clause

 
Ranch Hand
Posts: 391
1
MySQL Database PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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

 
Ranch Hand
Posts: 51
Hibernate Eclipse IDE Oracle
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 391
1
MySQL Database PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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. ?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 391
1
MySQL Database PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 391
1
MySQL Database PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK
I get it now.
 
All of the following truths are shameless lies. But what about this tiny ad:
New web page for Paul's Rocket Mass Heaters movies
https://coderanch.com/t/785239/web-page-Paul-Rocket-Mass
reply
    Bookmark Topic Watch Topic
  • New Topic