Solved

Can you explain this SQL Query - SQL Server ?

Posted on 2011-09-29
9
734 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
listing SQL login names of valid databases 2 20
sql server query 18 34
job schedule 8 16
SQL Recursion 6 16
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

808 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