Select Statement is not returning desired results with where clause containing NOT IN operator

Question

This is my query:

SELECT * 
FROM [dbo].[datamerge] 
WHERE Timesheet_Id = 'xxx' 
  AND AdminType_Id NOT IN (SELECT [ConfigValue] 
                           FROM [dbo].[adf_Config] 
                           WHERE [ConfigKey] = 'NonBillableEntries')

The above query returns 9 rows.

However, if I put in the value of the sub query I get 8 rows as expected:

SELECT * 
FROM [dbo].[datamerge] 
WHERE Timesheet_Id = 'xxx' 
  AND AdminType_Id NOT IN (106, 4, 3, 7, 100, 5, 105)

Any idea why this is happening?

EDIT

timesheet_Id    AdminType_Id
-----------------------------
16530056441     2033563818
16530056441     2033563818
16530056441     2033563818
16530056441     2033563818
16530056441     101
16530056441     101
16530056441     101
16530056441     101
16530056441     4

This is the table structure and sample data. I am not expecting the last row to be returned by the query.

And the result of the query

SELECT [ConfigValue] 
                           FROM [dbo].[adf_Config] 
                           WHERE [ConfigKey] = 'NonBillableEntries'

is

ConfigValue
106,4,3,7,100,5,105

Show us some sample table data, the current result(s), and the wanted result - all as formatted text (no images.) Simplify! [mcve]. — jarlh, Mar 31 '21 at 19:23
And what do you get with `SELECT [ConfigValue] FROM [dbo].[adf_Config] WHERE [ConfigKey] = 'NonBillableEntries'`? — Brian Stork, Mar 31 '21 at 19:35
Do you get a comma-separated string of numbers as a single row for ConfigValue? Or do you get seven, separate rows? — Brian Stork, Mar 31 '21 at 19:46
@BrianStork A single row for configValue separated by a string of numbers. — MAK, Mar 31 '21 at 19:47
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/230610/discussion-between-brian-stork-and-mak). — Brian Stork, Mar 31 '21 at 19:50
Does this answer your question? [NOT IN vs NOT EXISTS](https://stackoverflow.com/questions/173041/not-in-vs-not-exists) You have a nullable column, `NOT IN` doesn't work properly for that — Charlieface, Mar 31 '21 at 20:01

score 1 · Answer 1 · answered Mar 31 '21 at 19:24

1

It's better to use not exists. But if you want to use not in then please make sure there is no null value in the list. Please try this.

Select * from [dbo].[datamerge] WHERE Timesheet_Id = 'xxx' AND AdminType_Id NOT IN 
(SELECT [ConfigValue] FROM [dbo].[adf_Config] WHERE [ConfigKey] = 'NonBillableEntries'and ConfigValue is not null)

With not exists (strongly recommended)

Select * from [dbo].[datamerge] d WHERE Timesheet_Id = 'xxx' AND not exists
    (SELECT 1 FROM [dbo].[adf_Config] a WHERE a.[ConfigKey] = 'NonBillableEntries'and a.ConfigValue =d.AdminType_Id)

answered Mar 31 '21 at 19:24

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

I'm a big fan of explicitly accounting for nulls, but a null ConfigValue won't match either an IN nor a NOT IN. – Brian Stork Mar 31 '21 at 19:33
But a Null in not in () will prevent any row if there is any. If you have time the might have a look. https://stackoverflow.com/questions/66846703/sql-left-anti-semi-join-equivalent-query/66846745#66846745 – Kazi Mohammad Ali Nur Romel Mar 31 '21 at 19:48
If this answer helped you then please accept it so that the question can be marked as answered. – Kazi Mohammad Ali Nur Romel May 27 '21 at 18:12

score 0 · Answer 2 · answered Mar 31 '21 at 20:04

Not sure, but try this:

SELECT  * 
FROM        [dbo].[datamerge]   dm
LEFT JOIN   [dbo].[adf_Config]  ac  ON  ac.[ConfigValue]    =   dm.AdminType_Id
                                    AND ac.[ConfigKey]      =   'NonBillableEntries'
WHERE 
        dm.Timesheet_Id     =   'xxx' 
    AND ac.[ConfigValue]    IS  NULL

Select Statement is not returning desired results with where clause containing NOT IN operator

2 Answers2