[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Can you explain this SQL Query - SQL Server ?

Posted on 2011-09-29
9
Medium Priority
?
761 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
[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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 15

Assisted Solution

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

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 93

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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
 

Author Comment

by:milani_lucie
ID: 36818156
Got it.

Status NOT IN (NULL, 'Dormant')

returns FALSE. Am i correct ?

Thanks
0
 
LVL 60

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 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 668 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 93

Expert Comment

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

Accepted Solution

by:
Patrick Matthews earned 668 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

656 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