2021-12-15 14:50 작성

My SQL basic 메모

Table of contents

MySQL DELETE JOIN과 INNER JOIN

MySQL은 DELETE 구문 내에 INNER JOIN 절을 사용해 임의의 table의 rows를 삭제하고 다른 table의 매칭하는 rows를 삭제한다.

예를 들어, 명시적 조건을 충족하는 T1과 T2 tables의 rows를 삭제하기 위해 다음의 구문을 사용한다:

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;

DELETE와 FROM 키워드 사이에 T1과 T2 table names를 집어 넣은 것을 주목하라. 만약 T1 table을 제외하면 DELETE 구문은 오직 T2 table의 rows만을 삭제한다. 반대의 경우도 동일하다.

T1.key = T2.key expression은 T1과 T2 tables 간 매칭하는 rows의 조건을 명시한다.

WHERE 절의 조건은 T1과 T2에서 삭제될 rows를 결정한다.


MySQL DELETE JOIN과 INNER JOIN 예시

다음의 구조와 데이터를 가진 t1과 t2 tables를 가지고 있다고 가정하자:

DROP TABLE IF EXISTS t1, t2;

CREATE TABLE t1 (
    id INT PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE t2 (
    id VARCHAR(20) PRIMARY KEY,
    ref INT NOT NULL
);

INSERT INTO t1 VALUES (1), (2), (3);

INSERT INTO t2(id, ref) VALUES('A',1),('B',2),('C',3);


다음의 구문은 DELETE ... INNER JOIN 구문을 사용해 t1 table의 id가 1이며 t2 table의 ref가 1인 row를 삭제하는 구문이다.

DELETE t1, t2 
FROM t1
    INNER JOIN
    t2 ON t2.ref = t1.id
WHERE
    t1.id = 1;

위의 구문은 다음의 메시지를 출력한다:

Query OK, 2 rows affected (0.01 sec)

예상대로 삭제되었는지 확인하는 구문을 작성한다.

SELECT id 
FROM t1 
UNION 
SELECT ref 
FROM t2;

output: 1이 정상적으로 삭제된 것을 알 수 있다.

id
2
3



MySQL DELETE JOIN과 LEFT JOIN

종종 오른쪽 table과 매칭되지 않는 left table의 rows를 찾기 위해 SELECT 구문 내에 LEFT JOIN 절을 사용한다.

다른 table(오른쪽 table)에서 매칭되는 rows가 없는 왼쪽 table의 rows를 제거하기 위해 DELETE 구문 내에 LEFT JOIN 절을 사용할 수 있다.

다음의 문법은 T2 table에 일치하는 rows가 없는 T1 table의 rows를 DELETE 구문과 LEFT JOIN을 이용해 삭제하는 방법을 시각적으로 보여준다:

DELETE T1
FROM T1
        LEFT JOIN
    T2 ON T1.key = T2.key
WHERE T2.key IS NULL;

상기의 INNER JOIN절에서 T1과 T2 tables를 넣었던 것과 달리 DELETE 키워드 다음에 T1 table만 넣은 것에 주의한다.


MySQL DELETE JOIN과 LEFT JOIN 예시

다음의 customers와 others tables가 있다고 가정하자:


각각의 customer는 0개 혹은 그 이상의 orders를 가진다. 그러나 각각의 order는 오직 한 명의 customer에게 속한다.

customers master 데이터를 깨끗히 없애기 위해 LEFT JOIN 절과 DELETE 구문을 사용할 수 있다. 다음의 구문은 어떤 order도 가지고 있지 않은 customers를 제거한다.

DELETE customers
FROM customers
        LEFT JOIN
    orders ON customers.customerNumber = orders.customerNumber
WHERE
    orderNumber IS NULL;

다음의 쿼리를 사용해 어떤 order도 가지고 있지 않은 customers를 제대로 삭제했는지 확인할 수 있다.

SELECT 
    c.customerNumber, 
    c.customerName, 
    orderNumber
FROM
    customers c
        LEFT JOIN
    orders o ON c.customerNumber = o.customerNumber
WHERE
    orderNumber IS NULL;

쿼리가 empty result set을 반환하면 예상한대로 삭제가 진행되었다는 것이 증명된다.