Why does WHERE clause removes null values from the result?

Question

I have a table I want to filter data from. I tried the following query

SELECT 
SIS, COUNT(*)
FROM DL_SQ_DEV_INT.SMRY_DAILY_TRAILER_REPORT
GROUP BY 1;

Result:
BL,17386
EQ,3242
FIFO,5747
GR,15655
HOLD,13035
LT BL,20566
LT GR,14615
LT OR,14190
LT PU,13877
LT YE,13683
null,223376
OR,15727
PI,3563
PU,16105
RW,200
TA,6
tbd,25302
WH,1945
YE,14510

Now when I add a WHERE clause in it, it filters out the null values. The query is a not equal to (<>). How can I avoid that and still have the null values in my result? Changing null to blank or space?

SELECT 
SIS, COUNT(*)
FROM DL_SQ_DEV_INT.SMRY_DAILY_TRAILER_REPORT
WHERE UPPER(TRIM(SIS)) <> 'EQ'
GROUP BY 1;

Result:
BL,17386
FIFO,5747
GR,15655
HOLD,13035
LT BL,20566
LT GR,14615
LT OR,14190
LT PU,13877
LT YE,13683
OR,15727
PI,3563
PU,16105
RW,200
TA,6
tbd,25302
WH,1945
YE,14510

Answer 1

Neither "not equal" nor "equal" will select a value that is NULL.

SQL uses "three-way logic" where an expression can be true or false or unknown. NULL is the absence of any value at all so it cannot be equal to something, and if it cannot be equal to a compared value it also cannot be "not equal", instead it is unknown.

To overcome this you need to treat NULL explicitly in your where clause, to include NULLs use OR SIS IS NULL

SELECT 
SIS, COUNT(*)
FROM DL_SQ_DEV_INT.SMRY_DAILY_TRAILER_REPORT
WHERE UPPER(TRIM(SIS)) <> 'EQ' OR SIS IS NULL
GROUP BY 1;