[MySQL] NOT IN

테이블

Customers 테이블

id name
1 Joe
2 Henry
3 Sam
4 Max

Orders

id customerId
1 3
2 1

문제

  • 아직 주문을 하지 않은 고객만 뽑으시오

답

SELECT c.name AS Customers 
FROM Customers c 
WHERE c.id NOT IN (
    SELECT o.customerId 
    FROM Orders o 
);

스키마

Create table If Not Exists Customers (id int, name varchar(255));
Create table If Not Exists Orders (id int, customerId int);
Truncate table Customers;
insert into Customers (id, name) values (1, 'Joe');
insert into Customers (id, name) values (2, 'Henry');
insert into Customers (id, name) values (3, 'Sam');
insert into Customers (id, name) values (4, 'Max');
Truncate table Orders;
insert into Orders (id, customerId) values (1, 3);
insert into Orders (id, customerId) values (2, 1);

links

social