[MySQL] Three-valued logic: exists / not exists / in / not in

Exists

서브 쿼리에 행이 존재하는 지 반환

 

In 

값이 리스트 안에 존재하면 True로 반환

 

예제

Q. 1월에 구매한 이력이 있는 사람 중 2월에 구매한 이력이 없는 고객의 목록 (Null은 비로그인 구매 고객)

-- create
CREATE TABLE PRODUCT_SOLD_HISTORY (
  soldId INTEGER PRIMARY KEY,
  productId INTEGER,
  amount INTEGER,
  customerName TEXT,
  createDate DATE
);

-- insert
INSERT INTO PRODUCT_SOLD_HISTORY VALUES (1, 1, 2, 'Mark', '2021-01-01');
INSERT INTO PRODUCT_SOLD_HISTORY VALUES (2, 2, 12, 'Jay', '2021-01-02');
INSERT INTO PRODUCT_SOLD_HISTORY VALUES (3, 2, 12, 'John', '2021-01-02');
INSERT INTO PRODUCT_SOLD_HISTORY VALUES (4, 2, 1,  Null, '2021-01-03');
INSERT INTO PRODUCT_SOLD_HISTORY VALUES (5, 2, 28, 'Mark', '2021-02-01');
INSERT INTO PRODUCT_SOLD_HISTORY VALUES (6, 1, 3, 'Mark', '2021-02-03');
INSERT INTO PRODUCT_SOLD_HISTORY VALUES (7, 1, 3, Null, '2021-02-04');

soldId	productId	amount	customerName	createDate
1	1	2	Mark	2021-01-01
2	2	12	Jay	2021-01-02
3	2	12	John	2021-01-02
4	2	1	NULL	2021-01-03
5	2	28	Mark	2021-02-01
6	1	3	Mark	2021-02-03
7	1	3	NULL	2021-02-03

 

 

Not In을 쓰면 될 것 같지만 아래와 같이 실행하면 의외로 아무런 결과가 나오지 않습니다

SELECT
 DISTINCT customerName
FROM
 PRODUCT_SOLD_HISTORY
WHERE 1=1
 AND createDate >= '2021-01-01' AND createDate < '2021-02-01 '
 AND customerName not in (
  SELECT 
   customerName
  FROM
   PRODUCT_SOLD_HISTORY
  WHERE 1=1
   AND createDate >= '2021-02-01' AND createDate < '2021-03-01 '
 )

왜냐하면 

SELECT 
 customerName
FROM
 PRODUCT_SOLD_HISTORY
WHERE 1=1
 AND createDate >= '2021-02-01' AND createDate < '2021-03-01 '

의 결과가

(Mark, Mark, NULL)

입니다

 

MySQL은 Three-valued logic 을 사용합니다.

Three-valued logic 를 사용합니다.

논리적 표현이 True / False 또는 unknown 이 될 수 있습니다

 

NULL과의 연산은 무조건 unknown 이 되고,

unknown이 있으면 값을 반환하지 않습니다

Mark not in (Mark, Mark, NULL) -- False
Jay not in (Mark, Mark, NULL) -- unknown
John not in (Mark, Mark, NULL) -- unknown
NULL not in (Mark, Mark, NULL) -- unknown

따라서 아무런 값을 주지 않습니다

 

비슷한 예로,

1 + Null > 0 -- unknown
1*Null = 0 -- unknown

이 있습니다

 

단 constaint check에 있는 NULL은 true와 unknown을 함께 받을 수 있습니다.

CREATE TABLE t (
    a NUMERIC CHECK (a >= 0),
    b NUMERIC CHECK (b >= 0),
    CHECK ( a + b <= 10 )
)

a와 b에 NULL이 들어갈 수 있습니다

 

예제로 돌아와서,

문제를 해결하려면 NULL과의 비교를 없애주면 됩니다.

SELECT
 DISTINCT customerName
FROM
 PRODUCT_SOLD_HISTORY A
WHERE 1=1
 AND createDate >= '2021-01-01' AND createDate < '2021-02-01 '
 AND NOT EXISTS (
  SELECT 
   1
  FROM
   PRODUCT_SOLD_HISTORY B
  WHERE 1=1
   AND createDate >= '2021-02-01' AND createDate < '2021-03-01 '
   AND A.customerName = B.customerName 
 )

또는

SELECT
 DISTINCT customerName
FROM
 PRODUCT_SOLD_HISTORY
WHERE 1=1
 AND createDate >= '2021-01-01' AND createDate < '2021-02-01 '
 AND customerName not in (
  SELECT 
   customerName
  FROM
   PRODUCT_SOLD_HISTORY
  WHERE 1=1
   AND createDate >= '2021-02-01' AND createDate < '2021-03-01 '
   AND customerName IS NOT NULL
 )

첫번째 쿼리는 NULL 을 같이 반환한다

따라서, 어떠한 방법이든 명시적으로 NULL을 처리해주는 것이 좋다.