CODEWITHSUNDEEP
mysql
bdx
bdx

Reputation: 3516

Why does changing the comparison in a HAVING clause from '=' to '<' change the output when there are no matches?

In the following query, changing the comparison operator in the HAVING clause from '=' to '<' when the query returns no results changes the output so that there's either 1 row returned (of all NULLs) or no rows returned.

Can someone explain why this behaviour is as demonstrated? I'd ideally like to have the first query return 0 rows, but it would be nice if it could be done without wrapping it in another query to exclude the NULLs.

Query:

SELECT `templates`.* 
FROM `templates` 
INNER JOIN `items` ON `items`.`template_id` = `templates`.`id` 
HAVING COUNT(items.id) = 0

Results:

NULL,NULL,NULL...
(1 row(s) returned)

In comparison to:

Query:

SELECT `templates`.* 
FROM `templates` 
INNER JOIN `items` ON `items`.`template_id` = `templates`.`id` 
HAVING COUNT(items.id) < 0

Results:

(0 row(s) returned)

But also, this variation with HAVING COUNT(items.id) < 1 returns the row of NULLs:

Query:

SELECT `templates`.* 
FROM `templates` 
INNER JOIN `items` ON `items`.`template_id` = `templates`.`id` 
HAVING COUNT(items.id) < 1

Results:

NULL,NULL,NULL...
(1 row(s) returned)

Upvotes: 0

Views: 45

Answers (1)

Hitesh Mundra
Hitesh Mundra

Reputation: 1588

Simple, count() function never return negative. your condition says having count(items.id) < 0 which means having 0 < 0 which is always false. But having count(items.id)=0 can be true means or count(items.id)<1 can be true means 0=0 or 0<1 are true.

mysql> select 1 from test  having count(id)=0;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.29 sec)

mysql> select 1 from test having count(id)<0;
Empty set (0.00 sec)

Upvotes: 1

Related Questions