asked on

Can you explain this SQL Query - SQL Server ?

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

SOLUTION

Eyal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

Try it as:

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

Open in new window

Patrick Matthews

The reason why the original did not work: whenever you compare any value to NULL, the result is ALWAYS false.

milani_lucie

ASKER

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

milani_lucie

ASKER

Got it.

Status NOT IN (NULL, 'Dormant')

returns FALSE. Am i correct ?

Thanks

Kevin Cross

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.

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

What mwvisa1 said :)

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial