Problem: Given the Employee table, write a SQL query that finds out employees who earn more than their managers.
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
Solution
内连接:自连接
表连接的本质就是笛卡尔积现象,显示所有记录的乘积
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select * from dept a,dept b;
+--------+------------+----------+--------+------------+----------+
| DEPTNO | DNAME | LOC | DEPTNO | DNAME | LOC |
+--------+------------+----------+--------+------------+----------+
| 40 | OPERATIONS | BOSTON | 10 | ACCOUNTING | NEW YORK |
| 30 | SALES | CHICAGO | 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS | 10 | ACCOUNTING | NEW YORK |
| 10 | ACCOUNTING | NEW YORK | 10 | ACCOUNTING | NEW YORK |
| 40 | OPERATIONS | BOSTON | 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO | 20 | RESEARCH | DALLAS |
| 20 | RESEARCH | DALLAS | 20 | RESEARCH | DALLAS |
| 10 | ACCOUNTING | NEW YORK | 20 | RESEARCH | DALLAS |
| 40 | OPERATIONS | BOSTON | 30 | SALES | CHICAGO |
| 30 | SALES | CHICAGO | 30 | SALES | CHICAGO |
| 20 | RESEARCH | DALLAS | 30 | SALES | CHICAGO |
| 10 | ACCOUNTING | NEW YORK | 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON | 40 | OPERATIONS | BOSTON |
| 30 | SALES | CHICAGO | 40 | OPERATIONS | BOSTON |
| 20 | RESEARCH | DALLAS | 40 | OPERATIONS | BOSTON |
| 10 | ACCOUNTING | NEW YORK | 40 | OPERATIONS | BOSTON |
+--------+------------+----------+--------+------------+----------+
16 rows in set (0.00 sec)
mysql> select * from dept a join dept b;
+--------+------------+----------+--------+------------+----------+
| DEPTNO | DNAME | LOC | DEPTNO | DNAME | LOC |
+--------+------------+----------+--------+------------+----------+
| 40 | OPERATIONS | BOSTON | 10 | ACCOUNTING | NEW YORK |
| 30 | SALES | CHICAGO | 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS | 10 | ACCOUNTING | NEW YORK |
| 10 | ACCOUNTING | NEW YORK | 10 | ACCOUNTING | NEW YORK |
| 40 | OPERATIONS | BOSTON | 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO | 20 | RESEARCH | DALLAS |
| 20 | RESEARCH | DALLAS | 20 | RESEARCH | DALLAS |
| 10 | ACCOUNTING | NEW YORK | 20 | RESEARCH | DALLAS |
| 40 | OPERATIONS | BOSTON | 30 | SALES | CHICAGO |
| 30 | SALES | CHICAGO | 30 | SALES | CHICAGO |
| 20 | RESEARCH | DALLAS | 30 | SALES | CHICAGO |
| 10 | ACCOUNTING | NEW YORK | 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON | 40 | OPERATIONS | BOSTON |
| 30 | SALES | CHICAGO | 40 | OPERATIONS | BOSTON |
| 20 | RESEARCH | DALLAS | 40 | OPERATIONS | BOSTON |
| 10 | ACCOUNTING | NEW YORK | 40 | OPERATIONS | BOSTON |
+--------+------------+----------+--------+------------+----------+
16 rows in set (0.00 sec)
mysql> select a.* from dept a,dept b; mysql> select b.* from dept a,dept b;
+--------+------------+----------+ +--------+------------+----------+
| DEPTNO | DNAME | LOC | | DEPTNO | DNAME | LOC |
+--------+------------+----------+ +--------+------------+----------+
| 40 | OPERATIONS | BOSTON | | 10 | ACCOUNTING | NEW YORK |
| 30 | SALES | CHICAGO | | 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS | | 10 | ACCOUNTING | NEW YORK |
| 10 | ACCOUNTING | NEW YORK | | 10 | ACCOUNTING | NEW YORK |
| 40 | OPERATIONS | BOSTON | | 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO | | 20 | RESEARCH | DALLAS |
| 20 | RESEARCH | DALLAS | | 20 | RESEARCH | DALLAS |
| 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS |
| 40 | OPERATIONS | BOSTON | | 30 | SALES | CHICAGO |
| 30 | SALES | CHICAGO | | 30 | SALES | CHICAGO |
| 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO |
| 10 | ACCOUNTING | NEW YORK | | 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON | | 40 | OPERATIONS | BOSTON |
| 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON |
| 20 | RESEARCH | DALLAS | | 40 | OPERATIONS | BOSTON |
| 10 | ACCOUNTING | NEW YORK | | 40 | OPERATIONS | BOSTON |
+--------+------------+----------+ +--------+------------+----------+
16 rows in set (0.00 sec) 16 rows in set (0.00 sec)
mysql> select * from dept a,dept b where a.deptno=b.deptno;
+--------+------------+----------+--------+------------+----------+
| DEPTNO | DNAME | LOC | DEPTNO | DNAME | LOC |
+--------+------------+----------+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK | 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS | 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO | 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON | 40 | OPERATIONS | BOSTON |
+--------+------------+----------+--------+------------+----------+
4 rows in set (0.00 sec)
Approach 1
select * from Employee a,Employee b;
+----+-------+--------+-----------+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId | Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 | 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 | 1 | Joe | 70000 | 3 |
| 3 | Sam | 60000 | NULL | 1 | Joe | 70000 | 3 |
| 4 | Max | 90000 | NULL | 1 | Joe | 70000 | 3 |
+----+-------+--------+-----------+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 | 2 | Henry | 80000 | 4 |
| 2 | Henry | 80000 | 4 | 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL | 2 | Henry | 80000 | 4 |
| 4 | Max | 90000 | NULL | 2 | Henry | 80000 | 4 |
+----+-------+--------+-----------+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 | 3 | Sam | 60000 | NULL |
| 2 | Henry | 80000 | 4 | 3 | Sam | 60000 | NULL |
| 3 | Sam | 60000 | NULL | 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL | 3 | Sam | 60000 | NULL |
+----+-------+--------+-----------+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 | 4 | Max | 90000 | NULL |
| 2 | Henry | 80000 | 4 | 4 | Max | 90000 | NULL |
| 3 | Sam | 60000 | NULL | 4 | Max | 90000 | NULL |
| 4 | Max | 90000 | NULL | 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+----+-------+--------+-----------+
select * from Employee a,Employee b where a.ManagerId=b.Id;
+----+-------+--------+-----------+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId | Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 | 3 | Sam | 60000 | NULL |
| 2 | Henry | 80000 | 4 | 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+----+-------+--------+-----------+
SELECT * FROM Employee a,Employee b WHERE a.ManagerId=b.Id AND a.Salary>b.Salary;
+----+-------+--------+-----------+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId | Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 | 3 | Sam | 60000 | NULL |
+----+-------+--------+-----------+----+-------+--------+-----------+
Approach 2
Employee Leader
+----+-------+--------+-----------+ +----+-------+--------+-----------+
| Id | Name | Salary | ManagerId | | Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+ +----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 | | 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 | | 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL | | 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL | | 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+ +----+-------+--------+-----------+
select a.name Employee from Employee a join Employee b ON a.ManagerId=b.Id AND a.salary>b.salary;
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
+----+-------+--------+-----------+