I have two tables
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
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 >
Bonus question: Why does Postgresql behave this way?
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.