My query is still returning rows with null values-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

I think it's down to your COALESCE. Try taking the gameType out of the mix. In theory, if your game type is set, then all your other fields after this could be null, as per my comment...

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

I think your approach could work using COALESCE, however @Anssss's answer may be more readable.

Just on the same track - should your ID fields also be in this list? Won't they be given a default number?