Leetcode 181. Employees earning more Than their Managers

Topic Links:

https://leetcode.com/problems/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 Employee The table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe was the only employee of the WHO earns more than his manager.

+----------+| Employee |+----------+| Joe      |+----------+

Subscribe to see which companies asked this question

1 # Write Your MySQL query statement below 2 Select  from 3       4         where E1. Salary>5and              E1. ManagerID=E2. Id

Http://www.w3school.com.cn/sql/sql_join.asp

About SQL Jion

SQL JOIN

• SQL Aliases
• SQL Inner Join

SQL join is used to query data from these tables based on the relationship between the columns in two or more tables.

Join and Key

Sometimes in order to get the complete result, we need to get the results from two or more tables. We need to execute the join.

Tables in the database can be linked by keys. The primary key (Primary key) is a column, and the value of each row in the column is unique. In the table, the value of each primary key is unique. The purpose of this is to cross-bind the data between tables without repeating all the data in each table.

Please see the "Persons" table:

City

id_p	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Note that the "id_p" column is the primary key in the Persons table. This means that no two lines can have the same id_p. Even if the names of two people are exactly the same, id_p can distinguish them from each other.

Next, look at the Orders table:

Id_o	OrderNo	id_p
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

Note that the "id_o" column is the primary key in the Orders table, and the "id_p" column in the Orders table is used to refer to people in the "Persons" table without using their exact names.

Please note that the "id_p" column links the above two tables.

Referencing two tables

We can get the data from two tables by referencing two tables:

Who ordered the products and what products they ordered?

Result set:

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678

SQL join-Using Join

In addition to the above method, we can also use keyword JOIN to get data from two tables.

If we want to list everyone's orders, you can use the following SELECT statement:

SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom Persons on INNER JOIN Orders persons.id_p = Orders.id_porder by Persons.lastname

Result set:

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678

Different SQL JOIN

In addition to the INNER join (inner join) we used in the example above, we can also use several other connections.

The following lists the JOIN types that you can use, and the differences between them.

• JOIN: Returns a row if there is at least one match in the table
• Left JOIN: Returns all rows from the table, even if there is no match in the right table
• Right JOIN: Returns all rows from the correct table even if there is no match in the left table
• Full JOIN: Returns a row if there is a match in one of the tables

Leetcode 181. Employees earning more Than their Managers