Solved

Can you explain this SQL Query - SQL Server ?

Posted on 2011-09-29
9
701 Views
Last Modified: 2012-05-12
Hi,

I have Customer table. It has the following data:

CustomerId         Status
1                          Active
2                          Inactive
3                          Active
4                          NULL
5                          Dormant
6                          Dormant

I have written the following SQL Query to return all customers that do not have NULL or 'Dormant' for their status:

SELECT *
FROM Customer
WHERE Status NOT IN (NULL, 'Dormant')

I have got EMPTY result set. Can you please explain why ? I know the query is wrong.  I need to write in another way. But can you please explain the above SQL Query - why I am getting EMPTY result set ?

Thanks
0
Comment
Question by:milani_lucie
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 15

Assisted Solution

by:Eyal
Eyal earned 166 total points
ID: 36818020
SELECT *
FROM Customer
WHERE Status NOT IN ('Dormant')
and Status is not null
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36818025
Try it as:

SELECT *
FROM Customer
WHERE Status <> 'Dormant' AND Status IS NOT NULL

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36818030
The reason why the original did not work: whenever you compare any value to NULL, the result is ALWAYS false.
0
 

Author Comment

by:milani_lucie
ID: 36818116
matthewspatrick

1) whenever you compare any value to NULL

means

Status NOT IN (NULL)

2) the result is ALWAYS false

What does this mean ?

Thanks
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:milani_lucie
ID: 36818156
Got it.

Status NOT IN (NULL, 'Dormant')

returns FALSE. Am i correct ?

Thanks
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36818179
It means that any direct comparison of a field will eliminate NULLs; therefore, if you use:

SELECT *
FROM Customer
WHERE Status <> 'Dormant'

It will eliminate NULL rows also.
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 167 total points
ID: 36818219
In addition to this, if you do try to make a direct comparison to NULL it will result in FALSE and as such you will get an empty result set. That is what Patrick was indicating to you in his comment. Since NULL was in your IN statement, it is like doing this:

SELECT *
FROM Customer
WHERE Status <> NULL
;

The correct way to do that is:

SELECT *
FROM Customer
WHERE Status IS NOT NULL
;

However, if you are making another direct comparison, you do not need to mention NULL at all, which is why I made the suggestion I did:

SELECT *
FROM Customer
WHERE Status <> 'Dormant'
;
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36818493
What mwvisa1 said :)
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 167 total points
ID: 36818525
To further bend your mind a little...

Consider this condition:

WHERE ColA = ColB

Now, suppose that for a given row, both of those columns are null.  The condition will STILL return false, because using null in a logical comparison ALWAYS returns false, even when it boils down to "null = null".
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Detach & Attach 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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now