Why do the rows with 0 disappear when I use having?-CodePudding

I have this tables:

create table series(
    serie       varchar(10),
    season          varchar(10),
    chapter     varchar(10),
    primary key ( serie, season, chapter)
);

insert into series values ('serie_1', 'season_1', 'Chap_1'),
                          ('serie_1', 'season_1', 'Chap_2'),              
              ('serie_1', 'season_2', 'Chap_1'),

              ('serie_2', 'season_1', 'Chap_1'),
              ('serie_2', 'season_2', 'Chap_1'),
              ('serie_2', 'season_2', 'Chap_2'),

              ('serie_3', 'season_1', 'Chap_1'),
              ('serie_3', 'season_2', 'Chap_1');

create table actua(
    idActor     varchar(10),
    serie       varchar(10),
    season      varchar(10),
    chapter     varchar(10),
    salary      numeric(6),
    foreign key ( serie, season, chapter) references series,
    primary key ( idActor, serie, season, chapter)
);

insert into actua values   ('A1', 'serie_1', 'season_1', 'Chap_1', 1000),
               ('A1', 'serie_1', 'season_1', 'Chap_2', 1000),
               ('A1', 'serie_1', 'season_2', 'Chap_1', 1000),                   
               ('A2', 'serie_1', 'season_2', 'Chap_1', 1000),       
               ('A3', 'serie_1', 'season_2', 'Chap_1', 1000),       

               ('A1', 'serie_2', 'season_1', 'Chap_1', 1000),
               ('A2', 'serie_2', 'season_1', 'Chap_1', 2000),       
               ('A2', 'serie_2', 'season_2', 'Chap_1', 2000),   
               
               ('A2', 'serie_3', 'season_1', 'Chap_1', 3000),
               ('A4', 'serie_3', 'season_1', 'Chap_1', 500)
               ;

I am trying to get the series and seasons in which the sum of the salaries is less than 4000, and if any season of a series has no associated actors, their sum will count as zero (serie_3, season_2)

My expected solution is:

|serie_1|season_1|2000|
|serie_1|season_2|3000|
|serie_2|season_1|3000|
|serie_2|season_2|2000|
|serie_3|season_1|3500|
|serie_3|season_2|   0|

First I tried to make a query to obtain the series, with their different seasons and the sum of the salaries in each one, besides substituting 0 in the row that has null:

select serie, season, coalesce(sum(salary), 0) 
from series natural left join actua group by serie, season order by serie, season

And I get my expected solution (because all the sums of the salaries are less than 4000) Then I try to show the rows with salary less than 4000, which is actually all the rows; using the having clause:

select serie, season, coalesce(sum(salary), 0) 
from series natural left join actua group by serie, season having sum(salary) < 4000 order by serie, season

And the row in which I used the coalesce disappears, how can I show it when using the having?

CodePudding user response：

That's because the sum of the NULL values that you get from the outer join is NULL, and NULL is not less than 4000:

SELECT CASE WHEN NULL < 4000
            THEN 'NULL is less than 4000'
            ELSE 'it is not true that NULL is less than 4000'
       END;
                    case                    
════════════════════════════════════════════
 it is not true that NULL is less than 4000

Actually, if you compare NULL to anything, the result is always NULL ("unknown"), which is not true.

So you have to use coalesce() in the HAVING clause as well:

... HAVING coalesce(sum(salary), 0) < 4000