🎈 Write in front
🙋♂️ Hello everyone, I'm super dream. We all know that we have to deal with the database almost every day, whether in study or daily work. In order to better operate the database, our SQL knowledge reserve is essential. If you want to master SQL well, you must practice and study every day. Next, Xiaomeng will lead the partners to brush a topic related to LeetCode database (SQL) every day, and then cite relevant knowledge points at the end of the article to help the partners learn and consolidate and better master SQL.
🙋♂️ If you don't understand anything in the learning process, you are welcome to leave a message and ask questions in the comment area. Xiaomeng will tell you everything.
catalogue
🍕 Topic overview
Employee table
+----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+
Title: the Employee table contains all employees, and their managers also belong to employees. Each Employee has an Id and a list of the Id of the manager corresponding to the Employee. Given the Employee table, write an SQL query that gets the names of employees whose income exceeds their managers. In the above table, Joe is the only Employee whose income exceeds his manager. The query results are as follows.
+----------+ | Employee | +----------+ | Joe | +----------+
👇LeetCode original title address~
🍕 Problem solving ideas
First, according to the meaning of the question, the Employee table contains all employees, and their managers also belong to employees. Each Employee has an Id and a list of the Id of the manager corresponding to the Employee. Then we can see that the field ManagerId is the manager Id of the corresponding Employee. According to the Employee table, the manager Id corresponding to joe is 3 and the manager Id corresponding to Henry is 4. Managers also belong to employees. id3 corresponds to Sam, Id4 corresponds to Max, that is, joe corresponds to Sam and Henry corresponds to Max.
Because the table contains both Employee information and manager information, we need to obtain the information twice. We can regard Employee as both member work table and manager table. The title requires us to find out if the Employee's Salary is greater than the manager's Salary. Then we can connect the id field with the ManagerId field and add a condition that the Employee Salary is greater than the manager Salary. The specific methods are as follows.
🍟 Method 1
There are two kinds of information in the table: employees and managers. We need to obtain the information twice, and then use the Where statement to do conditional screening to find out the data that employees' wages are greater than managers.
Solution 1:
SELECT staff.Name AS 'Employee' FROM
Employee AS staff,
Employee AS manager
WHERE
staff.ManagerId = manager.Id
AND staff.Salary > manager.Salary🍟 Method 2
There are two kinds of information in the table: employee and manager. We need to obtain the information twice. In addition to using the where statement, we can also use the inner connection to screen the conditions through the on statement to find out the data that the employee's salary is greater than the manager.
Problem solution 2:
SELECT staff.NAME AS 'Employee' FROM Employee AS staff
JOIN
Employee AS manager
ON
staff .ManagerId = manager.Id
AND staff .Salary > manager.Salary
🍕 code implementation
🍟 Method 1
SELECT staff.Name AS 'Employee' FROM
Employee AS staff,
Employee AS manager
WHERE
staff.ManagerId = manager.Id
AND staff.Salary > manager.Salary
Consistent with the predicted results, success!
🍟 Method 2
SELECT staff.NAME AS 'Employee' FROM Employee AS staff
JOIN
Employee AS manager
ON
staff .ManagerId = manager.Id
AND staff .Salary > manager.Salary
Consistent with the predicted results, success!
🍕 Summary of knowledge points
Inner connection and outer connection
Xiaomeng takes her friends through the inner connection and outer connection in a very simple way.
Table 1 classa
Table 2 classb
1. Inner join (join is inner join by default)
The intersection of Table 1 and table 2 is shown by the above two performances
select
classa.id as aid,classb.id as bid
from
classa
inner join classb on classa.id = classb.id;The result of the query is the intersection of classa and classb
2. Left outer join
The result set retains all rows of the left table, but the right table contains only rows that match the left table. The corresponding NULL behavior of the right table is NULL.
select
classa.id as aid,classb.id as bid
from
classa
left join classb on classa.id = classb.id;
3. right join
The result set retains all rows of the right table, but the left table contains only rows that match the right table. The corresponding NULL behavior of the left table is NULL.
select
classa.id as aid,classb.id as bid
from
classa
right join classb on classa.id = classb.id;
4. full join
All rows of the two tables are displayed in the result table.
select
classa.id as aid,classb.id as bid
from
classa
full join classb on classa.id = classb.id;Attention, guys!!!
MySQL does not support full join!!! MySQL does not support full join!!! MySQL does not support full join!!!
Say important things three times!!! How to achieve the same effect as full join? It should be realized by using union. The specific SQL statements are as follows
select
classa.id as aid,classb.id as bid
from
classa left join classb on classa.id = classb.id
union
select
classa.id as aid,classb.id as bid
from
classa right join classb on classa.id = classb.id;
😀 Thank you for your support. If you have any questions, please leave a message. Xiaomeng will know everything and say everything!