Solved

Can you explain this SQL Query - SQL Server ?

Posted on 2011-09-29
9
715 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

895 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

15 Experts available now in Live!

Get 1:1 Help Now