Help with Not Equal SQL Statement

I have the following code as part of one of my sql statements. I'm trying to account for all values in the field that are not equal to Yes, including Null values. For some reason, if the field has <NULL> in it, the statement doesn't work. If the field has 'No' or some other value besides 'Yes' it works fine. Do I need to change something to find Nulls as well? I was under the impression that <> 'Yes' would work. Please advise. Thanks!

......
AND [Has__bAPI__bMatch__bRun__Q]<> 'Yes'
isaacr25Asked:
Who is Participating?
 
JimBrandleyConnect With a Mentor Commented:
Like this?
UPDATE t1
  SET API__bNumber = t2.[EMP ID]
FROM MASTER55 t1
JOIN [API].LaborWorkx_Live.dbo.vwCGHS_All_EmpList t2
  ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1)
 AND t1.L__d__bName = t2.[Last Name]
 AND t1.mrREF_TO_MR Is null
AND ( t1.Has__bAPI__bMatch__bRun__Q <> 'Yes' OR t1.Has__bAPI__bMatch__bRun__Q IS NULL)

Jim
0
 
JimBrandleyCommented:
Change it to:
AND ( [Has__bAPI__bMatch__bRun__Q]<> 'Yes' OR Has__bAPI__bMatch__bRun__Q IS NULL)

Jim
0
 
isaacr25Author Commented:
I thought about that, but take a look at the entire statement:

UPDATE t1
  SET API__bNumber = t2.[EMP ID]
FROM MASTER55 t1
JOIN [API].LaborWorkx_Live.dbo.vwCGHS_All_EmpList t2
  ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1)
 AND t1.L__d__bName = t2.[Last Name]
 AND t1.mrREF_TO_MR Is null

Is there anyway to group the two parts to your suggestion so that its not interpreted as --all of these previous statments--- OR Has__bAPI__bMatch__bRun__Q IS NULL)?

Does this make sense? Thanks.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Scott PletcherConnect With a Mentor Senior DBACommented:
>> I was under the impression that <> 'Yes' would work. Please advise. <<

SQL treats NULL as "Unknown" (essentially).  Futhermore, SQL's rule is that *any* comparison to an Unknown (NULL) yields Unknown (NULL).  That is the thing to keep in mind when dealing with NULL.

So, NULL <> 'Yes' yields NULL.  The clauses in a WHERE condition must all be *true* (not just "not false") in order for a row to be selected.  Since NULL/"Unknown" is not "true", SQL does not SELECT the row.

SQL also does not use any "extended logic" to handle things that would be obvious to humans.  For example:
WHERE ColA = 5 OR ColA <> 5
If ColA is NULL, it will *not* be selected, even though for any unknown value it must either be = 5 or <> 5.  SQL doesn't consider that (which, admittedly, could get very complex to do), but evaluates each expression separately, so WHERE Unknown OR Unknown yields Unknown (NULL), which is not true, so SQL doesn't SELECT the row.
0
 
mrihmConnect With a Mentor Commented:
Another way to account for NULL values is to use the COALESCE function which returns the first non-null value in the supplied list:

AND COALESCE(Has__bAPI__bMatch__bRun__Q, '') <> 'Yes'

The Has__bAPI__bMatch__bRun__Q value will be replaced with an empty string if the Has__bAPI__bMatch__bRun__Q value equals NULL and you won't have any problems comparing it to 'Yes'.

The reason that your statement does not work with NULL is that the database engine does not know what NULL represents and does not make any assumptions (i.e. the database does not assume that NULL is not 'YES'). That being said, the database engine takes the most conservative rout and simply returns false when comparing a NULL value to 'Yes' and therefore any Has__bAPI__bMatch__bRun__Q with a NULL value will be excluded.
0
 
JimBrandleyCommented:
I am a bit confused. I do not see a relationship between this:
AND ( [Has__bAPI__bMatch__bRun__Q]<> 'Yes' OR Has__bAPI__bMatch__bRun__Q IS NULL)

and this:
UPDATE t1
  SET API__bNumber = t2.[EMP ID]
FROM MASTER55 t1
JOIN [API].LaborWorkx_Live.dbo.vwCGHS_All_EmpList t2
  ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1)
 AND t1.L__d__bName = t2.[Last Name]
 AND t1.mrREF_TO_MR Is null

Do you want to add that predicate to the join criteria? If so, to which table does Has__bAPI__bMatch__bRun__Q belong?

Jim
0
 
isaacr25Author Commented:
Hi Jim,
     Yes, I want to add the statement to the end of the larger statement. This field belongs to MASTER55.
0
 
Scott PletcherSenior DBACommented:
>> That being said, the database engine takes the most conservative rout and simply returns false when comparing a NULL value to 'Yes' <<

That is simply not true.  If it returned "False", then this would return "True":
WHERE NOT (columnA <> 'Yes')
when columnA is NULL, but it is not.  Instead, it also returns "NULL" (unknown), which is also not true for the purposes of a WHERE clause.
0
 
isaacr25Author Commented:
As simple as parentheses... imagine that!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.