Maximum Records In Inner Join

Hello! Trying to understand INNER JOINs better:

  1. What is the maximum number of rows that can be returned when you join 2 tables?

  2. Also - let’s say we are joining on a column called ID between two hypothetical tables A and B : table A has 5 rows for ID 1, table B has 10 rows for ID 1, after the INNER JOIN, how many rows would be returned for ID 1 ?

Thank you in advance :slight_smile:

Hello @text2418761122 ,

Inner joins selects records with matching IDs in both tables.

Your limitations are based on other factors such as the database, configuration and disk space.

@coffeencake thank you ! any thoughts on my question 2 ?

id (identifiers) are almost always unique. Otherwise you can’t identify.

It is also really uncommon to join on two ids (often primary keys), usually you join a primary key (PK) with a foreign key (FK)

Hello @text2418761122 ,

The following example has Table A with 5 rows and Table B with 2 rows. There are two rows in total that will match up; therefore, your query will return 2 rows with an inner join.

Table A                Table B
ID                     ID
----------            ---------
1                      1
2                      2

I hope that helps.

That makes sense thanks! Let’s say we were joining two foreign keys together (albeit uncommon). Would the answer to my Qs2 above be 10 or 50 (5 x 10) ?

Got it ! But let’s say we were talking about a column that isn’t unique for each row, how does the join work then? Thats the bit that confuses me still

Again, then you very likely need to rethink your database architecture.

I am not entirely sure, I would say 50 rows, but because it is so uncommon, I can’t say with absolute certainty.

1 Like

I find working with data helps explain things.

(Keeping mind all the notes that @stetim94 are valid :slight_smile: )

Student_Course Table              Volunteer_Jobs Table
-------------------------         --------------------
Student_Num   Course_Num          Student_Num  Job_Num
-----------   -----------         -----------  -------
1             C1                  1            J1
2             C2                  2            J1
2             C2                  2            J2
1             C1                  1            J3

A query from both tables with an inner join on Student_Num from would result in 8 records total.

1 Like

perfect ! i suspect it would be 50 too

this makes it super clear! thanks so much :slight_smile: So I think the generalized version of this would be that for each matching student_num, the rows returned would be a cartesian product ie. 2 rows from course table x 2 rows from volunteer table = 4 rows for each student_num

1 Like