SQL displaying staff with more salary than their managers

I’m trying to display staff from the same department who earn more than their managers.

SELECT ID, NAME, DEPARTMENT, SALARY, JOB
FROM STAFF
WHERE SALARY > ANY (SELECT SALARY FROM STAFF WHERE JOB = 'Manager')

This doesn’t seem to work, and I’m reallly not sure why.

Here’s a peep at how the tables are formatted:

ID | NAME | DEPARTMENT | SALARY | JOB

20 | JOHN | 180        | 52000  | Manager
30 | KATY | 180        | 60000  | Analyst

Answer

The problem is that you need to correlate the subquery to match the same departement:

SELECT s1.ID, s1.NAME, s1.DEPARTMENT, s1.SALARY, s1.JOB
FROM SALARY s1
WHERE
    s1.JOB <> 'MANAGER' AND
    s1.SALARY > (SELECT s2.SALARY FROM SALARY s2
                 WHERE s2.DEPARTMENT = s1.DEPARTMENT AND s2.JOB = 'MANAGER');

This answer assumes that each department would have only one manager. If there could be more than one manager, then it would be safer to write the above using exists logic:

SELECT s1.ID, s1.NAME, s1.DEPARTMENT, s1.SALARY, s1.JOB
FROM SALARY s1
WHERE
    s1.JOB <> 'MANAGER' AND
    NOT EXISTS (SELECT 1 FROM SALARY s2
                 WHERE s2.DEPARTMENT = s1.DEPARTMENT AND
                       s2.JOB = 'MANAGER' AND
                       s2.SALARY >= s1.SALARY);