SQL left join returns all the rows

Question

I have a table, table1 that has 129 rows. I'd like to join another table table2 with 429 rows to it, and end up with only 129 rows.

So far, I've tried:

select *
from table1
left join table2 on table1.id = table2.id;

But I always end up with 429 rows.

I'm clearly missing something here. Do you know what it is?

Do all rows in `table2` have an `id` that matches a row in `table1`? — JamesFaix, Jun 12 '18 at 17:34
`with cte as (select distinct (Id) from table2) Select * from table1 left join cte on cte.id = table1.id` — Ajay Gupta, Jun 12 '18 at 18:13
" table2 has some multiples of table1's ids". A left join can multiply rows on equal matching ON conditions. This commonly occurs if you don't join on a primary key or distinct column. If the tableA ids are distinct, and you match to tableB ids which are multiples of id's contained in tableA- then you will multiply rows in tableA by the LEFT JOIN and will get the exact number of rows in tableB. — Zorkolot, Jun 12 '18 at 19:25

tourist · Answer 1 · 2018-06-12T17:33:18.167

1

Inner join is what you might need

select table1.*
from table1
inner join table2 on table1.id = table2.id;

edited Jun 12 '18 at 17:33

answered Jun 12 '18 at 17:26

tourist

Unfortunately even the inner join still returns all 429 results instead of the 129 results. – Joshua M. Moore Jun 12 '18 at 17:30
Could this be if table2 contains multiple instances of table1.id? – Joshua M. Moore Jun 12 '18 at 17:31
@JoshuaMoore I updated the query, Can u try the updated query? – tourist Jun 12 '18 at 17:33
I did, it returned all the records from table2. table2 has some multiples of table1's ids. I think I'm inching towards a solution. – Joshua M. Moore Jun 12 '18 at 17:36
@JoshuaMoore it shouldn't because we are using `table.*` in the `select` clause – tourist Jun 12 '18 at 17:37

score 1 · Answer 2 · answered Jun 12 '18 at 17:28

1

This image always helps me when constructing SQL joins. It's usually the top result when googling "sql joins"

answered Jun 12 '18 at 17:28

Jake

I think the query you want is: `LEFT JOIN TableB .. WHERE B.Key IS NULL` – Jake Jun 12 '18 at 17:30

2 Answers2