asked on

SQL Not Equal To not working as expected

Hi
I have a table called [Descriptions] with a column called [Other]. Half of the records on this table
have the word "Stope" in. The following SQL shows all these records

Select * From Descriptions Where Shaft = 'masimong air' And Other = 'Stope'

Open in new window

but this SQL query does not show the other 20 records that do not have the word "Stope" in.

Select * From Descriptions Where Shaft = 'masimong air' And Other <> 'Stope'

Open in new window

Why does the second query not work properly?

Vitor Montalvão

Maybe because of the Shaft filter?
What the following returns?

Select * From Descriptions Where Other <> 'Stope'

Open in new window

Murray Brown

ASKER

Thanks. That shows all the other records that are not 'masimong air' shaft but still no records that are 'masimong shaft' but not Other = 'Stope'

SOLUTION

Vitor Montalvão

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

PortletPaul

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Jim Horn

>NOTE: Providing sample data will help to provide you a faster and assertive answer.
Amen brother...

Murray Brown

ASKER

Hi. Thank you all. There was something strange happening because I populated the blank items with code from an Excel spreadsheet. Putting some info back in solved the problem so thanks for the help.

Vitor Montalvão

I populated the blank items

So, I think Paul's comment says something about the NULL values. You didn't provide sample data but Paul guessed that you may had those blank items.

PortletPaul

Thanks Vitor. "NOT EQUAL" does not detect NULLs (because first you must try to learn if it is equal, which you cannot do)

Here is a tiny example:

declare @Descriptions table
    (Shaft varchar(40), Other varchar(40))
;
    
INSERT INTO @Descriptions 
    (Shaft, Other)
VALUES
    ('masimong air', 'Stope'),
    ('liviero air', 'Not That'),
    ('liviero air', NULL)
;

select * from @Descriptions
where other <> 'Stope'
;
+----+-------------+----------+
|    |    Shaft    |  Other   |
+----+-------------+----------+
|  1 | liviero air | Not That |
+----+-------------+----------+

select * from @Descriptions
where ( other <> 'Stope' OR Other IS NULL)
;
+---+-------------+----------+
|   |    Shaft    |  Other   |
+---+-------------+----------+
| 1 | liviero air | Not That |
| 2 | liviero air | NULL     |
+---+-------------+----------+

Open in new window

This is not wrong, it complies with the SQL standard. i.e. it is "expected behaviour"

Murray Brown

ASKER

Yes Vitor: Paul's advice helped me too. Thanks both again. I learned something really important here

Murray Brown

ASKER

Thanks