SQL 'IS NOT NULL' fails to filter rows and return an emtpy set-CodePudding

Obviously the title is provocative, but this is where I'm stuck.

I have an SQL query with a JOIN that returns a single row (even though the result set is empty) because of the count(r.sid) function on the column 'sid' of the joint table. I wanted to filter it out checking that the primary key c.cid is not NULL but I get a non empty set anyway....(removing count(r.sid) makes the query return an empty set as expected)

MariaDB [***]> select count(r.sid),c.code,c.created,c.cid from conference as c left join registrations as r on r.cid=c.cid where c.code = 'not_existing_code' and c.code is NOT NULL;

 -------------- ------ --------- ----- 
| count(r.sid) | code | created | cid |
 -------------- ------ --------- ----- 
|            0 | NULL | NULL    | NULL|
 -------------- ------ --------- ----- 
1 row in set (0.001 sec)

-- M

CodePudding user response：

Try this

select COUNT(r.sid)  ,c.code,c.created,c.cid
from conference as c 
left join registrations as r on r.cid=c.cid 
where c.code = 'not_existing_code' 
and c.code is NOT NULL
Group by c.code,c.created,c.cid
Having COUNT(case when r.sid IS NOT NULL THEN r.sid END)>=0

CodePudding user response：

Your GROUP BY is incorrect (if you enable ONLY_FULL_GROUP_BY setting you will find that your query won't work at all). I would re-write the query like so:

select code, created, cid, (
    select count(*)
    from registrations
    where registrations.cid = conference.cid
) as reg_count
from conference
where c.code = 'not_existing_code'

This query is semantically correct, will produce the identical result and 0 rows when the conference table has no matching rows.