SQL "하위 쿼리에없는 경우 선택"은 결과를 반환하지 않습니다.

SQL "하위 쿼리에없는 경우 선택"은 결과를 반환하지 않습니다.

---

면책 조항 : 나는 문제를 알아 냈지만 (나는 생각한다) 어디서나 쉽게 사용할 수 없기 때문에 문제를 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널을 허용하지 않습니다, 모든 쿼리는 의미 적으로 동일합니다.

nullable 일 번호 NOT IN. 왜냐하면 값이 포함되는 목록의 어떤 것과 일치하지 않을 때 반환 IN때문 입니다.NOT INNULLNULL

이 혼란 스러울 수 있도록 이에 대한 대체 구문을 상기하면 더 분명해질 수 있습니다.

common_id = ANY
(
SELECT  common_id
FROM    table1 t1
)

이 조건의 결과는 목록 내 모든 비교의 부울 곱입니다. 물론, one- 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. 열이 nullable이면 NOT EXISTS가장 먼저입니다 (다시 말하지만 많지는 않습니다).

에서 Oracle세 쿼리 모두 동일한 계획 (AN 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 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

---

내 머리에서 ...

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의 쿼리는 여전히 2 배 빠 사용하지 않는 경우 (100K 행 마스터 테이블의 경우).

둘 다 commonID에서 인덱싱되지 않은 두 개의 테이블 스캔이 발생하고 그 차이는 무시할 수 있습니다.

둘 다 commonID에 인덱싱 된 경우 "존재하지 않음"발생 쿼리가 1/3 시간에 실행됩니다.

---

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과 대답은 unknown 입니다. 따라서 쿼리는

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

거짓 또는 알 수 없음 :

select *
from Common
where true
and not (unknown)

사실과 알 수 없음도 알 수 없습니다.

select *
from Common
where unknown

조건은 결과가 알려지지 않은 레코드를 반환하지 않습니다.

이를 처리하는 한 가지 방법은 in이 아닌 존재 연산자를 사용하는 것입니다. 존재는 열이 아닌 행에서 작동하는 것이 unkown을 반환하지 않습니다. (행이 존재하거나 존재하지 않습니다.)

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)

---

이것은 나를 위해 일했습니다 :)

공통에서 * 선택

어디

common_id가 ( Table1 에서 ISNULL (common_id, 'dummy-data') 선택 )

및 common_id가 없습니다 ( Table2 에서 ISNULL (common_id, 'dummy-data') 선택 ).

---

select *,
(select COUNT(ID)  from ProductMaster where ProductMaster.CatID = CategoryMaster.ID) as coun 
from CategoryMaster

참고 URL : https://stackoverflow.com/questions/1406215/sql-select-where-not-in-subquery-returns-no-results