CODEWITHSUNDEEP
sqlsql-servert-sql
b_dobee
b_dobee

Reputation: 29

correlated subquery on same table

here is table schema emp(no, name, salary)

and insert data like this.

insert into emp values (1, 'gandalf', 3000);
insert into emp values (2, 'dobby', 4000);  
insert into emp values (3, 'legolas', 5000);

and I select data, like this.

select no, name, salary
from emp e
where salary > (
    select AVG(salary)
    from emp
    where no=e.no
);

but result is empty!!! I don't understand...

I expected this (3, 'legolas', 5000)

I try this query, It worked.

select no, name, salary
from emp
where salary > (
    select AVG(salary)
    from emp d
    where no=d.no
);

So, Correlated Subquery must have to alias variable on same table?

At the same time, superquery must have not to alias variable?

and I don't understand this, too.

select no, name, salary
from emp s
where salary > (
    select AVG(salary)
    from emp d
    where s.no=d.no
);

the result is empty, too..

why!!!????

Upvotes: 1

Views: 2487

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 176284

Do not use correlation. At your example you are basically comparing employees salary with average salary (of the same person where s.no=d.no).

For instance for employee no = 1 you got:

WHERE 3000 > (3000) -- false no record returned

You probably want to pick employees which salary is higher than average of all employees. In that case use:

select no, name, salary
from emp
where salary > (
    select AVG(salary)
    from emp d
);

EDIT:

Scenario when to use correlation (deparment_id column added):

SELECT no, name, salary, deparment_id
FROM emp e1
WHERE salary >= (SELECT AVG(salary)
                FROM emp e2
                WHERE e1.department_id = e2.department_id);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271161

Assuming that emp.no uniquely identifies each row, then the average in the correlated subquery is the salary for that employee. An employee's salary can never be greater than his/her salary. Presumably you intend:

select no, name, salary
from emp e
where salary > (select AVG(e2.salary)from emp e2);

You can also write this using window functions, though:

select no, name, salary
from (select e.*, avg(salary) over () as avg_salary
      from emp e
     ) e
where salary > avg_salary;

Upvotes: 1

Related Questions