?
Solved

Can you explain this SQL Query - SQL Server ?

Posted on 2011-09-29
9
Medium Priority
?
764 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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.
Suggested Courses

807 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