How many records result from Inner Join, Left Join, Right Join given Table A and Table B?

The number of records resulting from a join depends on the join type and the cardinality of the join keys. An INNER JOIN returns m records (where m is the count of matching key combinations). A LEFT JOIN returns at least |A| records, and a RIGHT JOIN returns at least |B| records. A FULL OUTER JOIN returns |A| + |B| - m records.

Mechanics of Row Multiplication

The "at least" qualification for LEFT and RIGHT joins, and the precise count for INNER joins, hinges on "row multiplication." If a single key from one table (e.g., Table A) matches multiple keys in the other table (Table B), the row from Table A will be duplicated for each corresponding match in Table B. This occurs in 1:N or N:M relationships. For instance, if Table A has a unique user_id but Table B contains five orders for that user_id, an INNER or LEFT JOIN on user_id will produce five rows for that specific user_id from Table A. LEFT JOIN guarantees all rows from Table A are present, padding with NULLs for non-matches in Table B. RIGHT JOIN does the inverse for Table B.

Performance Implications and Trade-offs

This "row multiplication" can significantly inflate dataset sizes, impacting query performance and storage costs. In distributed systems like Apache Spark, it can lead to substantial data shuffling across partitions, becoming a major bottleneck. For data warehouses like Snowflake, increased row counts mean more micro-partitions to scan and more compute consumed. Data engineers must understand the expected cardinality of their join keys to prevent unintended data explosions, which can also break assumptions in downstream dbt models or data quality checks.

To validate the record count, always run a COUNT(*):

SELECT COUNT(*)
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id;

In the interview, also mention…

Always validate your join results with COUNT() and COUNT(DISTINCT <join_key>) on both sides before and after* the join. Emphasize understanding the data's distribution and key uniqueness upfront to anticipate cardinality issues and design efficient, correct joins.