Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Left Join bigger than the "left" table alone?

Tags:

mysql

Am I understanding what Left Join is supposed to do?

I have a query. Call it Query A. It returns 19 records.

I have another query, Query B. It returns 1,400 records.

I insert Query B into Query A as a left join, so Query A becomes:

SELECT *
FROM tableA
LEFT JOIN (<<entire SQL of Query B>>) ON tableA.id = tableB.id

Now, a Left Join means everything from Table A, and only records from Table B where they match. So no matter what, this mixed query should not return more than the 19 records that the original Query A returns. What I actually get is 1,000 records.

Am I fundamentally misunderstanding how LEFT JOIN works?

like image 380
Stephen R Avatar asked Dec 24 '22 20:12

Stephen R


1 Answers

You're not exactly misunderstanding LEFT JOIN, so much as the results implied by it. If you have only one row in A, and 1000 in B that reference to the id of that single row in A; your result will be 1000 rows. You're overlooking that the relation may be 1-to-many. The size of the "left" table/subquery (subject to WHERE conditions) is the lower bound for the number of results.

like image 123
Uueerdo Avatar answered Jan 05 '23 06:01

Uueerdo