Not equal to number does not include null SQL Server

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?

>Solution :

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.