- Employees Earning More Than Their Managers
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
+—-+——-+——–+———–+
| Id | Name | Salary | ManagerId |
+—-+——-+——–+———–+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+—-+——-+——–+———–+
Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.
+———-+
| Employee |
+———-+
| Joe |
+———-+
Answer1:
使用内连接语句——JOIN
SELECT E.Name Employee
FROM Employee E
INNER JOIN Employee M
ON E.ManagerId=M.Id AND E.Salary>M.Salary;
Answer2:
使用SELECT FROM WHERE语句——WHERE
SELECT E.Name Employee
FROM Employee E ,Employee M
WHERE E.ManagerId=M.Id AND E.Salary>M.Salary;
* SELECT * FROM table_a, table_b 得到的结果是两个表的笛儿卡积,也就是说SELECT多表查询时,默认是隐式链接,本例子中首先得到的是employee自身链接产生的4x4的表,在此基础上进行的两个附件条件的查询。*