SQL NOT IN Paragraph

Question

I have a request that does not work as expected

Q1:
SELECT id, name 
FROM vw_x 
WHERE id NOT IN (select pid from table_x)
GROUP BY id, name
Having max(c_date) > GETDATE()

Q2:
SELECT id, name 
FROM vw_x 
GROUP BY id, name
Having max(c_date) > GETDATE()

Q1 doesn’t return anything, although I know that these identifiers are not in table_x Q2 works correctly, NOT IN

What could be wrong with my request?

Answer 1

you have a null value in the table

try it

SELECT id, name 
FROM vw_x 
WHERE id NOT IN (select pid from table_x where pid is not null)
GROUP BY id, name
Having max(c_date) > GETDATE()

or

SELECT id, name 
FROM vw_x 
WHERE  NOT EXISTS (select 1 from table_x  where pid = vw_x.id  )
GROUP BY id, name
Having max(c_date) > GETDATE()

See also Select all rows from one table that do not exist in another table.

Answer 2

how about using left join?

SELECT id, name 
FROM vw_x 
LEFT JOIN table_x on id = pid
WHERE pid IS NULL
GROUP BY id, name
Having max(c_date) > GETDATE()

Answer 3

: . SQL Server , , NOT IN . :

select * from table where id not in (select id from tableB where somecondition(x))

When a subquery contains a list of identifiers, the query returns data as expected. But when the subquery returns nothing, the query will still return data, but then it gets stuck.

I changed the request to the following and solved the problem:

select * from table where id not in (select id from tableB where somecondition(x) **union all select 0**)

which ensures that the subquery contains at least one number.