Left and inner Join in SQL

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Left and inner Join in SQL

Post by BittenApple »

Hello all,

I have 64 rows and I need to find them in a bigger data set. Here, my inquiry is about the logic that I have applied.

I did a left outer join with missing on the second dataset and I was able to find those rows that exist in the first/left dataset, but it doesn't exist in the second/right dataset. The result came 4 rows.

Then I did inner join between first and second dataset with Distinct values and came 54 rows.

If I do without distinct, the result is 169 rows. 

54+4 equal to 58 and deduct it from 64, we have 6 rows left, which I don't know what is going about them.

Can we say since we put distinct, we removed duplicates, in fact those 6 rows are among those 54 rows, they are just taken away by Distinct because they are duplicates.

Regards,
bittenapple

Here, I am looking for logic. I need to know the way I elaborate it is correct. I need to present it to my boss.

Regards,

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Left and inner Join in SQL

Post by HansV »

That sounds correct. You can also explain it this way:

The small dataset has 64 rows.
4 of those do not occur in the large dataset.
So 64-4 = 60 rows do occur in the large dataset.
Not all of them are unique: there must be 6 duplicate rows, leaving 54 unique rows that also occur in the large dataset.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Left and inner Join in SQL

Post by BittenApple »

Hello Hans,
Wow, great response. Again, you saved my life one more time.
Regards,
BittenApple,