CASE WHEN НЕ В SELECT

Если вам хочется использовать результат логического выражения CASE WHEN в условии WHERE, вы не можете просто написать фильтр по колонке. Нужно написать или заново case when или набор условий в WHERE.

Возьмем наш предыдущий пример, допустим, мы не хотим выводить строки группы average

select
    long_name,
    club,
    case
        when age < 30 and overall >= 85 then 'Top' 
        when age < 30 and overall between 80 and 84 then 'Good'
        when age < 30 and overall between 75 and 79 then 'Average'
        when age < 30 and overall < 75 then 'Bad'
        when age >= 30 and overall >= 90 then 'Top' 
        when age >= 30 and overall between 85 and 89 then 'Good'
        when age >= 30 and overall between 80 and 84 then 'Average'
        else 'Bad'
    end rating_class
from
    fifa_players_2018

Вот так мы не можем написать:

select
    long_name,
    club,
    case
        when age < 30 and overall >= 85 then 'Top' 
        when age < 30 and overall between 80 and 84 then 'Good'
        when age < 30 and overall between 75 and 79 then 'Average'
        when age < 30 and overall < 75 then 'Bad'
        when age >= 30 and overall >= 90 then 'Top' 
        when age >= 30 and overall between 85 and 89 then 'Good'
        when age >= 30 and overall between 80 and 84 then 'Average'
        else 'Bad'
    end rating_class
from
    fifa_players_2018
where rating_class != 'Average'

Можем написать только так:

select
    long_name,
    club,
    case
        when age < 30 and overall >= 85 then 'Top'
        when age < 30 and overall between 80 and 84 then 'Good'
        when age < 30 and overall between 75 and 79 then 'Average'
        when age < 30 and overall < 75 then 'Bad'
        when age >= 30 and overall >= 90 then 'Top' 
        when age >= 30 and overall between 85 and 89 then 'Good'
        when age >= 30 and overall between 80 and 84 then 'Average'
        else 'Bad'
    end rating_class
from
    fifa_players_2018
where (
    case
        when age < 30 and overall between 75 and 79 then 'Average'
        when age >= 30 and overall between 80 and 84 then 'Average'
        else 'Not Average'
    end) = 'Not Average'

Можно вообще не использовать CASE WHEN в условии, а просто написать условие так, чтобы строки average не входили в результирующую таблицу. Но иногда переписать CASE WHEN в условии просто легче, чем пытаться перезаписать это в классическом выражении.