We help IT Professionals succeed at work.

SQL Not Equal To not working as expected

Murray Brown
Murray Brown asked
on
760 Views
Last Modified: 2017-05-26
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?
Comment
Watch Question

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Maybe because of the Shaft filter?
What the following returns?
Select * From Descriptions Where Other <> 'Stope'

Open in new window

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
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'
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>NOTE: Providing sample data will help to provide you a faster and assertive answer.
Amen brother...
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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 BrownASP.net/VBA/VSTO Developer

Author

Commented:
Yes Vitor: Paul's advice helped me too. Thanks both again. I learned something really important here
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.