Not in (values) for 2 different columns ignoring NULL value

Question

I have a set of values (IDs) and i want to use "not in" in my query

if i use

select ...
where ColumnA not in (1234, ...,...,...,..more than 3000 values -IDs)

it works but... but returns resultset of row 2.

I have also a second Column B and want to look the query also in this Column

ColumnA  ColumnB

1234     NULL
NULL     1234

I want that the "not in" looks in both columns but i believe the NULL value make my query not working

I want the result to say ...no row returned

the OR clause doesn't work:

select ...
where ColumnA not in (1234) or ColumnB not in (1234)

gives back row1 and 2.

Did you try EXISTS? msdn.microsoft.com/en-us/library/ms188336.aspx — Evaldas Buinauskas
– Evaldas Buinauskas, Commented Feb 18, 2015 at 18:44
I think if you change "OR" to "AND" you will get the desired results — Tab Alleman
– Tab Alleman, Commented Feb 18, 2015 at 19:16

animateme · Accepted Answer · 2015-02-18 19:13:25Z

1

Try something like this:

select ColumnA, ColumnB
from SomeDatabase
where ISNULL(ColumnA, 0) not in (1234)
and ISNULL(ColumnB, 0) not in (1234);

answered Feb 18, 2015 at 19:13

animateme

3091 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2015-02-18 19:27:00Z

1

I would suggest:

select t.*
from table t
where 1234 not in (coalesce(columnA, -1), coalesce(columnB, -1));

This allows you to include the constant value only once in the query.

answered Feb 18, 2015 at 19:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

sT3v3n Over a year ago

sorry i not only have to look for '1234' i have maybe 3000 IDs

abatishchev · Accepted Answer · 2015-02-18 20:10:36Z

1

declare @ids table
{
    id int
}

insert into @ids (id)
values
(1),
(2),
(3),
(4),
...

select *
from table
where
    ColumnA is not null and not in (select id from @ids)
and ColumnB is not null and not in (select id from @ids)

edited Feb 18, 2015 at 20:10

answered Feb 18, 2015 at 19:50

abatishchev

101k88 gold badges304 silver badges443 bronze badges

12 Comments

sT3v3n Over a year ago

i would prefer something without needing permission to write into database

sT3v3n Over a year ago

and just put my 'IDs' somewhere between this ()

abatishchev Over a year ago

@sT3v3n: it isn't writing to teh db, it's a variable of type table.

abatishchev Over a year ago

@sT3v3n: it may and probably will cause 2 reads of the same data. Until you cache it or join.

sT3v3n Over a year ago

sorry for my non pro sql behavior! so i should write my IDs inside...{ id int } ???

|

Collectives™ on Stack Overflow

Not in (values) for 2 different columns ignoring NULL value

3 Answers 3

Comments

1 Comment

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related