MIN() causing MySQL to return all-NULL row instead of 0 rows

Question

I have two tables, services and extraFees, related 1xn through services.id = extraFees.serviceId. My problem is that when I execute the following query for a non-existing combination of s.id and s.category, I still get 1 row(s) returned, with all fields NULL.

SELECT 100 + (s.feeRate * MIN(ef.extra)) AS extraFees FROM services s 
LEFT JOIN extraFees ef ON s.id=ef.serviceId WHERE s.id=12 AND s.category='PRG'

I know the culprit is MIN(), because if I replace it with a number or NULL, I get 0 row(s) returned, which is what I want.

SELECT 100 + (s.feeRate * 5) AS extraFees FROM services s 
LEFT JOIN extraFees ef ON s.id=ef.serviceId WHERE s.id=12 AND s.category='PRG'
SELECT 100 + (s.feeRate * NULL) AS extraFees FROM services s 
LEFT JOIN extraFees ef ON s.id=ef.serviceId WHERE s.id=12 AND s.category='PRG'

Why does this happen and how can I avoid it?

Answer 1

An aggregate function will return a result. Try this:

CREATE TABLE x(id int);
SELECT MIN(id) FROM x

and you will get 1 row: NULL. You can wrap in a subquery if you want to ignore the NULL result:

SELECT y.id
  FROM (SELECT MIN(id) AS id FROM x) y
  WHERE y.id IS NOT NULL

or make use of the HAVING clause.

Answer 2

This happens because, MIN() returns NULL if there were no matching rows.

How can I avoid it?

Try to wrap the call to MIN() in a sub-query to filter out the NULL.

EDIT:

I've read that doc, but I don't see how the NULL returned by MIN() is different from the NULL I manually specify in my last query.

I suspect this is because your second set of queries are not aggregate queries. Aggregate queries will return the result of the aggregation on the rows. If there are no rows then the query returns NULL.

On the other hand the second set of queries are not aggregate queries, those queries can return an 'empty set' as a logical result.