면책 조항 : 나는 문제를 알아 냈지만 (나는 생각한다) 어디서나 쉽게 찾을 수 없기 때문에이 문제를 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입니다.)
답변
최신 정보:
내 블로그의이 기사에서는 메소드 간의 차이점에 대해 자세히 설명합니다.
NOT IN
대NOT EXISTS
대LEFT JOIN / IS NULL
:SQL Server
NOT IN
대NOT EXISTS
대LEFT JOIN / IS NULL
:PostgreSQL
NOT IN
대NOT EXISTS
대LEFT JOIN / IS NULL
:Oracle
NOT IN
대NOT EXISTS
대LEFT JOIN / IS NULL
:MySQL
이러한 쿼리를 수행하는 세 가지 방법이 있습니다.
-
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_id
Null을 허용하지 않습니다, 모든 쿼리는 의미 적으로 동일합니다.
Null을 허용 하는 경우 값이을 포함하는 목록의 값과 일치하지 않으면을 반환 하기 때문에 NOT IN
달라 집니다.IN
NOT IN
NULL
NULL
이것은 혼란 스러울 수 있지만 이에 대한 대체 구문을 기억하면 더 분명해질 수 있습니다.
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)