[LeetCode] 183. Customers who never order

https://leetcode.com/problems/customers-who-never-order/

 

IN 을 활용하기

# solution 1
SELECT name AS Customers
FROM Customers
WHERE id NOT IN (
    SELECT Distinct customerId
    FROM Orders
)

 

Exists 활용하기

# solution 3
SELECT c.name AS Customers
FROM Customers c
WHERE NOT EXISTS (
    SELECT *
    FROM Orders o
    WHERE c.id = o.customerId
)

 

import pandas as pd

def find_customers(customers: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:
    
    df = customers[~customers['id'].isin(orders['customerId'])]

    return df[['name']].rename(columns={'name':'Customers'})

 

Left Join 활용하기

# solution 2
SELECT c.name AS Customers
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customerId
WHERE o.customerId IS NULL

 

import pandas as pd

def find_customers(customers: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:
    
    df = pd.merge(left=customers, right=orders, left_on="id", right_on="customerId", how="left")

    ans = df[df['customerId'].isna()]

    return ans[['name']].rename(columns={'name':'Customers'})