SQL select where not in select statement

Question

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

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'
)

possible duplicate of [Select rows not in another table, SQL Server query](http://stackoverflow.com/questions/8165534/select-rows-not-in-another-table-sql-server-query) — Palpatim, Jun 18 '15 at 18:35

score 3 · Accepted Answer · answered Jun 18 '15 at 18:39

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 statement

1 Answers1

Linked