OraFAQ Forum: SQL & PL/SQL » order of operations in the below statements

Home » SQL & PL/SQL » SQL & PL/SQL » order of operations in the below statements (sql developer)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

order of operations in the below statements [message #659900]

Thu, 02 February 2017 12:41

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 ?

Report message to a moderator

Re: order of operations in the below statements [message #659902 is a reply to message #659900]

Thu, 02 February 2017 13:27

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What happens when you remove WHERE clause from the first query?

Report message to a moderator

Re: order of operations in the below statements [message #659903 is a reply to message #659900]

Thu, 02 February 2017 13:31

joy_division
Messages: 4963
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.

Report message to a moderator

Re: order of operations in the below statements [message #659905 is a reply to message #659903]

Fri, 03 February 2017 03:25

cookiemonster
Messages: 13920
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.

Report message to a moderator

Previous Topic:	constraint error
Next Topic:	Performance tuning in delete operation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 01:35:30 CDT 2024