[sql] NOT IN 절 내의 NULL 값

NOT IN (subquery)null을 올바르게 처리하지 않으므로 여기에 대한 답변에서 결론을 내릴 수 있습니다 NOT EXISTS. 그러나 그러한 결론은 시기상조 일 수 있습니다. Chris Date (데이터베이스 프로그래밍 및 디자인, Vol 2 No 9, 1989 년 9 월)로 인정되는 다음 시나리오 NOT IN에서는 null을 올바르게 처리하고 대신 올바른 결과를 반환합니다 NOT EXISTS.

부품 ( )을 수량 ( ) 으로 공급하는 것으로 알려진 sp공급 업체 ( sno) 를 나타내는 표 를 고려하십시오 . 이 테이블에는 현재 다음 값이 있습니다.pnoqty

      VALUES ('S1', 'P1', NULL),
             ('S2', 'P1', 200),
             ('S3', 'P1', 1000)

수량은 무효화됩니다. 즉, 공급 업체가 수량을 모르더라도 부품을 공급한다고 알려진 사실을 기록 할 수 있습니다.

이 작업은 알려진 공급 부품 번호 ‘P1’이지만 1000 수량이 아닌 공급 업체를 찾는 것입니다.

다음은 NOT IN공급 업체 ‘S2’만 올바르게 식별하는 데 사용 됩니다.

WITH sp AS
     ( SELECT *
         FROM ( VALUES ( 'S1', 'P1', NULL ),
                       ( 'S2', 'P1', 200 ),
                       ( 'S3', 'P1', 1000 ) )
              AS T ( sno, pno, qty )
     )
SELECT DISTINCT spx.sno
  FROM sp spx
 WHERE spx.pno = 'P1'
       AND 1000 NOT IN (
                        SELECT spy.qty
                          FROM sp spy
                         WHERE spy.sno = spx.sno
                               AND spy.pno = 'P1'
                       );

그러나 아래 쿼리는 동일한 일반 구조를 사용하지만 NOT EXISTS결과에 공급자 ‘S1’을 포함하지만 잘못 포함합니다 (예 : 수량이 null 인 경우).

WITH sp AS
     ( SELECT *
         FROM ( VALUES ( 'S1', 'P1', NULL ),
                       ( 'S2', 'P1', 200 ),
                       ( 'S3', 'P1', 1000 ) )
              AS T ( sno, pno, qty )
     )
SELECT DISTINCT spx.sno
  FROM sp spx
 WHERE spx.pno = 'P1'
       AND NOT EXISTS (
                       SELECT *
                         FROM sp spy
                        WHERE spy.sno = spx.sno
                              AND spy.pno = 'P1'
                              AND spy.qty = 1000
                      );

그래서 NOT EXISTS그것이 나타날 수도있는 은색 총알이 아닙니다!

물론 문제의 원인은 널이 존재하므로 ‘실제’해결책은 이러한 널을 제거하는 것입니다.

이것은 두 가지 테이블을 사용하여 (다른 가능한 디자인 중에서도) 달성 할 수 있습니다.

• sp 부품 공급으로 알려진 공급 업체
• spq 알려진 수량으로 부품을 공급하는 것으로 알려진 공급 업체

spq참조 할 경우 외래 키 제약 조건이 있어야합니다 sp.

그런 다음 ‘빼기’관계 연산자 ( EXCEPT표준 SQL 에서 키워드)를 사용하여 결과를 얻을 수 있습니다.

WITH sp AS
     ( SELECT *
         FROM ( VALUES ( 'S1', 'P1' ),
                       ( 'S2', 'P1' ),
                       ( 'S3', 'P1' ) )
              AS T ( sno, pno )
     ),
     spq AS
     ( SELECT *
         FROM ( VALUES ( 'S2', 'P1', 200 ),
                       ( 'S3', 'P1', 1000 ) )
              AS T ( sno, pno, qty )
     )
SELECT sno
  FROM spq
 WHERE pno = 'P1'
EXCEPT
SELECT sno
  FROM spq
 WHERE pno = 'P1'
       AND qty = 1000;