Sample SQL with self-join - using SQL Server 2005.
Assume an employee table that looks like this:
| empid | dept | salary | mgrid |
| 100 | SALES | 95000 | 200 |
| 200 | SALES | 100000 | 990 |
| 300 | SALES | 105000 | 200 |
| 400 | ACCTG | 110000 | 990 |
| 500 | ACCTG | 115000 | 400 |
| 600 | ACCTG | 120000 | 400 |
| 700 | ADMIN | 125000 | 900 |
| 800 | ADMIN | 130000 | 900 |
| 900 | ADMIN | 127000 | 990 |
| 990 | DAMAN | 98000 | |
Query 1: find how many employees in each department are paid more than their managers. Only count non-managerial employees.
select E.dept, count(M.mgrid) As NumOverPaidEmps
from employees E, employees M
where E.salary > M.salary
and E.mgrid = M.empid
and M.mgrid IS NOT NULL
group by E.dept;
Result:
| dept | NumOverPaidEmps |
| ACCTG | 2 |
| ADMIN | 1 |
| SALES | 1 |
Query 2: find how many employees in each department are paid more than their managers.
Same as above, but include managers this time.
select E.dept, count(*) As NumOverPaidEmps
from employees E, employees M
where E.salary > M.salary
and E.mgrid = M.empid
group by E.dept;
Result:
| dept | NumOverPaidEmps |
| ACCTG | 3 |
| ADMIN | 2 |
| SALES | 2 |
Query 3: Now who are these non-managerial employees we counted in query 1? They are:
select E.dept, E.empid
from employees E, employees M
where E.salary > M.salary
and E.mgrid = M.empid
and M.mgrid IS NOT NULL;
Result:
| dept | empid |
| SALES | 300 |
| ACCTG | 500 |
| ACCTG | 600 |
| ADMIN | 800 |
Query 4: Finally, who are these employees and managers we counted in query 2 above? They
are:
select E.dept, E.empid
from employees E, employees M
where E.salary > M.salary
and E.mgrid = M.empid;
Result:
| dept | empid |
| SALES | 200 |
| SALES | 300 |
| ACCTG | 400 |
| ACCTG | 500 |
| ACCTG | 600 |
| ADMIN | 800 |
| ADMIN | 900 |
Date created: September 25, 2008.