jtuttle99
asked on
How do I include NULL in NOT IN clause? SQL Sever
I'm trying to retrieve Null value along with my specified value.
SubcategoryID2 has 4 values, 'STO', 'ORD', ' ', NULL
I would always like to apply this condition (deleted <>0)
If I mix up AND and OR condition, I get wrong results.
What didn't work
- WHERE (SubcategoryID2 NOT IN ('STO','ORD')) AND (deleted <> 1)
-----results ------ SubcategoryID2 doesn't include NULL
- WHERE (SubcategoryID2 NOT IN ('STO','ORD')) AND (deleted <> 1) OR (SubcategoryID2 IS NULL)
-----results ------ SubcategoryID2 has correct results, but deleted column also includes value of 1
How do I write this condition? What I need to do here is
1, deleted<>1 always applies
2, I would like to include NULL value in results.
here is my current SQL syntax
SELECT ImageID, Priority, SubcategoryID2
FROM Items
WHERE (SubcategoryID2 NOT IN ('STO', 'ORD')) AND (deleted <> 1)
ORDER BY Priority
Thank you
SubcategoryID2 has 4 values, 'STO', 'ORD', ' ', NULL
I would always like to apply this condition (deleted <>0)
If I mix up AND and OR condition, I get wrong results.
What didn't work
- WHERE (SubcategoryID2 NOT IN ('STO','ORD')) AND (deleted <> 1)
-----results ------ SubcategoryID2 doesn't include NULL
- WHERE (SubcategoryID2 NOT IN ('STO','ORD')) AND (deleted <> 1) OR (SubcategoryID2 IS NULL)
-----results ------ SubcategoryID2 has correct results, but deleted column also includes value of 1
How do I write this condition? What I need to do here is
1, deleted<>1 always applies
2, I would like to include NULL value in results.
here is my current SQL syntax
SELECT ImageID, Priority, SubcategoryID2
FROM Items
WHERE (SubcategoryID2 NOT IN ('STO', 'ORD')) AND (deleted <> 1)
ORDER BY Priority
Thank you
enachemc
WHERE (SubcategoryID2 NOT IN ('STO','ORD') and SubcategoryID2 is not null ) AND (deleted <> 1)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sorry, I didn't explain right.
I would like to treat ' ' and NULL same.
So, If I specify NOT IN ('STO', 'ORD'), I would like to get results ' ' AND NULL
I would like to treat ' ' and NULL same.
So, If I specify NOT IN ('STO', 'ORD'), I would like to get results ' ' AND NULL
ASKER
WHERE (deleted <> 1) AND (ISNULL(SubcategoryID2, N'NULL') NOT IN ('STO', 'ORD'))
may work....
may work....
WHERE (SubcategoryID2 NOT IN ('STO','ORD', '') and SubcategoryID2 is not null ) AND (deleted <> 1)
another other way:
WHERE isNull(SubcategoryID2,'') NOT IN ('STO','ORD','') AND deleted <> 1
WHERE isNull(SubcategoryID2,'') NOT IN ('STO','ORD','') AND deleted <> 1
you need this.
WHERE (SubcategoryID2 NOT IN ('STO','ORD')
OR ISNULL(SubcategoryID2,'') = '')
AND deleted <> 1