9/22 每日一題(MYSQL找出兩個表格沒有重複出現的id)

Table: Employees

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
+-------------+---------+
employee_id is the column with unique values for this table.
Each row of this table indicates the name of the employee whose ID is employee_id.

 

Table: Salaries

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| salary      | int     |
+-------------+---------+
employee_id is the column with unique values for this table.
Each row of this table indicates the salary of the employee whose ID is employee_id.

 

Write a solution to report the IDs of all the employees with missing information. The information of an employee is missing if:

• The employee's name is missing, or
• The employee's salary is missing.

Return the result table ordered by employee_id in ascending order.

The result format is in the following example.

 

Example 1:

Input: 
Employees table:
+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+
Output: 
+-------------+
| employee_id |
+-------------+
| 1           |
| 2           |
+-------------+
Explanation: 
Employees 1, 2, 4, and 5 are working at this company.
The name of employee 1 is missing. 

The salary of employee 2 is missing. 

# Write your MySQL query statement below

select e.employee_id from Employees e

where e.employee_id not in (select employee_id from Salaries )

union

select s.employee_id from Salaries s

where s.employee_id not in (select employee_id from Employees )

order by employee_id

----------------------------

import pandas as pd

def find_employees(employees: pd.DataFrame, salaries: pd.DataFrame) -> pd.DataFrame:

    missing_e=[]

    missing_s=[]

    for i,row in employees.iterrows():

        missing_e.append(row['employee_id'])

    for i,row in salaries.iterrows():

        missing_s.append(row['employee_id'])

    res=[]

    for i,j in zip(missing_e,missing_s):

        if i not in missing_s:

            res.append(i)

        if j not in missing_e:

            res.append(j)

    res.sort()

    return pd.DataFrame({'employee_id':res})

--------------------參考

import pandas as pd

def find_employees(employees: pd.DataFrame, salaries: pd.DataFrame) -> pd.DataFrame:
    merged = employees.merge(salaries, how = 'outer', on = 'employee_id')
    return merged[merged.isna().any(axis = 1)][['employee_id']].sort_values(by = 'employee_id')

----------------------------------

import pandas as pd

def find_employees(employees: pd.DataFrame, salaries: pd.DataFrame) -> pd.DataFrame:
  df=pd.merge(employees, salaries, how="outer", on="employee_id")
  return df[df["name"].isna() | df["salary"].isna()][["employee_id"]].sort_values(by="employee_id")