CASE vs OR in SQL filters

user_7287543 0
Viewed 320

I'm going through a DataCamp course on SQL. This course examines the European soccer database.

Part of the course discusses using CASE as a filter, so that you are not left with a chunk of NULL values. The following code is offered to filter specifically Chelsea home and road victories:

SELECT date, season,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal
            THEN 'Chelsea home win!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea away win!' END AS outcome
FROM match
WHERE CASE WHEN hometeam_id = 8455 AND home_goal > away_goal
                THEN 'Chelsea home win!'
           WHEN awayteam_id = 8455 AND home_goal < away_goal
                THEN 'Chelsea away win!' END IS NOT NULL;

Looking at this code, there seems to be some superfluity. The CASE in the filter, for example, defines categories a second time, even though it was done during the SELECT clause. It seems to me that it would be more advantageous to simply use AND and OR in the filter:

SELECT date, season,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal
            THEN 'Chelsea home win!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea away win!' END AS outcome
FROM match
WHERE (hometeam_id = 8455 AND home_goal > away_goal)
   OR (awayteam_id = 8455 AND home_goal < away_goal);

So a couple of questions.

  1. Is there a particular reason for using the CASE clause like this? Or is the DataCamp course just using the CASE in a filter in a teachable way, and this partcular case is not necessarily a practical use?
  2. What does assigning categories in the filtering CASE clause do, as seen in the first query?
  3. If we are filtering on the CASE clause, then why not simply end with WHERE outcome IS NOT NULL?

Again, I understand if it's entirely just to teach a principle--the first question still stands, in that case--but assuming there's a deeper, more complex reason behind all this verbosity, what is it?

filter sqlite sql
2 Answers

You are correct, it should be a where clause as you've written. where only needs a boolean.

I cannot think of a reason one would use that as a teaching example.

It would make slightly more sense like so:

WHERE CASE
      WHEN hometeam_id = 8455 AND home_goal > away_goal THEN
        true
      WHEN awayteam_id = 8455 AND home_goal < away_goal THEN
        true
      ELSE
        false
      END

At least there's no superfluous strings, and the else case is more obvious, but that is still a highly unusual way to write a where clause.

If we are filtering on the CASE clause, then why not simply end with WHERE outcome IS NOT NULL?

Most databases do not support using columns aliases like outcome in a where clause. SQLite does, but I'd advise against getting used to non-standard features.

user_146600

In a WHERE clause, you generally want to avoid CASE expressions. The reason is simple: the CASE expression enforces a particular order of evaluation. And enforcing the evaluation order affects the ability of the optimizer to produce the best query plan.

I would say this is a very poor example if the solution insists on using CASE. Although there are some situations where CASE expressions are helpful in the WHERE clause, the one you describe is not one of them.

user_11440350
Related