Using the following query
select * from table1 left join table2 on table1.name = table2.name
table1 returns 16 rows and table2 returns 35 rows.
I was expecting the above query to return 16 rows because of the left join, but it is returning 35 rows. right join also returns 35 rows
Why is this happening and how do I get it to return 16 rows?
Advertisement
Answer
LEFT JOIN can return multiple copies of the data from table1, if the foreign key for a row in table 1 is referenced by multiple rows in table2.
If you want it to only return 16 rows, one for each table 1 row, and with a random data set for table 2, you can use just a plain GROUP BY:
select * from table1 left join table2 on table1.name = table2.name group by table1.name
GROUP BY aggregates rows based on a field, so this will collapse all the table1 duplicates into one row. Generally, you specify aggregate functions to explain how the rows should collapse (for example, for a number row, you could collapse it using SUM() so the one row would be the total). If you just want one random row though, don’t specify any aggregate functions. MySQL will by default just choose one row (note that this is specific to MySQL, most databases will require you to specify aggregates when you group). The way it chooses it is not technically “random”, but it is not necessarily predictable to you. I guess by “random” you really just mean “any row will do”.