CODEWITHSUNDEEP
sqlsql-servernull
IrLurker
IrLurker

Reputation: 3

Returning null and other values. SQL

Just started to fiddle in SQL and really dont have any experience with coding before but it's fun. Here is the issue im having.

WHERE ([Text 7] collate SQL_Latin1_General_CP1_CI_AS like '%perm%' 
   OR Article.Stat2 IN ('70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '83'))
      AND NOT Articletype ='z'

In the column Articletype there is multiple values, z,q,s,/ and NULL. If I remove z it doesnt return the NULL values any longer

How do i get it to return NULL values and the others but not z without it conflicting my orignal selection of articles.

Upvotes: 0

Views: 84

Answers (2)

Thom A
Thom A

Reputation: 95924

NULL does not equal anything, but conversely it does not NOT equal anything either. NULL is an unknown value. The only way you can compare to a NULL value is with IS NULL and IS NOT NULL.

With an expression like YourColumn = 'z' if YourColumn has the value NULL then the result of that expression is not False, it's Unknown. For the purposes of the WHERE here it doesn't "matter", as Unknown isn't True, and so the row it filtered out.

For NOT(YourColumn = 'z'), however, this evaluates to NOT(Unknown) which is also Unknown; which is (importantly) not True.

Therefore, if you are checking for NULL values, you need to use IS NULL and IS NOT NULL.

In this case that would be the below:

WHERE ([Text 7] COLLATE SQL_Latin1_General_CP1_CI_AS like '%perm%' OR Article.Stat2 IN ('70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '83'))
  AND (Articletype != 'z' OR Articletype IS NULL)

Couple of extra notes:
Do you really need the COLLATE here? This will cause the query to be non_SARGable. What is the COLLATE achieving here, to change it to a non case-sensitive collation? If so, you might be better off with an indexed PERSISTED computed column.

Also, don't provide numbers in single quotes; they don't need them. Your IN should be Stat2 IN (70,71,...,83).

Upvotes: 0

Peter B
Peter B

Reputation: 24280

You can extend the check as follows:

AND (Articletype IS NULL OR Articletype <> 'z')

PS I switched to using <> because I believe that is more readable than NOT ... =.

Upvotes: 0

Related Questions