Inner join between two tables with same count values

Question

I have been working on this issue since 2 days now.

I have two tables created by using SQL Select statements

SELECT (


) Target

INNER JOIN

SELECT (


) Source

ON Join condition 1
AND Join condition 2
AND Join condition 3
AND Join condition 4
AND Join condition 5

The target table has count value of 10,000 records. The source table has count value of 10,000 records.

but when I do an inner join between the two tables on the 5 join conditions

I get 9573 records.

I am basically trying to find a one to one match between source and target table. I feel every field from target matches every field in source.

Questions:

Why does my inner join give less records even if there are same value of records in both tables?
If it is expected, how can I make sure I get the exact 10,000 records after the join condition?

sarin · Accepted Answer · 2014-04-08 22:39:51Z

0

1) An INNER JOIN only outputs the rows from the JOINING of two tables where their joining columns match. So in your case, Join Condition1 may not exist in rows in both tables and therefore some rows are filtering out.

2) As the other poster mentioned a left join is one way. You need to look which table source or target you want to use as your master i.e. start from and return all those rows. You then left join the remaining table based on your conditions to add all the columns where you join conditions match.

It's probably better if you give us the tables you are working on and the query\results you are trying to achieve.

answered Apr 8, 2014 at 22:39

sarin

5,3173 gold badges39 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt H · Accepted Answer · 2014-04-08 22:34:52Z

0

There's some really good articles about the different joins out there. But it looks like you'd be interested in left joins. So if it exists in Target, but not in Source, it will not drop the record.

So, it would be:

SELECT(...) Target
LEFT OUTER JOIN
SELECT(...) Source
   ON cond1 and cond2 and cond3 and cond4 and cond5

Give that a shot and let me know how it goes!

answered Apr 8, 2014 at 22:34

Matt H

6484 silver badges18 bronze badges

Comments

Nick.Mc · Accepted Answer · 2014-04-08 23:00:14Z

0

Sometime you need to rely on logical analysis rather than feelings. Use this query to find the fields that do not match and then work out your next steps

SELECT 
Target.Col1,Source.Col1,
Target.Col2,Source.Col2,
Target.Col3,Source.Col3
FROM
(

) Target
FULL OUTER JOIN
(

) Source
ON  Target.Col1=Source.Col1
AND Target.Col2=Source.Col2
AND Target.Col3=Source.Col3
WHERE (
Target.Col1 IS NULL 
OR Source.Col1 IS NULL 
OR Target.Col2 IS NULL 
OR Source.Col2 IS NULL 
OR Target.Col3 IS NULL
OR Source.Col3 IS NULL
)

answered Apr 8, 2014 at 23:00

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Collectives™ on Stack Overflow

Inner join between two tables with same count values

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related