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!

Answer 1

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.