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
Who is Participating?
knightEknightConnect With a Mentor Commented:
another way:

WHERE isNull(SubcategoryID2,'NULL') NOT IN ('STO','ORD','NULL') AND deleted <> 1
WHERE (SubcategoryID2 NOT IN ('STO','ORD') and SubcategoryID2 is not null ) AND (deleted <> 1)
jtuttle99Author Commented:
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

jtuttle99Author Commented:
WHERE        (deleted <> 1) AND (ISNULL(SubcategoryID2, N'NULL') NOT IN ('STO', 'ORD'))

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
SharathData EngineerCommented:
you need this.
 WHERE (SubcategoryID2 NOT IN ('STO','ORD') 
         OR ISNULL(SubcategoryID2,'') = '') 
       AND deleted <> 1

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.