Search code examples
sqlimpala

Why return more rows than the expected after a join?

I have 2 tables:

table 1 : count(*) ->7k rows

table 2 : count(*) ->19k rows

when I do this:

select count(*)
from table1
inner join table2;

It returns 1366127997 rows. How is this possible?

Solution

  • Your current query is actually doing a cross join:

    SELECT COUNT(*)
FROM table1
INNER JOIN table2;  -- behaves like CROSS JOIN sans an explicit ON clause

    You most likely were expecting the result from an inner join involving an ON clause with a primary and secondary key, something like:

    SELECT COUNT(*)
FROM table1 t1
INNER JOIN table2 t2
    ON t2.fk = t1.pk;

    This would likely return fewer records than the count you are currently seeing with the cross join.