Min and max of entries which SQL query can return.

Question

Hi, guys! I'm trying to get into SQL joins and there is one thing, that I can't understand.
For example, we have 2 tables (tab1and tab2). Both of them consist of one column with numerical data type. The first table has 4 entries and the second has 5. These entries can be any.
So, what is the min and max number of entries that the query can return:

SELECT t1, t2 FROM tab1 LEFT JOIN tab2 ON tab1.t1 = tab2.t2;

The min is 4 and the max is 4 too or more?

Answer 1

The maximum is 20.

Consider

t1: 1,1,1,1
t2: 1,1,1,1,1

Each t1 will find 5 join partners in t2 and join up with all of them.

Now that's in real world SQL. If this is a uni exercise then I think no two rows can be the exact same, and all the values in t1 and t2 have to be distinct:

t1: 1,2,3,4
t2: 1,2,3,4,5

Which obviously gives you 4 tuples in your answer.

Answer 2

Thank you very much! I've just thought about this variant.

I am also stuck with this min/max problem with another type of query. The structure is more complicated and looks like that:

SELECT t1 FROM tab1 WHERE EXISTS (select 1 from tab2 where tab1.t1 = tab2.t2);

It will be fantastic if you help!

Answer 3

That's I think a left inner join, or similar anyway. It's saying, put t1 in the output if we can find matches in t2 yes?

Try to think about what happens when

t1: 1,2,3,4
t2: 1,2,3,4,5

and

t1: 1,2,3,4
t2: 10,11,12,13,14,15

When do we have t2s that match up with t1s?