My query is still returning rows with null values

SkyeBoniwell 0
Viewed 54

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?

sql-server-2012 tsql sql-server
3 Answers

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
Anssssss0

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 ...);
userqKk210

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
wnutt0
Related