Solved

How do I include NULL in NOT IN clause?  SQL Sever

Posted on 2011-03-21
7
313 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:jtuttle99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 12

Expert Comment

by:enachemc
ID: 35183313
WHERE (SubcategoryID2 NOT IN ('STO','ORD') and SubcategoryID2 is not null ) AND (deleted <> 1)
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 500 total points
ID: 35183380
another way:

WHERE isNull(SubcategoryID2,'NULL') NOT IN ('STO','ORD','NULL') AND deleted <> 1
0
 

Author Comment

by:jtuttle99
ID: 35183408
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
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:jtuttle99
ID: 35183424
WHERE        (deleted <> 1) AND (ISNULL(SubcategoryID2, N'NULL') NOT IN ('STO', 'ORD'))

may work....
0
 
LVL 12

Expert Comment

by:enachemc
ID: 35183443
WHERE (SubcategoryID2 NOT IN ('STO','ORD', '') and SubcategoryID2 is not null ) AND (deleted <> 1)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35183474
another other way:

WHERE isNull(SubcategoryID2,'') NOT IN ('STO','ORD','') AND deleted <> 1
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35183507
you need this.
 WHERE (SubcategoryID2 NOT IN ('STO','ORD') 
         OR ISNULL(SubcategoryID2,'') = '') 
       AND deleted <> 1

Open in new window

0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question