SQL "select where not in subquery"에서 결과가 반환되지 않음

SQL "select where not in subquery"에서 결과가 반환되지 않음

면책사항:문제는 파악했습니다만(쉽게 찾을 수 없었기 때문에) 스택 오버플로에 이 문제를 추가하고 싶었습니다.그리고 나보다 더 나은 답을 가진 사람이 있을지도 몰라.

하나의 테이블 "Common"이 다른 여러 테이블에서 참조되는 데이터베이스가 있습니다.Common 테이블의 어떤 레코드가 고립되어 있는지 확인하려고 했습니다(다른 테이블에서 참조가 없는 경우).

다음 쿼리를 실행했습니다.

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 NULLSQL Server
• NOT IN ★★NOT EXISTS ★★LEFT JOIN / IS NULLPostgreSQL
• NOT IN ★★NOT EXISTS ★★LEFT JOIN / IS NULLOracle
• NOT IN ★★NOT EXISTS ★★LEFT JOIN / IS NULLMySQL

---

이러한 쿼리에는 다음 3가지 방법이 있습니다.

• 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는 늘이 아닙니다.이러한 쿼리는 모두 의미론적으로 동일합니다.

인 경우, 「」NOT ININNOT IN) (반환) 。NULL이 " "를 의 어떤 하지 않는 NULL.

이는 혼란스러울 수 있지만 이에 대한 대체 구문을 호출하면 더 명확해질 수 있습니다.

common_id = ANY
(
SELECT  common_id
FROM    table1 t1
)

이 조건의 결과는 목록 내 모든 비교의 부울 곱입니다. ★★★★★★★★★★★★★★★★★★★★★★★.NULL는 ""를 합니다.NULL NULL

할 수 없다common_id는, 1이 「1 개의 1 개의 1 에, 이 리스트의 어느 하지 않습니다.NULL.

다음과 같은 데이터가 있다고 가정합니다.

common

--
1
3

table1

--
NULL
1
2

LEFT JOIN / IS NULL ★★★★★★★★★★★★★★★★★」NOT EXISTS3,NOT IN아무것도 반환하지 않습니다(항상 어느 쪽인가로 평가되기 때문입니다).FALSE ★★★★★★★★★★★★★★★★★」NULL를 참조해 주세요.

»MySQL 컬럼의 Non-timeoutableLEFT JOIN / IS NULL ★★★★★★★★★★★★★★★★★」NOT IN이 좋다(12%)보다 약간NOT EXISTS 수 있는 "늘NOT EXISTS가장 효율적입니다(다시 말씀드리지만 그다지 효율적이지 않습니다).

»Oracle 같은 , 3개의 플랜)을합니다.ANTI JOIN를 참조해 주세요.

»SQL Server,NOT INNOT EXISTS, 효율적이다.LEFT JOIN / IS NULL할 수 ANTI JOIN최적기에 의해.

»PostgreSQL,LEFT JOIN / IS NULL ★★★★★★★★★★★★★★★★★」NOT EXISTS이 NOT IN되어 있기 에 최적화되어 있습니다.Anti Join와 동시에 , 「」를 참조해 주세요.NOT INhashed subplan 평야()도)subplan너무 해시할 수 ).

월드를 2개의 값의 부울 플레이스로 할 경우 null(3번째 값) 대소문자를 직접 방지해야 합니다.

리스트 측에 늘을 허용하는 IN 절을 쓰지 마십시오.걸러내!

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

간단한 답변:

서브쿼리에 의해 반환된 컬렉션 내에 NULL이 있습니다.에 이 하거나 NULL을 사용하여 할 수 .NOT EXISTS 술어하다NOT IN은연중에 행해집니다.

긴 답변 (T-SQL Fundamentals, 제3판, Itzik Ben-Gan에서)

다음은 예를 제시하겠습니다.Sales에 주문 ID가 NULL인 주문이 있다고 가정합니다.테이블을 정렬하여 하위 쿼리는 일부 정수와 NULL 값을 반환합니다.

SELECT custid, companyname
FROM Sales.Customers
WHERE custid NOT IN(SELECT O.custid
             FROM Sales.Orders AS O);

위의 쿼리에서 빈 세트가 반환되는 이유에 대한 설명:

은 분명히 '오빠', '오빠'입니다.NULL[ Orders ] 더 ]NULL부터 시작해보죠예상대로 동작하는 부분부터 시작합시다.IN 술어는 주문을 한 고객(예: 고객 85)에 대해 TRUE를 반환합니다. 이러한 고객은 하위 쿼리에 의해 반환되기 때문입니다.는 NOT를 합니다.IN즉 '술어'입니다.따라서NOT TRUE becomes가 되다FALSE고객님은 폐기됩니다.여기서 예상되는 동작은 고객 ID가 [Orders]테이블에 표시되어 있는 것을 알고 있는 경우 반환을 원하지 않는다는 것을 확실히 알고 있는 것입니다.

의 고객 이 아닌 고객 NULL ID가NULL주문서의 고객 ID는 고객이 있는지 확실히 알 수 없습니다.또, 고객이 없는지도 확실히 알 수 없습니다.★★★★★★★★★★★★★★★★★?예를 들어 이 설명을 명확하게 하고 싶습니다.

IN 반환 " " "UNKNOWN22와 같은 고객의 경우 주문의 알려진 고객 ID 세트에 표시되지 않습니다.FALSE가 , 하면 FALSE가 입니다.UNKNOWNFALSE ★★★UNKNOWN 율율UNKNOWN 라는해 보세요.22 NOT IN (1, 2, <other non-22 values>, NULL) 이은 이렇게 표현하면 됩니다.NOT 22 IN (1, 2, …, NULL) 은 로 할 수 NOT (22 = 1 OR 22 = 2 OR … OR 22 = NULL) 안에 하면, 이 표현들을 얻을 수 있습니다NOT (FALSE OR FALSE OR … OR UNKNOWN) 「」로 됩니다.NOT UNKNOWNUNKNOWN.

의 UNKNOWN 「」를 에, 「」를 참조해 주세요.NOT고객 를 판별할 수 은 고객 ID가 세트에 표시되는지 아닌지를 할 수 없기 때문입니다.NULL아이디입니다.은 이 부분을 입니다.UNKNOWNNOT 「」를 출력하고 .UNKNOWN를 알 수 않는지 도 알 수 없습니다에 의해, 해 주세요.UNKNOWN술어의 결과입니다.

'를 때,NOT IN 1개의 한다.NULL쿼리에서는 항상 빈 세트가 반환됩니다.그렇다면, 이러한 문제를 피하기 위해 어떤 연습을 할 수 있을까요?, 컬럼에 허용되지 않는 경우NULLs NOT NULL둘째, 쓰는 모든 쿼리에서 NULL과 3값 로직을 고려해야 합니다.쿼리가 NULL을 처리할 수 있는지, 처리할 수 있는 경우 SQL의 NULL 처리가 올바른지 여부를 명시적으로 고려합니다.그렇지 않을 때는 당신이 개입해야 합니다.를 들어,빈 세트를 는 "", "", "", "", ""와 입니다.NULL고객 ID가 기존의 값 집합에만 표시되는지 여부를 확인하려면 NULL을 명시적으로 또는 암묵적으로 제외해야 합니다.하려면 , .O.custid무효로 하다

SELECT custid, companyname
FROM Sales.Customers
WHERE custid NOT IN(SELECT O.custid
                    FROM Sales.Orders AS O
                    WHERE O.custid IS NOT NULL);

NULL을 하면 .NOT EXISTS 술어하다NOT IN 이렇게요.

SELECT custid, companyname
FROM Sales.Customers AS C
WHERE NOT EXISTS
   (SELECT *
    FROM Sales.Orders AS O
    WHERE O.custid = C.custid);

은 꼭 기억해주세요.IN,EXISTS2번입니다. EXISTS 반환하다TRUE ★★★★★★★★★★★★★★★★★」FALSE 절대 하지 않는다UNKNOWN가 NULLO.custid, , the expression evaluates to 이 표현은 다음과 같이 평가됩니다.UNKNOWN행은 필터링됩니다.행이 필터링 됩니다.위 에 서 as내 far the asEXISTS predicate is concerned, the 술어는 관련된 것입니다.NULL만약 그들이 그곳에 있지 않았지만, 그들은 자연적으로 제거된다.케이스는 없었던 것처럼 자연스럽게 없어집니다. So 그렇게EXISTS의 고객합니다. 때문에 라, 용, 용, 용, 용, 용을 사용하는 것이 안전합니다.NOT EXISTSNOT IN.

위의 정보는 제4장 - 서브쿼리, T-SQL Fundamentals, 제3판에서 발췌한 것입니다.

표 1 또는 표 2에는 common_id에 대해 몇 가지 늘 값이 있습니다.대신 다음 쿼리를 사용합니다.

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의 코멘트를 포함한 결과를 나타냅니다.

표 1 또는 표 2 중 하나가 공통으로 색인화되지 않은 경우ID는 테이블스캔을 받습니다만, @patmortech의 쿼리는 여전히 2배 고속입니다(10K 행 마스터 테이블의 경우).

둘 다 공통으로 인덱싱되지 않은 경우아이디, 두 개의 테이블 스캔을 받았는데 그 차이는 무시해도 될 정도야

둘 다 공통으로 색인화된 경우ID, "존재하지 않음" 쿼리는 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의 행을 되돌리고 싶습니다.하지만 늘은 몽키 렌치를 던집니다.

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

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)

false 또는 unknown은 알 수 없습니다.

select *
from Common
where true
and not (unknown)

true 및 not unknown도 알 수 없습니다.

select *
from Common
where unknown

where 조건에서는 결과를 알 수 없는 레코드는 반환되지 않으므로 레코드는 반환되지 않습니다.

이 문제를 해결하는 한 가지 방법은 in이 아닌 기존 연산자를 사용하는 것입니다.열이 아닌 행에서 작동하므로 알 수 없는 상태를 반환하지 않습니다.(행은 존재하거나 존재하지 않습니다.행 레벨에서는 이 늘의 애매함이 없습니다!)

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가 없습니다(표1에서 ISULL(common_id, 'dummy-data' 선택)

및 common_id가 없습니다(표2에서 ISULL(common_id, 'dummy-data' 선택).

위의 항목을 이해하려면 다음 예를 따르십시오.

또한 다음 링크를 방문하여 안티 가입을 확인할 수 있습니다.

select department_name,department_id from hr.departments dep
where not exists 
    (select 1 from hr.employees emp
    where emp.department_id=dep.department_id
    )
order by dep.department_name;

DEPARTMENT_NAME DEPARTMENT_ID
Benefits    160
Construction    180
Contracting 190
.......

, ★★★★★★★★★★★★★★★★★★★★★★★★★★★★.NOT IN이 경우 데이터를 얻을 수 없습니다.

select Department_name,department_id from hr.departments dep 
where department_id not in (select department_id from hr.employees );

데이터를 찾을 수 없습니다.

일부러)로서 .select department_id from hr.employees는 늘 쿼리 가 false로 는 늘 값을 반환하고 있으며 쿼리 전체가 false로 평가됩니다.아래와 같이 SQL을 약간 변경하고 NVL 함수로 null 값을 처리하면 알 수 있습니다.

select Department_name,department_id from hr.departments dep 
where department_id not in (select NVL(department_id,0) from hr.employees )

이제 데이터를 얻을 수 있습니다.

DEPARTMENT_NAME DEPARTMENT_ID
Treasury    120
Corporate Tax   130
Control And Credit  140
Shareholder Services    150
Benefits    160
....

NVL 함수로 null 값을 처리하여 다시 데이터를 받고 있습니다.

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