Trying to understand why IS NOT NULL is necessary at end of query and case statement in SQL

Question

I'm working on a query in DataCamp SQL and I don't understand the ending to this query:

SELECT 
    season,
    date,
    home_goal,
    away_goal
FROM matches_italy
WHERE 
-- Exclude games not won by Bologna
    CASE WHEN hometeam_id = 9857 AND home_goal > away_goal THEN 'Bologna Win'
        WHEN awayteam_id = 9857 AND away_goal > home_goal THEN 'Bologna Win' 
        *END IS NOT NULL;*

It's the very last line I don't get.

MY thoughts would be to not have anything written after END, because obviously you wouldn't want to select any NULLS, because you are specfiying the only data you will accept, right there in the WHERE clause, right? So I would think it would end just as:

...
END;

Otherwise, if anything is to be put there, wouldn't it be something like:

...
AND IS NOT NULL;

?

But I still think in my mind you shouldn't have to respecify that you don't want something other than what is already there in the case statement.

Many thanks!!

Answer 1

Because this is in the WHERE statement, there needs to be a comparison happening. The comparisons between CASE and END are simply deciding what value to return, but that value must be compared to something.

So this syntax is saying, WHERE {{ what case statement returns }} IS NOT NULL

The reason this is confusing, is because you could essentially eliminate the CASE portion and write the WHERE clause yourself, which would be easier to understand, imho. Contributing to the confusion is the CASE portion not explicitly returning NULL for any condition, because it is relying on the fact that there is no ELSE in order to return a NULL. Thats a lot of mental energy to figure out what that WHERE clause is doing.

WHERE (hometeam_id = 9857 AND home_goal > away_goal)
OR (awayteam_id = 9857 AND away_goal > home_goal)