CODEWITHSUNDEEP
sqlsql-servert-sql
Kuttan Sujith
Kuttan Sujith

Reputation: 7979

Adding more condition while joining or in where which is better?

    SELECT C.* 
      FROM Content C
INNER JOIN ContentPack CP ON C.ContentPackId = CP.ContentPackId 
                         AND CP.DomainId = @DomainId

...and:

    SELECT C.* 
      FROM Content C
INNER JOIN ContentPack CP ON C.ContentPackId = CP.ContentPackId
     WHERE CP.DomainId = @DomainId

Is there any performance difference between this 2 queries?

Upvotes: 0

Views: 166

Answers (2)

Icarus
Icarus

Reputation: 63966

There's no performance difference but I would prefer the inner join because I think it makes very clear what is it that you are trying to join on both tables.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332581

Because both queries use an INNER JOIN, there is no difference -- they're equivalent.
That wouldn't be the case if dealing with an OUTER JOIN -- criteria in the ON clause is applied before the join; criteria in the WHERE is applied after the join.

But your query would likely run better as:

SELECT c.* 
  FROM CONTENT c
 WHERE EXISTS (SELECT NULL
                 FROM CONTENTPACK cp
                WHERE cp.contentpackid = c.contentpackid
                  AND cp.domainid = @DomainId)

Using a JOIN risks duplicates if there's more than one CONTENTPACK record related to a CONTENT record. And it's pointless to JOIN if your query is not using columns from the table being JOINed to... JOINs are not always the fastest way.

Upvotes: 7

Related Questions