left join returning more than expected

Question

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?

Answer 1

Let's assume you have the following tables:

tbl1:
|Name |
-------
|Name1|
|Name2|

tbl2:
|Name |Value |
--------------
|Name1|Value1|
|Name1|Value2|
|Name3|Value1|

For your LEFT JOIN you'll get:

|tbl1.Name|tbl2.Name|Value |
----------------------------
|Name1    | Name1   |Value1|
|Name1    | Name1   |Value2|
|Name2    | NULL    | NULL |

So, LEFT JOIN means that all records from LEFT (first) table will be returned regardless of their presence in right table.

For your question you need to specify some specific fields instead of using "*" and add GROUP BY tbl1.Name - so your query will look like

select tbl1.Name, SOME_AGGREGATE_FUNCTION(tbl2.specific_field), ...
from table1
left join table2 on table1.name = table2.name
GROUP BY tbl1.Name