What is the query to find the salary of employees having more salary than his/her manager?

Question

Suppose there is a below table

EmpID	EmpName	ManagerID	EmpSalary
1	ABC	2	1000
2	DEF	3	2000
10	GHI	11	5000
3	JKL	4	1000

Now we know that EmpID = 2 should be the output because it has higher salary than its manager.

But what query can we write to get this output? I am really confused here

Answer 1

WITH CTE(EmpID,     EmpName,    ManagerID,  EmpSalary) AS
 (

    SELECT 1,   'ABC' , 2 , 1000 UNION ALL
    SELECT 2 ,  'DEF' , 3 , 2000  UNION ALL
    SELECT 10 , 'GHI'   ,11 ,   5000  UNION ALL
    SELECT 3 ,  'JKL' , 4 , 1000
 )
 SELECT T.EmpID,T.EmpName,T.ManagerID,T.EmpSalary,T2.EmpSalary AS MANAGER_SALARY
 FROM CTE AS T
 JOIN CTE AS T2 ON T.ManagerID=T2.EmpID
 --WHERE T.EmpSalary>T2.EmpSalary

This is called "self-join". CTE in the above code represents your table's data. Please take a look how table is joined with itself (I commented the WHERE-condition) to show employee's an manager'ssalaries