1

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)

  • 1
    "Please don't "cross-post" - meaning post the same question to both Stack Overflow and DBA Stack Exchange. While our subject matters certainly overlap, this activity splinters answers and can cause unnecessary work for other users. Usually one of the copies of the question will either be deleted, or migrated / closed / merged." dba.stackexchange.com/help/on-topic – Mat Feb 17 '16 at 13:14
  • This is expected behaviour. – ypercubeᵀᴹ Feb 17 '16 at 13:24
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.