Problem with using IN in sql

Question

Hi!
Can you please help me to understand where is the problem?

SELECT mz_t_NaznExec.p_Services FROM mz_t_NaznExec

Above script returns recordset where record with p_Services = 200000000000115 not exists

SELECT mz_t_Services.p_ID from mz_t_Services

Above script returns recordset where record with id = 200000000000115 exists
But this query returns no record

SELECT mz_t_Services.p_ID from mz_t_Services where mz_t_Services.p_ID not in(SELECT mz_t_NaznExec.p_Services FROM mz_t_NaznExec)

I am using MSSQL
EDIT:
Both p_ID and p_Services data types ara bigint

Answer 1

Are there any NULLs in mz_t_NaznExec.p_Services? "NOT IN" will always fail if there is even one NULL in the resultset.

Answer 2

SELECT mz_t_Services.p_ID from mz_t_Services where mz_t_Services.p_ID not in(SELECT ISNULL(mz_t_NaznExec.p_Services,0) FROM mz_t_NaznExec)

or better

SELECT mz_t_Services.p_ID from mz_t_Services where mz_t_Services.p_ID not in(SELECT mz_t_NaznExec.p_Services FROM mz_t_NaznExec WHERE mz_t_NaznExec.p_Services is not null)

Answer 3

This will also work:

SELECT p_id
  FROM mz_t_services
 WHERE NOT EXISTS
       ( SELECT 1
           FROM mz_t_naznexec
          WHERE mz_t_naznexec.p_services = mz_t_services.p_id
       )

I prefer it to the solutions above because it more reliably uses indexes (in my experience). It works whether there are nulls present or not, so you can just use that form all the time regardless of your expectations of the table contents, and not worry about people asking you why you're checking for nulls in a column that doesn't allow them.

In any case, you should try both solutions and see which works better for you.