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

what is `1` represents, can you add your table schema? – flyingfox Jul 09 '21 at 01:50 — flyingfox, Jul 09 '21 at 01:50
Group by 1 is same as Group by SIS. I will add the schema – Jonathan Lam Jul 09 '21 at 01:52 — Jonathan Lam, Jul 09 '21 at 01:52

score 2 · Answer 1 · answered Jul 09 '21 at 02:01

2

You should use this:

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

Why does your WHERE clause removes null values from the result: NULL value means the absence of value or value is unknown, so that you can't compare NULL value using scalar value operator, <> 'EQ' will return unknown or not true.

You could refer more post about NULL value in sql in sof, eg this link or search in gg

answered Jul 09 '21 at 02:01

Pham X. Bach

5,284
4
28
42

2

I usually use COALESCE() – Jeff Dege Jul 09 '21 at 02:13
@JeffDege do you mean using `COALESCE(TRIM(sis), ' ') = ' '` instead of `TRIM(sis) IS NULL`? That is just another style of code, and all would return right result. – Pham X. Bach Jul 09 '21 at 02:17
2

`WHERE COALESCE(UPPER(TRIM(SIS)),'x') <> 'EQ'` would replace the whole line, less code but arguably less easy to understand the intention of the code at a glance – FLSte Jul 09 '21 at 02:24
COALESCE(field, '') <> '' is only difficult to understand the first time you see it. It's a common idiom. – Jeff Dege Jul 09 '21 at 04:58
3

@JeffDege: The optimizer will not be able to use collected statistics on a COALESCE resulting in a (probaby totally) wrong estimate and *no confidence*. No problem for a simple query like this, but maybe for a complex one with ots of steps. – dnoeth Jul 09 '21 at 09:04

score 2 · Accepted Answer · answered Jul 09 '21 at 02:02

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;

Why does WHERE clause removes null values from the result?

2 Answers2