SQL select where not in select 语句 [英] SQL select where not in select statement

问题描述

我正在尝试选择我的数据库中不存在于子查询中的所有记录.

I'm trying to Select all the records in my database that don't exist in a subquery.

由于某种原因，即使子查询自己返回 2000 行左右并且主查询返回超过 5000 行，它也不会返回任何内容.我需要子查询中未包含的所有记录

For some reason it returns nothing even though the sub query returns 2000 or so rows on it's own and the main query returns over 5000. I need all the records that aren't contained in the subquery

SELECT ID
FROM PART
WHERE NOT ID IN 
(
SELECT DOCUMENT_ID AS ID
FROM USER_DEF_FIELDS
WHERE PROGRAM_ID = 'VMPRTMNT' AND ID = 'UDF-0000029'
)

推荐答案

这最好写成相关的 NOT EXISTS 子查询.

This is better written as a correlated NOT EXISTS subquery.

SELECT ID
  FROM PART
 WHERE NOT EXISTS 
    (
        SELECT 1
        FROM USER_DEF_FIELDS
        WHERE PROGRAM_ID = 'VMPRTMNT' 
          AND ID = 'UDF-0000029'
          AND DOCUMENT_ID = PART.ID
    )

这篇关于SQL select where not in select 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！