Not equal to number does not include null SQL Server

Question

I have this SQL query in SQL Server for context.

SELECT 
    O.OrderID, SUM(P.Quantity) AS SprayGlueCans
FROM 
    FCMDB.dbo.FcoOrders O
JOIN 
    FCMDB.dbo.FcoStyleGroups SG ON SG.OrderID = O.OrderID
JOIN 
    FCMDB.dbo.FcoProducts P ON P.StyleGroupID = SG.StyleGroupID
WHERE
    P.CatalogProductID = 12715 
    AND O.Shipped = 1 
    AND MONTH(O.ShipDate) = 8 
    AND YEAR(O.ShipDate) = 2023 
    AND O.InstallerID <> 3 
    AND InstallerID <> 29
GROUP BY 
    O.OrderID

When I run it everything works great except for the fact that the InstallerID can be NULL. I expected this to not matter because. I was under the assumption that since NULL is not 3 or 29 then it would be included in the results. But this query seems to leave out everything with a NULL InstallerID. I was wondering why this is so.

Is this just how it works? If so am I supposed to be careful to not write queries that have this mistake again? And if that is true are there practices or design choices to best handle or avoid this situation?

*"I was under the assumption that since NULL is not 3 or 29 then it would be included in the results."* That is not how `NULL` works. `NULL` is an *unknown* value; it is neither equal nor *not* equal to anything. To compare to a `NULL` value you need to use `IS (NOT) NULL`. Alternatively, if you're on 2022 or Azure, you can use `IS DISTINCT FROM` when comparing to *scalar* values: `YourColumn IS DISTINCT FROM 3` will return rows where `YourColumn` does not have the value `3` *including* `NULL` values. — Thom A, Sep 01 '23 at 15:34
On a separate note, don't use syntax like `YEAR(O.ShipDate) = 2023` in your `WHERE`; this is *not* SARGable. Use explicit date ranges, such as `YourDate >= '20210101' AND YourDate < '20220101'`. — Thom A, Sep 01 '23 at 15:35
A comparison with a NULL returns UNKNOWN, which is neither true nor false. I think you want `((O.InstallerID <> 3 AND O.InstallerID <> 29) OR O.InstallerID IS NULL)` — Dan Guzman, Sep 01 '23 at 15:36

score 3 · Accepted Answer · answered Sep 01 '23 at 15:41

As I mention in the comments, NULL values don't work with equality operators. SomeColumn != 3 will return UNKNOWN when SomeColumn has a value of 3, which importantly isn't TRUE, so the row is omitted from the result set.

When comparing to NULLs you need to use IS NULL or IS DISTINCT FROM (the latter is only supported in Azure or on SQL Server 2022+). For example (SomeColumn != 3 OR SomeColumn IS NULL) and SomeColumn IS DISTINCT FROM 3.

For your query, this results in the following ((un)comment the solution appropriate for you):

USE FCMDB; --Connect to the correct DB, there's no need for 3 part naming here
GO

SELECT O.OrderID,
       SUM(P.Quantity) AS SprayGlueCans
FROM dbo.FcoOrders O
    JOIN dbo.FcoStyleGroups SG ON SG.OrderID = O.OrderID
    JOIN dbo.FcoProducts P ON P.StyleGroupID = SG.StyleGroupID
WHERE P.CatalogProductID = 12715
  AND O.Shipped = 1
  AND O.ShipDate >= '20230801'
  AND O.ShipDate < '20230901'
  AND (O.InstallerID NOT IN (3,29) OR O.InstallerID IS NULL)
  --AND O.InstallerID IS DISTINCT FROM 3 AND O.InstallerID IS DISTINCT FROM 29
GROUP BY O.OrderID;

You'll notice I switch to a NOT IN when using IS NULL and I also change your clauses against ShipDate to be SARGable. Again, as mentioned in the comments, don't uses clauses like YEAR(O.ShipDate) = 2023 in the WHERE, as indexes can't be used for such queries, which will result in poor(er) performance.

Thanks I did not know about the SARGable thing it is good to know. — Daniel Kelsch, Sep 01 '23 at 15:49
Very good explained. Another way beside `OR` would be `COALESCE` (but might be slower, I'm not sure). — Jonas Metzler, Sep 01 '23 at 16:42
It (likely) would be, @JonasMetzler , as that would also make the query non-SARGable. — Thom A, Sep 01 '23 at 16:52

Not equal to number does not include null SQL Server

1 Answers1