CODEWITHSUNDEEP
sql
Mr.Blev
Mr.Blev

Reputation: 21

How many minimum and maximum records FULL JOIN can return

This question is interested me in SQL query. How many min. and max. records FULL JOIN can return when joining two tables without any other conditions? Say, if there are 5 rows in table '1' and 10 rows in table '2'.

It seems to me that the minimum and maximum will be equal. 10/10. Is it so?

Exm:

SELECT customers.customer_id,

orders.order_id,

orders.order_date

FROM customers

FULL OUTER JOIN orders

ON customers.customer_id = orders.customer_id

Upvotes: -1

Views: 4459

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

The minimum number of rows is the number of rows in the largest table. This would occur if all the join keys were unique and all the keys in the smaller table match keys in the larger table.

The maximum number of rows is the product of the rows in the two tables -- equivalent to a Cartesian join. This would occur if all the keys in the two tables were the same.

So, if one table has 5 rows and the other 10, the minimum is 10 and the maximum 50.

Here is a db<>fiddle, illustrating these cases.

Upvotes: 1

Benjamin Cbr
Benjamin Cbr

Reputation: 536

Minimum rows returned: A FULL JOIN would return at minimum the number of rows that is contained in the largest table. This would happen for instance if all the rows from the smallest table are matched by only one row from the other joined table the JOIN condition.

Maximum rows returned: At maximum the product of number of rows in both table if the FULL JOIN condition always matches (for instance FULL JOIN ON TRUE).

Upvotes: 3

Related Questions