[SQL] 부정 논리 연산자(not in 등) 과 Null의 관계

실무에서 SQL쿼리를 사용하다 보면 Where 조건에 in과 not in 조건을 많이 사용할 텐데, not in(부정 논리 연산자)을 사용할때 주의하여야 한다. 그 이유는 NULL 때문이다.

부정연산자

출처 : SQLD가이드

부정 논리 연산자의 경우 위 표처럼 같지 않은 경우를 필터링할 때 사용하는 연산자이다.

주로 not in이나 '<>', '!=' 등을 많이 사용한다.

 

부정 연산자와 Null의 관계

결론부터 말하자면 부정 연산자를 사용하면 Null값이 모두 누락되게 된다.

예시를 확인해보자 (샘플데이터 쿼리는 포스팅 최하단에 있음)

샘플데이터

SELECT  * 
  FROM  orders
 WHERE  1=1
        AND product_id not in (101,102)

원래 의도는 product_id가 101,102인 것만 제외하고 조회하고 싶었으나 product_id가 null인 것도 제외되고 조회되었다.

그 이유는 Null의 특징 때문이다. Null이란 아무런 값을 갖지 않는 것을 의미하는데, 아무런 값을 가지고 있지 않기 때문에 다른 값과 비교를 할 수 없기 때문이다. 즉, null은 Ttue나 False가 아닌 unknown이기 때문에, 비교할 수 없어 조회할 때 전부 누락되는 것이다.

 

부정 연산자를 사용할때 Null을 포함하고 싶을 경우

그럼에도 불구하고 부정연산자를 사용할 때 Null을 포함하여 조회하고 싶은 경우가 있다. Null을 같이 조회하기 위한 방법은 여러가지가 있다.

• Where절에 null을 처리하는 조건을 따로 넣어준다

SELECT * 
  FROM orders
 WHERE 1=1
   AND (product_id IS NULL OR product_id NOT IN (101, 102));

• coalesce나 ifnull을 활용하여 null값을 다른 값으로 대치한 다음 부정연산자를 사용한다

SELECT  * 
  FROM  orders
 WHERE  1=1
        AND coalesce(product_id, 0) not in (101,102) --null을 0으로 치환한 다음 조건 수행
-- ifnull(product_id, 0) not in (101,102) ##MYSQL

 

• True가 아닌 False와 unknown(null)값을 가져온다.

SELECT  * 
  FROM  orders
 WHERE  1=1
	AND product_id in (101,102) is not TRUE

3가지 Case 모두 부정연산자를 사용해서 null값도 조회할 수 있다.

 

샘플데이터

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    shipping_date DATE,
    product_id INT,
    quantity INT,
    price NUMERIC(10, 2),
    discount NUMERIC(5, 2),
    shipping_address TEXT
);

-- 샘플 데이터 삽입
INSERT INTO orders (customer_id, order_date, shipping_date, product_id, quantity, price, discount, shipping_address) VALUES
(1, '2023-07-01', '2023-07-03', 101, 2, 19.99, NULL, '123 Main St, Springfield'),
(NULL, '2023-07-02', NULL, 102, 1, 49.99, 5.00, '456 Elm St, Shelbyville'),
(2, NULL, '2023-07-05', 103, 3, 9.99, NULL, '789 Oak St, Capital City'),
(3, '2023-07-04', '2023-07-06', NULL, 1, 29.99, 10.00, '101 Maple St, Springfield'),
(4, '2023-07-05', '2023-07-08', 104, NULL, 39.99, 15.00, NULL),
(5, '2023-07-06', '2023-07-10', 105, 2, 40, 0.00, '202 Birch St, Shelbyville'),
(6, '2023-07-07', NULL, 106, 5, 24.99, NULL, '303 Pine St, Capital City'),
(NULL, '2023-07-08', '2023-07-11', 107, 1, 99.99, 20.00, '404 Cedar St, Springfield');

-- 테이블 확인
SELECT * FROM orders;

샘플데이터