MySQL: JOIN empty tables with a constant value column, COUNT(*) returns a 0, but adding a GROUP BY on the constant value returns nothing?

Question

Can someone explain why, when these tables are empty, the following query returns a COUNT(*) of 0 and a MAX(Timsestamp) of null...

SELECT 'Combined', COUNT(*), MAX(Timestamp)
FROM TableA 
JOIN TableB USING (Id)

...while adding a "GROUP BY" on the constant value (first column) returns no rows at all?

SELECT 'Combined', COUNT(*), MAX(Timestamp)
FROM TableA
JOIN TableB USING (Id)
GROUP BY 1

I wasn't able to find anything in the MySQL documentation explaining this behavior. (Re-posted from https://stackoverflow.com/questions/35438668/mysql-join-with-empty-tables-and-count-returns-a-row-with-0-count-but-addin)

Answer 1

It does not matter what you group by, try for example TableA.id. The reason is that MAX and COUNT returns a scalar even for the empty set (your first query). If you add a group by (no matter what attribute you use), you get one scalar per group. But since there are no groups, you get an empty set.

It may be illustrative to add an outer query that count from your second query:

SELECT COUNT(*) 
FROM (
    SELECT 'Combined', COUNT(*), MAX(Timestamp)
    FROM TableA
    JOIN TableB USING (Id)
    GROUP BY 1
) AS T