SQL Join types and possible minimum and maximum number of rows

Question

enter image description here

I have 2 tables, both contain a column user_id; table 1 has 50 rows and table 2 has 100 rows. If I perform an inner join, left join, right join and full outer join from table 1 to table 2.

What are the minimum and maximum possible number of rows from these joins? Give your answers in integer.

Note: Do not make any assumptions about the uniqueness of user_id in these tables.

• Inner Join: Min = ?, Max = ?

• Left Join: Min = ?, Max = ?

• Right Join: Min = ?, Max = ?

• Full Outer Join: Min = ?, Max = ?

• Inner Join: Min = 0, Max = 150

• Left Join: Min = 50, Max = 150

• Right Join: Min = 100, Max = 150

• Full Outer Join: Min = 50, Max = 150

Are my answers correct?

Answer 1

You have twice been given the advice to create the two tables and try to measure this somehow. I think this is bad advice. Use your brain instead. :-)

It seems to be assumed that you join the two tables on user_id.

There are three questions that look at extremes in order to find the answers for every join type:

What will the results look like when ...

1. ... there is no match at all? E.g. one table contains user IDs 1 to 50 and the other table the IDs 51 to 150.
2. ... every row matches every row in the other table? I.e. there exists just one user ID in the two tables.
3. ... every row in the smaller table has exactly one distinct match in the bigger table? E.g. one table table contains user IDs 1 to 50 and the other table the IDs 1 to 100.

The answers

Join type	case 1	case 2	case 3	min	max
Inner join	0	50 x 100 = 5000	50	0	5000
Left outer join	50	50 x 100 = 5000	50	50	5000
Right outer join	100	50 x 100 = 5000	100	100	5000
Full outer join	50 + 100 = 150	50 x 100 = 5000	50 + 50 = 100	100	5000

So, no, your answers are not correct. If you don't understand some of my calculations, think about what the joins do exactly.

One thing is a bit confusing:

Note: do not make any assumptions about the uniqueness of user_id in these tables.

As you can see from the questions we must ask, this is all about the uniqueness or non-uniqueness of the user_id in the tables. It is the key to answer the questions properly and get the right numbers. Well, maybe they meant just that: Don't assume there to be a certain number of matches or no matches, exactly 50 different IDs or exactly 100 different IDs, or whatever, but consider all possiblities instead.