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?
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.
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.
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.
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).
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With