My query is still returning rows with null values

Question

I am trying to write a query that only returns rows where a group of columns does not have any nulls.

I don't want any rows returns if the result has a null in any of these columns:

gameTitle, gameDevTeam, gameType, innerId/outerId, prodCode, teamCode

So I did some searching, and found this question:

Optimize way of Null checking for multiple columns

When I tried to apply that logic to my query, I am still seeing results where one or more of the columns are NULL.

Here is my query:

SELECT  *
FROM  GameData gd
WHERE gd.dev_Status = 002
  AND COALESCE(gd.gameTitle, gd.gameDevTeam, gd.gameType, COALESCE(gd.innerId, gd.outerId), gd.prodCode, gd.teamCode) IS NOT NULL
  AND gd.gameType IN(003, 004)

Is there anything I am doing wrong?

Why have you put the IDs inside a `COALESCE`? Surely you can just include one value after the other in the list? (Always follow KISS!) — Paul, Sep 15 '22 at 14:58
@Paul I'm sorry, that is a good point. I will remove that and see if it helps. Thanks! — SkyeBoniwell, Sep 15 '22 at 15:00
In theory, if your `COALESCE` gets past `gd.gametype`, your next criteria would fail anyway. Try taking that out of your list. — Paul, Sep 15 '22 at 15:03

score 2 · Answer 1 · answered Sep 15 '22 at 15:03

That link you specified shows a solution that is checking if any of the columns is not null. It sounds like you are trying to check if all of the columns are not null. If that is correct, then you could just do:

AND gd.gameTitle IS NOT NULL
AND gd.gameDevTeam IS NOT NULL
AND gd.innerId IS NOT NULL
...repeat for every column you care about

score 2 · Accepted Answer · answered Sep 15 '22 at 15:09

2

Logically you are asking for the opposite of any column being NULL so the following pattern should hopefully work for you:

select * 
from t
where not( col1 is null or col2 is null or col3 is null or ...);

answered Sep 15 '22 at 15:09

Stu

30,392
6
14
33

When you put the `not` outside the parentheses, but test for null inside, does that mean you are saying where every column in this group is not null? Thanks! – SkyeBoniwell Sep 15 '22 at 15:25
1

Yes, you will get only rows where all columns in the parenthesis are not null, ie, have a value - that's what I interpereted your requirement to mean. – Stu Sep 15 '22 at 15:26

score 1 · Answer 3 · answered Sep 15 '22 at 15:27

You can set CONCAT_NULL_YIELDS_NULL to ON and then just add the columns as text.
If any of the columns are NULL then the sum of them will also be NULL.

SET CONCAT_NULL_YIELDS_NULL ON

select * from (values 
    ('gameTitle1', 'gameDevTeam1', 'gameType', 1, 2, 'prodCode1', 'teamCode1'),
    ('gameTitle2', 'gameDevTeam2', null, 1, 2, 'prodCode2', 'teamCode2'),
    ('gameTitle3', 'gameDevTeam3', 'gameType', null, 2, 'prodCode3', 'teamCode3')
)t(gameTitle, gameDevTeam, gameType, innerId, outerId, prodCode, teamCode)
WHERE 
    (gameTitle + gameDevTeam + gameType + cast(innerId as varchar(50)) + cast(outerId as varchar(50)) + prodCode + teamCode) is not null

Regarding my answer, you were right - I was too focused on making COALESCE work before, to the detriment of what should have been the answer. — Paul, Sep 21 '22 at 10:44

My query is still returning rows with null values

3 Answers3