The Max records number could be got from a LEFT JOIN Query in MySQL

Code Feedback
1

I have a SQL query as below:

 SELECT  P.ProductID, P.ProductName, S.SalesAmount
         FROM  Product P LEFT JOIN Sales S
         ON    S.ProductID = P.ProductID

Here’s the information about tables and relationship:
1.
Product: ProductID, ProductName, UnitPrice
Sales: ProductID, SalesQuantity, SalesAmount

image
2.
There are 2000 lines in “Product” and 100.000 lines in “Sales”

My question is why is the max records can be returned from the query is 200.000.000 ?

I would appreciate if someone explain in detail to me. Thanks !!!

2

Quick question, how did you come up with 200.000.000 ?

1 Like
3

It’s not a Database i created, it’s a question from a course i’m learning. The question does not provide to me the database design, it only gives me some informations as i listed above and ask me what’s the max records you could get from the SQL query with the information. The answer is 200.000.000, the only explanation i could think of is that there are 2000 lines with the same ID in Product and 100.000 lines in Sales using the same ID which cause the query to render 100.000*2000 = 200.000.000 records

4

Could this really be the case? An entry in Product has both an ID and a Name. Would differently named products have the same ID? It seems to me that ProductID would be a unique primary key and thus you would have 2000 unique IDs in the Product table.

I’m a little rusty on SQL since I haven’t done serious work with it in years, so let me ask you this, for an inner join on these two tables WHERE S.ProductID = P.ProductID what is the max results we can get back?

5

I know how unrealistic it sounds but it’s the only scenario i could think of that can give you 200.000.000 records

6

I’m noticing the notation 1…1 in the diagram, which I believe means “one to one relationship” if I am not mistaken. And from where it’s placed I believe it puts a restriction on the relationship between the two tables in that an entry in Sales can only be linked to one row in Product. If I am correct then having duplicate ProductIDs is not allowed by definition.

I guess I’m trying to convince you that 200.000.000 is not the correct answer by logically thinking through this. I’m assuming you answered this question and your answer was not 200.000.000 and you were marked wrong and were given the feedback that the answer is 200.000.000. Did you consider the possibility that that is wrong? Are you able to ask the instructor to justify this answer?

I could be way off here and if I am I hope someone else can come in and set me straight. But I’m just not seeing how the answer can be 200.000.000. Also, I’d be interested to know what your answer was.