Prev: Database Publishing
Next: Temp tables vs Permanent table with deletes
From: tshad on 5 Apr 2010 13:35
If you have something in your where clause that could be put in the inner
join would it be better there?

SELECT *
FROM tableA a
JOIN tableB b on b.key = a.key
Where b.status <> 15 and b.status <> 20

or

SELECT *
FROM tableA a
JOIN tableB b on b.key = a.key and b.status not in (15,20)

Any difference?

Thanks,

Tom


From: Plamen Ratchev on 5 Apr 2010 14:10
There is no difference when using inner join. The optimizer will generate the same execution plan. It is more a
preference/style of writing queries. I prefer to place filters in WHERE and leave only join predicates in the ON clause:

SELECT <columns>
FROM tableA AS a
JOIN tableB AS b
ON b.key = a.key
WHERE b.status NOT IN (15, 20);

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 5 Apr 2010 14:24

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:45CdnbXTLs0XuifWnZ2dnUVZ_hKdnZ2d(a)speakeasy.net...
> There is no difference when using inner join. The optimizer will generate
> the same execution plan. It is more a preference/style of writing queries.
> I prefer to place filters in WHERE and leave only join predicates in the
> ON clause:
>

That was what I figured as well.

I didn't think that there would be any significant performance enhancement
with this.

As you said, it is a preference.

I assume it would be the same if it were a Left Join.

Thanks,

Tom

> SELECT <columns>
> FROM tableA AS a
> JOIN tableB AS b
> ON b.key = a.key
> WHERE b.status NOT IN (15, 20);
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 6 Apr 2010 22:21
tshad wrote:
> If you filter using the Where clause (and that was the result you wanted)
> then should you use an INNER JOIN instead?
>

If you refer to the case when a left outer join is transformed to inner join because of predicate(s) in WHERE on the
non-preserved table attributes, then yes, it makes sense to simply write the query using inner join.

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Database Publishing
Next: Temp tables vs Permanent table with deletes