[sql] SQL “하위 쿼리에없는 경우 선택”은 결과를 반환하지 않습니다

Program / 글쓴이

면책 조항 : 나는 문제를 알아 냈지만 (나는 생각한다) 어디서나 쉽게 찾을 수 없기 때문에이 문제를 Stack Overflow에 추가하고 싶었다. 또한 누군가가 나보다 더 나은 대답을 할 수 있습니다.

하나의 테이블 “공통”이 다른 여러 테이블에서 참조되는 데이터베이스가 있습니다. 공통 테이블의 어떤 레코드가 고아인지 확인하고 싶었습니다 (즉, 다른 테이블에서 참조가 없었습니다).

이 쿼리를 실행했습니다.

select *
from Common
where common_id not in (select common_id from Table1)
and common_id not in (select common_id from Table2)

고아 레코드가 있지만 레코드가 반환되지 않았다는 것을 알고 있습니다. 왜 안돼?

(중요한 경우 SQL Server입니다.)

답변

최신 정보:

내 블로그의이 기사에서는 메소드 간의 차이점에 대해 자세히 설명합니다.

이러한 쿼리를 수행하는 세 가지 방법이 있습니다.

  • LEFT JOIN / IS NULL:

    SELECT  *
FROM    common
LEFT JOIN
        table1 t1
ON      t1.common_id = common.common_id
WHERE   t1.common_id IS NULL

  • NOT EXISTS:

    SELECT  *
FROM    common
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    table1 t1
        WHERE   t1.common_id = common.common_id
        )

  • NOT IN:

    SELECT  *
FROM    common
WHERE   common_id NOT IN
        (
        SELECT  common_id
        FROM    table1 t1
        )

때 table1.common_idNull을 허용하지 않습니다, 모든 쿼리는 의미 적으로 동일합니다.

Null을 허용 하는 경우 값이을 포함하는 목록의 값과 일치하지 않으면을 반환 하기 때문에 NOT IN달라 집니다.INNOT INNULLNULL

이것은 혼란 스러울 수 있지만 이에 대한 대체 구문을 기억하면 더 분명해질 수 있습니다.

common_id = ANY
(
SELECT  common_id
FROM    table1 t1
)

이 조건의 결과는 목록 내 모든 비교의 부울 곱입니다. 물론 단일 NULL값은NULL 전체 결과 NULL도 렌더링되는 결과가 생성 됩니다.

우리는 결코 그렇게 말할 수 없습니다 common_id값 중 적어도 하나가이 (가)이므로이 목록의 내용과 동일 는 없습니다 NULL.

다음과 같은 데이터가 있다고 가정하십시오.

common

--
1
3

table1

--
NULL
1
2

LEFT JOIN / IS NULL및 NOT EXISTS반환 3, NOT IN반환하지 않습니다 아무것도 항상 하나를로 평가하기 때문에 (FALSE 또는 NULL).

에서 MySQL, 비 – 널 열의 경우에, LEFT JOIN / IS NULL그리고 NOT IN보다 약간 (수 퍼센트)을보다 효율적NOT EXISTS . 열이 널 입력 가능 NOT EXISTS하면 가장 효율적입니다 (다수는 아님).

에서 Oracle세 쿼리 모두 동일한 계획 ( ANTI JOIN)을 생성합니다.

에서 SQL Server, NOT IN/ NOT EXISTS때문에 더 효율적 LEFT JOIN / IS NULL에 최적화되지 않을 수 ANTI JOIN의 최적화.

에서 PostgreSQL, LEFT JOIN / IS NULL그리고 NOT EXISTS보다 더 효율적이다 NOT IN, 그들이가에 최적화 된 사인 인 Anti Join반면, NOT IN사용 hashed subplan(또는 일반은 subplan서브 쿼리 해시에 너무 큰 경우)

답변

세계가 두 값의 부울 자리가 되려면 null 값 (세 번째 값)을 직접 방지해야합니다.

리스트 측에서 널을 허용하는 IN 절을 작성하지 마십시오. 그들을 걸러 내십시오!

common_id not in
(
  select common_id from Table1
  where common_id is not null
)

답변

Table1 또는 Table2에는 common_id에 대한 일부 null 값이 있습니다. 이 쿼리를 대신 사용하십시오.

select *
from Common
where common_id not in (select common_id from Table1 where common_id is not null)
and common_id not in (select common_id from Table2 where common_id is not null)

답변

select *
from Common c
where not exists (select t1.commonid from table1 t1 where t1.commonid = c.commonid)
and not exists (select t2.commonid from table2 t2 where t2.commonid = c.commonid)

답변

내 머리 꼭대기에서

select c.commonID, t1.commonID, t2.commonID
from Common c
     left outer join Table1 t1 on t1.commonID = c.commonID
     left outer join Table2 t2 on t2.commonID = c.commonID
where t1.commonID is null
     and t2.commonID is null

나는 몇 가지 테스트를 수행했으며 여기에 @patmortech의 답변과 @rexem의 의견이 있습니다.

table1 또는 Table2가 commonID에서 색인화되지 않은 경우 테이블 스캔이 수행되지만 @patmortech의 쿼리는 여전히 두 배 빠릅니다 (100K 행 마스터 테이블의 경우).

commonID에서 색인화되지 않은 경우 두 개의 테이블 스캔이 발생하고 차이는 무시할 수 있습니다.

둘 다 commonID에서 색인화되는 경우 “존재하지 않음”조회가 1/3 시간에 실행됩니다.

답변

SELECT T.common_id
  FROM Common T
       LEFT JOIN Table1 T1 ON T.common_id = T1.common_id
       LEFT JOIN Table2 T2 ON T.common_id = T2.common_id
 WHERE T1.common_id IS NULL
   AND T2.common_id IS NULL

답변

common_id에 대해 다음 값을 가정하십시오.

Common - 1
Table1 - 2
Table2 - 3, null

우리는 Common의 행이 다른 테이블에 없기 때문에 반환하기를 원합니다. 그러나, 널 (null)은 멍키 렌치에 던져진다.

이 값을 사용하면 쿼리는 다음과 같습니다.

select *
from Common
where 1 not in (2)
and 1 not in (3, null)

이는 다음과 같습니다.

select *
from Common
where not (1=2)
and not (1=3 or 1=null)

여기서 문제가 시작됩니다. null과 비교할 때 답을 알 수 없습니다 . 따라서 쿼리는

select *
from Common
where not (false)
and not (false or unkown)

거짓 또는 알 수 없음 : 불명

select *
from Common
where true
and not (unknown)

사실이며 알려지지 않은 것은 또한 알려지지 않습니다.

select *
from Common
where unknown

where 조건은 결과가 알려지지 않은 레코드를 반환하지 않으므로 레코드를 다시 얻지 못합니다.

이를 처리하는 한 가지 방법은 in 대신 존재 연산자를 사용하는 것입니다. Exists는 열이 아닌 행에서 작동하기 때문에 unkown을 반환하지 않습니다. (행이 존재하거나 존재하지 않습니다. 행 수준 에서이 null 모호성은 없습니다!)

select *
from Common
where not exists (select common_id from Table1 where common_id = Common.common_id)
and not exists (select common_id from Table2 where common_id = Common.common_id)