Not Equal Expression with Nulls in field

[MajP]

Finally getting around to teaching myself SSMS. I had a query with the WHERE clause

FamilyCategoryID_FK <> 18

In Access this would return all records including those that are NULL.
In SS it did not include the NULL records. I am assuming that is just a difference.
What is the normal why to handle this? I did a couple things that worked, but not sure if there is a standard way to do this.

FamilyCategoryID_FK <> 18 or FamilyCategoryID_FK is null
and
isnull(FamilyCategoryID_FK,0) <> 18

Thanks.

 

[Isaac]

Finally getting around to teaching myself SSMS. I had a query with the WHERE clause

FamilyCategoryID_FK <> 18

In Access this would return all records including those that are NULL.
In SS it did not include the NULL records. I am assuming that is just a difference.
What is the normal why to handle this? I did a couple things that worked, but not sure if there is a standard way to do this.

FamilyCategoryID_FK <> 18 or FamilyCategoryID_FK is null
and
isnull(FamilyCategoryID_FK,0) <> 18

Thanks.

I just use the way that you did it ... test for it either being not equal to 18 or null.

You can also wrap the column in the isnull expression, but that would be bad for optimization.

Access is weird.

 

[Minty]

In SQL Server, I think the preferred method is to use the Exists or Not Exists predicates?
That will return nulls but would involve a subquery. Eg. something like

SELECT * FROM YourTable
WHERE NOT EXISTS ( SELECT * FROM YourTable WHERE FamilyCategoryID_FK = 18)

Either way, semantically, I personally think SQL is right and Access is wrong, but you need to remember the differences.
(Caveat - shooting from the hip a bit here, as My SSMS is doing massive updates and taking ages, so untested.)

 

[WayneRyan]

MajP,
On iPhone so ...

Some databases will implicitly convert Nulls.
Null can = '' -- empty string

SQL Server won't do that.

Use Coalesce:

Where Coalese(ID, -99) <> 18 -- -99 isn't in YOUR data

Hth,
Wayne

 

[Isaac]

In SQL Server, I think the preferred method is to use the Exists or Not Exists predicates?
That will return nulls but would involve a subquery. Eg. something like

SELECT * FROM YourTable
WHERE NOT EXISTS ( SELECT * FROM YourTable WHERE FamilyCategoryID_FK = 18)

Either way, semantically, I personally think SQL is right and Access is wrong, but you need to remember the differences.
(Caveat - shooting from the hip a bit here, as My SSMS is doing massive updates and taking ages, so untested.)

Wow! .. I've never heard of doing that for a simple "if not equals 18 or is null".....
Plus, it wouldn't even work unless you correlated the subquery with the outer query - but why in the world would you want to use a correlated subquery (that scans for every single record!!) instead of just typing out "= 18 or [] is null"

I'm sorry but that is not good advice--I'm going to assume either you were joking or you just spead-read the question and didn't mean to advise as such.

 

[Isaac]

MajP,
On iPhone so ...

Some databases will implicitly convert Nulls.
Null can = '' -- empty string

SQL Server won't do that.

Use Coalesce:

Where Coalese(ID, -99) <> 18 -- -99 isn't in YOUR data

Hth,
Wayne

Same as using IsNull, as I originally mentioned, NO - you do NOT want to include expressions in your Where clause unecessarily. That results in unnecessary processing on the table.

Just type out the extra or statement.

Sorry guys, but suggesting using a correlated subquery or an expression like isnull or coalesce in a where clause instead of typing out "where []<>18 or [] is null" is really bad advice, and I couldn't help point out.
Peace.

 

[oleronesoftwares]

the syntax you used is approved in ms sql, i.e

"
FamilyCategoryID_FK <> 18 or FamilyCategoryID_FK is null
and
isnull(FamilyCategoryID_FK,0) <> 18"

There are some variations between the syntax used across the SQL engines

Access runs on MSDE which is a light version of MS SQL and differs a bit.

Both are good and correct in their own right

same thing you will find variations between
MS SQL, Mysql, Postgres,etc

 

[The_Doc_Man]

It has been a while since I looked at this in depth, but we have to remember that when a given vendor says "compliant with ANSI-92 SQL" there are loopholes. The loopholes come about when the printed standard says "subject to vendor implementation" as part of the definition of behavior for the given syntax. The exact behavior in expressions involving nulls is, unfortunately, one of those actions subject to vendor implementation.

When you say "Access is wrong and SQL Server is right" - beware of the "implementation" loophole. If comparing to the standards, it is possible for both of the implementations to be technically correct simply because the standard backs away from that case.

 

[Pat Hartman]

In Access this would return all records including those that are NULL.

No. Any expression where one or more operands is Null will return Null. So, it will effectively be false.

print IIf( 1 = Null, "a", "b")
b
print IIf( 1 <> Null, "a", "b")
b

When one of the operands could be null, you have to specifically account for that by using Is Null or IsNull() to put them specifically on one side of the condition or the other.

 

[strive4peace]

hi @MajP

adding on ... you'll probably be using Coalesce a lot! It's different from NZ in that you can have multiple expressions after the test, and it'll return the first one that doesn't evaluate to Null. A literal value,of course, won't ever be null

https://docs.microsoft.com/en-us/sq...s/coalesce-transact-sql?view=sql-server-ver15