SQL: Give the names of employees who earn more than their manager

EMPNO   ENAME   JOB         MGR     HIREDATE    SAL     COMM    DEPTNO

7369    SMITH   CLERK       7902    2000-12-17  800     Null        20

7902    FORD    ANALYST     7566    1992-12-03  3000    Null        20

Hi guys, I have this type of table, and I need to show the employees who earn more than there managers? It would be easier if we had 2 tables, but I cannot imagine a code within one table. Any ideas?

Answer

• You can do “self-join” between the two tables. In the second table, EMPNO will be equal to the corresponding MGR value.
• Use Where to filter out cases where salary of employee is higher than that of manager.

Try:

SELECT employee.*
FROM your_table_name AS employee
JOIN your_table_name AS manager ON manager.EMPNO = employee.MGR 
WHERE employee.SAL > manager.SAL 

---

As @Strawberry suggested in comments that column comparisons can be done in the join condition itself (instead of using Where). You can do the following as well:

SELECT employee.*
FROM your_table_name AS employee
JOIN your_table_name AS manager ON manager.EMPNO = employee.MGR AND 
                                   employee.SAL > manager.SAL 

The advantage of this approach is that we have to switch from Inner Join to Left Join, changes required to the query would be lesser.