Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

left join returning more than expected

Tags:

sql

mysql

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?

like image 539
oshirowanen Avatar asked Oct 10 '11 10:10

oshirowanen


People also ask

Why is my left join creating more rows?

There are two line items for ID 1003 in the second table, so the result of the join will be 2 line items. So, if your secondary tables have more than one row for the key you're joining with, then the result of the join will be multiple rows, resulting in more rows than the left table.

How many records does LEFT join return?

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

Does LEFT join return multiple rows?

Introduction to SQL Server LEFT JOIN clause The LEFT JOIN clause allows you to query data from multiple tables. The LEFT JOIN returns all rows from the left table and the matching rows from the right table. If no matching rows are found in the right table, NULL are used.

Can left outer join causes duplicates?

Again, if we perform a left outer join where date = date, each row from Table 5 will join on to every matching row from Table 4. However, in this case, the join will result in 4 rows of duplicate dates in the joined DataSet (see Table 6).


1 Answers

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
like image 136
Sergey Kudriavtsev Avatar answered Nov 15 '22 18:11

Sergey Kudriavtsev