CODEWITHSUNDEEP
sql-server-2005
Hakoo Desai
Hakoo Desai

Reputation: 341

What is difference between Join and doing same thing with subquery?

I have tblProducts and NioaProducts. tblProducts is look like this : id | productId | skuCode | manCode |.....

NioaProducts looks like this : partNo | manuCode | price |....

Now, I am executing this two queries where I am getting different counts,

 SELECT     COUNT(manCode) AS Expr1
 FROM         tblProducts p
 WHERE     (manCode IN
                          (SELECT     manuCode
                            FROM          NioaProducts))

This gives me o / p: 10057

Now I am doing same thing with inner join

   SELECT     COUNT(manCode) AS Expr1
     FROM         tblProducts p inner join NioaProducts n on p.manCode = n. manuCode

This gives me o/p: 11481

I just want to know which query is correct and why both results are different?

Upvotes: 0

Views: 32

Answers (1)

ZubaiR
ZubaiR

Reputation: 605

Why the results are different ?

One possibility is one to many relationship. Consider the following two tables

tblProducts - 

manCode   Product
1         p1
2         p2
3         p3

NioaProducts - 

manCode   NioaProductName
1         np1
2         np2
2         np3

When you run 

SELECT     COUNT(manCode) AS Expr1
FROM         tblProducts p
WHERE     (manCode IN
                      (SELECT     manuCode
                        FROM          NioaProducts))

The subquery returns (1,2,2) therefore the query filters only 1 and 2 as result. so it returns 2.

But when you run the join query 

SELECT     COUNT(manCode) AS Expr1
FROM         tblProducts p inner join NioaProducts n on p.manCode = n. manuCode

the result of the inner join is 

tblProducts.manCode NioaProducts.manCode
1                   1
2                   2
3                   2

Therefore the count returns 3

Upvotes: 1

Related Questions