Home » SQL & PL/SQL » SQL & PL/SQL » order of operations in the below statements (sql developer)
order of operations in the below statements [message #659900] Thu, 02 February 2017 12:41 Go to next message
M123
Messages: 47
Registered: February 2016
Location: USA
Member

hi

need help on the order of operations.

/*select which employees salary is higher than the avg salary*/

1)

select a.employee_id,salary from employees a where a.salary >(select avg(b.salary) from employees b 
where a.department_id = b.department_id) order by a.SALARY;

the above code results total 38 records with salary 3600,3800 also which is less than average.



2)

select   e1.employee_id ,e1.salary   from     employees e1 ,    employees e2    group by e1.employee_id  ,  e1.salary
   having   e1.salary > avg(e2.salary);

this code results 58 records and this is correct. the average salary is 6400
Why the first one is retrieving wrong values less than average ?


Re: order of operations in the below statements [message #659902 is a reply to message #659900] Thu, 02 February 2017 13:27 Go to previous messageGo to next message
Littlefoot
Messages: 21333
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens when you remove WHERE clause from the first query?
Re: order of operations in the below statements [message #659903 is a reply to message #659900] Thu, 02 February 2017 13:31 Go to previous messageGo to next message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
You are joining by dept in first one and you have a Cartesian product in the second one.

Your question of select which employees salary is higher than the avg salary does not have enough information.
Re: order of operations in the below statements [message #659905 is a reply to message #659903] Fri, 03 February 2017 03:25 Go to previous message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
The first is giving employees who's salary is greater than the average for their department.
It seems you want employees who's salary is greater than the average across everyone.
If so the second one does that, but probably not very efficiently.
Remove the where clause of the sub-query in the first as LF suggested.
Previous Topic: constraint error
Next Topic: Performance tuning in delete operation
Goto Forum:
  


Current Time: Tue Jan 23 05:04:57 CST 2018

Total time taken to generate the page: 0.01356 seconds