I have two tables table_a and table_b in a Postgresql 13 database, having UUIDs primary key columns.

table_a has multiple entries, whereas table_b is empty (no entry).
The following query returns the expected result, namely entry_count_a larger than 0:

SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a FROM table_a ta

However, the following query returns 0 for both entry_counts:

SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a, COUNT(DISTINCT tb.uuid) AS entry_count_b FROM table_a ta, table_b tb

What is the correct way to write the query, so that entry_count_a contains the correct (expected) value > 0, whereas entry_count_b is 0?

Bonus question: Why does Postgresql behave this way?

>Solution :

Your current query is equivalent to the following one:

SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a, 
       COUNT(DISTINCT tb.uuid) AS entry_count_b 
FROM       table_a ta
CROSS JOIN table_b tb

When you apply the cartesian product between two tables, you multiply their cardinality. You get no rows because one of the two tables has cardinality 0, hence 0*n, for any n, is always 0.

If you want to display the two counts correctly, you could use two subqueries as follows:

SELECT *
FROM (SELECT COUNT(DISTINCT uuid) AS entry_count_a FROM table_a) cnt1,
     (SELECT COUNT(DISTINCT uuid) AS entry_count_b FROM table_b) cnt2

Check the demo here.