복붙노트

[SQL] NOT IN 절 내부에 NULL 값

SQL

NOT IN 절 내부에 NULL 값

내가하지 제약 및 다른 조인 된 부분에를 사용하여 동일한 쿼리를 하나씩라고 생각하는 것에 대해 다른 기록 수를 받았을 때이 문제를 내놓았다. 제약 조건에서하지의 표는 해당 쿼리는 0 레코드 수를 반환하는 원인 중 하나 개는 null 값 (잘못된 데이터)를 가지고 있었다. 나는 종류의 이유를 이해하지만 도움이 완전히 개념을 파악 사용할 수 있습니다.

단순히 상태로, 왜 쿼리 A는 결과를 반환하지만, B는 수행하지 않는 이유는 무엇입니까?

A: select 'true' where 3 in (1, 2, 3, null)
B: select 'true' where 3 not in (1, 2, null)

이것은 내가 또한 세트를 호출하면 그 결과를 반환하는 원인 B를 ANSI_NULLS 것을 발견 SQL 서버 2005에 있었다.

해결법

  1. ==============================

    1.쿼리 A는 동일하다 :

    쿼리 A는 동일하다 :

    select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

    3 = 3 사실이기 때문에, 당신은 결과를 얻을.

    쿼리 B는 동일합니다 :

    select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null

    ANSI_NULLS가 켜져있을 때, 3 <> null의 UNKNOWN, 술어 평가하여 너무 UNKNOWN에, 당신은 어떤 행을하지 않습니다.

    ANSI_NULLS가 꺼져있을 때 3 <> null의 술어 평가하여 이렇게 참으로, 사실, 당신은 행을 얻을.

  2. ==============================

    2.때마다 당신이 정말로 3 치 논리 다루고있다 NULL을 사용합니다.

    때마다 당신이 정말로 3 치 논리 다루고있다 NULL을 사용합니다.

    첫 번째 쿼리에 WHERE 절 평가되는 결과를 반환합니다 :

        3 = 1 or 3 = 2 or 3 = 3 or 3 = null
which is:
    FALSE or FALSE or TRUE or UNKNOWN
which evaluates to 
    TRUE

    두 번째 :

        3 <> 1 and 3 <> 2 and 3 <> null
which evaluates to:
    TRUE and TRUE and UNKNOWN
which evaluates to:
    UNKNOWN

    알 수없는이 FALSE와 동일하지 않습니다 당신은 쉽게 호출하여 테스트 할 수 있습니다 :

    select 'true' where 3 <> null
select 'true' where not (3 <> null)

    두 쿼리는 당신에게 어떤 결과를 제공하지 않습니다

    알 수 없음 (UNKNOWN)가 FALSE로 동일 경우 첫 번째 쿼리는 당신에게 거짓을 줄 것이라고 두 번째는 NOT (FALSE)와 같은이었을 것 같은 TRUE로 평가해야 가정. 그건 사실이 아니다.

    SqlServerCentral에이 주제에 대한 아주 좋은 기사가있다.

    널 (NULL)와 3 치 논리의 모든 문제는 약간 처음에는 혼동 될 수 있지만 TSQL에서 올바른 쿼리를 작성하기 위해 이해하는 것이 필수적입니다

    내가 추천하는 또 다른 기사는 SQL 집계 함수 및 NULL입니다.

  3. ==============================

    3.NULL은 알 수없는이 때문에 모르겠 NULL 값이 테스트되고 값이 아니라고 할 수있는 방법이 없기 때문에 항상 0 레코드를 반환 할 수있는 값의 목록에서 NULL 또는 널 (null)을 포함하는 쿼리.

    NULL은 알 수없는이 때문에 모르겠 NULL 값이 테스트되고 값이 아니라고 할 수있는 방법이 없기 때문에 항상 0 레코드를 반환 할 수있는 값의 목록에서 NULL 또는 널 (null)을 포함하는 쿼리.

  4. ==============================

    4.당신이 사용이 NULL이 아닌 경우 null로 비교가 정의되어 있지 않습니다.

    당신이 사용이 NULL이 아닌 경우 null로 비교가 정의되어 있지 않습니다.

    NULL (쿼리 A)에 3을 비교했을 때 그래서, 그것은 undefined를 반환합니다.

    즉 SELECT '참'3 곳 (1,2, NULL)  과 SELECT '진정한'어디에 3 NOT IN (1, 2, 널 (null))

    아직 정의되지 NOT (미정)과 동일한 결과를 생성하지만, TRUE하지 않습니다

  5. ==============================

    5.글을 쓰는 시점에서이 질문의 제목입니다

    글을 쓰는 시점에서이 질문의 제목입니다

    문제의 텍스트에서이 문제가 아니라 SQL DDL CONSTRAINT보다는 SQL DML SELECT 쿼리에서 발생 된 것으로 보인다.

    그러나, 특히 제목의 표현을 주어, 나는 여기에서 몇 가지 문이 잠재적으로 문을 오해하는 것을 지적 할의 라인을 따라 그 (의역)

    이 SQL DML의 경우이지만 제약 조건을 고려할 때, 효과가 다르다.

    질문에 술어에서 직접 촬영 한 두 가지 제약 조건이 매우 간단한 테이블을 고려 (그리고 @Brannon의 탁월한 대답에 언급)

    DECLARE @T TABLE 
(
 true CHAR(4) DEFAULT 'true' NOT NULL, 
 CHECK ( 3 IN (1, 2, 3, NULL )), 
 CHECK ( 3 NOT IN (1, 2, NULL ))
);

INSERT INTO @T VALUES ('true');

SELECT COUNT(*) AS tally FROM @T;

    브래넌의 답변 @ 당, 제 제약 (IN을 사용하여) TRUE로 평가하고 두 번째 제약은 UNKNOWN으로 평가 (NOT IN을 사용하여). 그러나, 삽입 성공! 따라서,이 경우 우리가 실제로 결과로 삽입 된 행을 가지고 있기 때문에 "당신이 어떤 행을하지 않는다"라고 엄격하게 올바르지 않습니다.

    위의 효과는 참 관해서는 SQL-92 표준으로 올바른 하나입니다. 비교와 SQL-92 사양에서 다음 섹션을 대조

    다시 말해:

    UNKNOWN으로 평가가이 조건을 만족하지 않기 때문에 어디는 "사실이다"때 SQL DML에서 행이 결과에서 제거됩니다.

    이 조건을 만족 않기 때문에 그들이 UNKNOWN으로 평가하는 경우 SQL DDL (즉 제약)에서, 행은 "거짓없는"결과에서 제거되지 않습니다.

    SQL DML 및 SQL DDL의 효과는 각각 모순 보일 수 있지만, (그들 제약 조건을 만족시키기 위해 실패하지 수 있도록 더 정확하게)들이 제약 조건을 충족 할 수 있도록하여 UNKNOWN 결과에게 '의심의 혜택'을주는 실제적인 이유가있다 :이 동작하지 않고, 모든 제약 조건을 명시 적으로 null을 처리해야하고 그 언어 디자인의 관점에서 매우 만족스럽지 못한 것 (코더에 대한 권리 고통을 언급하지 않기!)

    추신. 당신은 내가 그것을 쓸 생각으로 "알 수없는 제약 조건을 만족하지 않는다"등의 논리를 따라 도전로를 발견하는 경우, 단순히 SQL DML에 널 (NULL) SQL DDL 열하고 새로운 것을 피함으로써이 모든 생략 할 수 고려 즉, 널 (null)을 (예를 들어 외부 조인) 생산!

  6. ==============================

    6.(A)에, 3 세트의 각 멤버 얻었다 (FALSE, FALSE, TRUE, UNKNOWN) 같은지 검사한다. 요소 중 하나에 해당되므로, 조건이 TRUE이다. (그것은 최초의 진정한 안타 3 = NULL을 평가하지으로 실제로 즉시 중지 있도록 일부 단락은, 여기에서 발생 가능성도 있습니다.)

    (A)에, 3 세트의 각 멤버 얻었다 (FALSE, FALSE, TRUE, UNKNOWN) 같은지 검사한다. 요소 중 하나에 해당되므로, 조건이 TRUE이다. (그것은 최초의 진정한 안타 3 = NULL을 평가하지으로 실제로 즉시 중지 있도록 일부 단락은, 여기에서 발생 가능성도 있습니다.)

    B에서, 나는 ((1,2, null이) 3) NOT 등의 상태를 평가하고있다 생각합니다. UNKNOWN 응집 된 세트 수율 (FALSE, FALSE, UNKNOWN) 같은지 3 테스트. NOT (UNKNOWN) 수익률의 UNKNOWN. 그래서 전반적인 조건의 진실은 마지막에 기본적으로 FALSE로 취급되는, 알 수 없습니다.

  7. ==============================

    7.그것은 NOT IN (하위 쿼리가) 제대로 널 (null)을 처리하지 않고 NOT 존재의 찬성 피해야한다고 여기 답변에서 결론을 내릴 수있다. 그러나 이러한 결론은 시기상조 일 수 있습니다. 다음과 같은 시나리오에서, 크리스 날짜 (데이터베이스 프로그래밍 및 디자인, 2 권 9 호 1989 년 9 월)에 적립, 그것은 NOT이 존재하는 것이 아니라, 그 핸들이 제대로 null을 반환에서 올바른 결과가 아닙니다.

    그것은 NOT IN (하위 쿼리가) 제대로 널 (null)을 처리하지 않고 NOT 존재의 찬성 피해야한다고 여기 답변에서 결론을 내릴 수있다. 그러나 이러한 결론은 시기상조 일 수 있습니다. 다음과 같은 시나리오에서, 크리스 날짜 (데이터베이스 프로그래밍 및 디자인, 2 권 9 호 1989 년 9 월)에 적립, 그것은 NOT이 존재하는 것이 아니라, 그 핸들이 제대로 null을 반환에서 올바른 결과가 아닙니다.

    공급 수량 (수량)에 공급 부품 (PNO)로 알려져있다 (SNO)을 나타내는 테이블 (SP)를 생각해 보자. 표는 현재 다음과 같은 값을 원하는 분야

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

    수량이 공급 업체가이 어떤 양에 알려져 있지 않은 경우에도 공급 부품에 알려진 사실을 기록 할 수있을 즉, Null을 허용합니다.

    이 작업은 공급 부품 번호 'P1'가 아니라 천의 양에 알려진 공급 업체를 찾을 수 있습니다.

    NOT 올바르게에 다음 사용은 공급 업체 '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'
                       );

    그러나, 질의는 아래의 동일한 일반 구조를 사용하지만, 함께하지 존재하지만 부정확 한 결과에 보조 장비 'S1'을 포함한다 (즉되는 금액은 널)

    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
                      );

    그래서하지 은색 총알이가 등장 할 수있다되지 EXISTS!

    물론, 문제의 근원은 그러므로 '진짜'솔루션은 그 널 (null)을 제거하는 것입니다, 널의 존재이다.

    이 두 테이블을 사용하여 (다른 가능한 설계 중)을 달성 할 수 있습니다 :

    주목할 것은 아마 외래 키 제약 SPQ 참조 특검팀이 있어야합니다.

    결과는 다음 마이너스 관계 연산자 (표준 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;
  8. ==============================

    8.널 의미하고 아무것도 알 수없는이 아닌 데이터 값입니다 데이터의 부재. 그것은 매우 쉬운 C 형 언어로 널 (null)이 실제로 아무것도 포인터를 사용하는 경우 때문에 프로그래밍 배경의 사람들이 혼동 할 수 있습니다.

    널 의미하고 아무것도 알 수없는이 아닌 데이터 값입니다 데이터의 부재. 그것은 매우 쉬운 C 형 언어로 널 (null)이 실제로 아무것도 포인터를 사용하는 경우 때문에 프로그래밍 배경의 사람들이 혼동 할 수 있습니다.

    따라서 제 케이스 (3)에 반환된다 (1,2,3, NULL) 사실 때문에 세트에 참

    두 번째에서는 그러나 당신은 그것을하기 줄일 수 있습니다

    (널) 여기서 3하지에서 '사실'을 선택

    이 빈 세트 아니지만 알 수없는 세트 - 파서 당신이 그것을 비교하는에 설정에 대해 아무것도 알고 있기 때문에 아무것도 반환되지 않습니다 그래서. (1, 2, 널 (null))를 사용하면 (1, 2) 세트는 분명히 거짓이기 때문에 도움이되지 않습니다,하지만 당신은 알 수있는 알 수없는에 대한 것을 and'ing하고 있습니다.

  9. ==============================

    9.하위 쿼리가 포함 NULL을 위해 NOT IN으로 필터링 할 경우 그냥 널 확인

    하위 쿼리가 포함 NULL을 위해 NOT IN으로 필터링 할 경우 그냥 널 확인

    SELECT blah FROM t WHERE blah NOT IN
        (SELECT someotherBlah FROM t2 WHERE someotherBlah IS NOT NULL )
  10. ==============================

    10.이 보이입니다 :

    이 보이입니다 :

    select party_code 
from abc as a
where party_code not in (select party_code 
                         from xyz 
                         where party_code = a.party_code);

    이 ANSI 설정에 관계없이 작동

  11. ==============================

    11.SQL 진리 값에 대한 세 가지 값 논리를 사용합니다. 은 IN 쿼리는 예상되는 결과를 생성합니다 :

    SQL 진리 값에 대한 세 가지 값 논리를 사용합니다. 은 IN 쿼리는 예상되는 결과를 생성합니다 :

    SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE col IN (NULL, 1)
-- returns first row

    그러나 추가하는 것은 결과를 반전하지 않습니다 NOT :

    SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE NOT col IN (NULL, 1)
-- returns zero rows

    위의 쿼리는 다음의 것과 동일하기 때문이다 :

    SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE NOT (col = NULL OR col = 1)

    여기에 where 절을 평가하는 방법이다 :

    | col | col = NULL (1) | col = 1 | col = NULL OR col = 1 | NOT (col = NULL OR col = 1) |
|-----|----------------|---------|-----------------------|-----------------------------|
| 1   | UNKNOWN        | TRUE    | TRUE                  | FALSE                       |
| 2   | UNKNOWN        | FALSE   | UNKNOWN (2)           | UNKNOWN (3)                 |

    그것을주의해라:

    사용자는 두 개 이상의 값 (예컨대 NULL, 1, 2)을 상기 실시 예를 확장 할 수 있지만, 그 결과는 동일 할 것이다 : 값 중 하나가 일절 행 일치 것 NULL 인 경우.

  12. ==============================

    12.존재에 참여 사이에이 논리의 차이를 알고 사용 될 수 있습니다 http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

    존재에 참여 사이에이 논리의 차이를 알고 사용 될 수 있습니다 http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

    13. from https://stackoverflow.com/questions/129077/null-values-inside-not-in-clause by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 관리 및 MS Access에서 SQL 쿼리를 디버깅  (0) 2020.03.07
[SQL] BY 쿼리 최적화 그룹은 사용자 당 최신 행을 검색하기  (0) 2020.03.07
[SQL] 어떻게 MySQL의에서 AUTO_INCREMENT를 재설정?  (0) 2020.03.07
[SQL] SQL 테이블에서 중복 값 찾기  (0) 2020.03.07
[SQL] 측면과 PostgreSQL에서 하위 쿼리의 차이점은 무엇입니까?  (0) 2020.03.07
,

티스토리툴바