Why is INNER JOIN producing more records than original file?

Question

I have two tables. Table A & Table B. Table A has 40516 rows, and records sales by seller_id. The first column in Table A is the seller_id that repeats every time a sale is made.

Example: Table A (40516 rows)

seller_id | item | cost
------------------------
   1      | dog  | 5000
   1      | cat  | 50
   4      |lizard| 80
   5      |bird  | 20
   5      |fish  | 90

The seller_id is also present in Table B, and also contains the corresponding name of the seller.

Example: Table B (5851 rows)

seller_id | seller_name
-------------------------
   1      | Dog and Cat World INC
   4      | Reptile Love.com
   5      | Ocean Dogs Inc

I want to join these two tables, but only display the seller name from Table B and all other columns from Table A. When I do this with an INNER JOIN I get 40864 rows (348 extra rows). Shouldn't the query produce only the original 40516 rows?

Also not sure if this matters, but the seller_id can contain several zeros before the number (e.g., 0000845, 0000549).

I've looked around on here and haven't really found an answer. I've tried LEFT and RIGHT joins and get the same results for one and way more results for the other.

SQL Code Example:

SELECT public.table_B.seller_name, *
FROM public.table_A
INNER JOIN public.table_B ON public.table_A.seller_id = 
public.table_B.seller_id;

Expected Results:

seller_name           | seller_id | item | cost
------------------------------------------------
Dog and Cat World INC |    1      | dog  | 5000
Dog and Cat World INC |    1      | cat  | 50
Reptile Love.com      |    4      |lizard| 80
Ocean Dogs Inc        |    5      |bird  | 20
Ocean Dogs Inc        |    5      |fish  | 90

I expected the results to contain the same number of rows in Table A. Instead I gut names matching up and an additional 348 rows...

Update:

I changed "unique_id" to "seller_id" in the question.

I guess I should have chosen a better name for unique_id in the original example. I didn't mean it to be unique in the sense of a key. It is just the seller's id that repeats every time there is a sale (in Table A). The seller's ID does repeat in Table A because it is supposed to. I simply want to pair up the seller IDs with the seller names.

Thanks again everyone for their help!

1- What is the unique_id type in your tables? 2- the extra rows are duplicated? — gandalf, Jun 14 '19 at 13:14
your query should work if the tables and data is correct. Maybe show us a sample of the "extra" records. — chue x, Jun 14 '19 at 13:15
Reasons:(a) 40516 is not the correct number of rows of Table A, (b) That unique_id is not unique in Table B. — forpas, Jun 14 '19 at 13:16
Please provide us with [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/minimal-reproducible-example). — Łukasz Kamiński, Jun 14 '19 at 13:19
@Pliskin the unique_id is CHARACTER VARYING. I had to choose this due to the numbers occasionally having dashes in them (i.e., 9-000768). — SupaDupa, Jun 14 '19 at 13:51
The unique column is not unique, in your case it is like a foreign key. — gandalf, Jun 14 '19 at 15:14

score 2 · Accepted Answer · answered Jun 14 '19 at 13:16

unique_id is already not correctly named in the first table, so there is no reason to assume it is unique in the second table either.

Run this query to find the duplicates:

select unique_id
from table_b
group by unique_id
having count(*) > 1;

You can fix the query using distinct on:

SELECT b.seller_name, a.*
FROM public.table_A a JOIN
     (SELECT DISTINCT ON (b.unique_id) b.*
      FROM public.table_B b
      ORDER BY b.unique_id
     ) b
     ON a.unique_id = b.unique_id;

In this case, you may get fewer records, if there are no matches. To fix that, use a LEFT JOIN.

score 0 · Answer 2 · answered Jun 14 '19 at 13:23

0

Because unique id column is not unique.

answered Jun 14 '19 at 13:23

berkancetin

315
1
3
18

score 0 · Answer 3 · answered Jun 20 '19 at 20:16

Gordon Linoff was correct. The seller_id (formerly listed as unique_id) was indeed duplicated throughout the data set. I foolishly assumed otherwise. Also the seller_name had many duplicates too! In the end I had to use the CONCAT() function to join the seller_id with second identifier to create a type of foreign key. After I did this the join worked as expected. Thanks everyone!

Why is INNER JOIN producing more records than original file?

3 Answers3