Performance difference in 2 coding examples

31 Aug 2006 6:18 PM

Stephanie

Is there any difference in these 2 ways of coding? Does it matter if the
qualifier is in the WHERE clause a opposed to being in an "AND" statement in
the JOIN?

Sample A
SELECT A.Col1, B.Col2
FROM TableA A INNER JOIN
          TableB B
          ON A.Col1 = B.Col1
          AND B.Col5 = 'XXX'

Sample B
SELECT A.Col1, B.Col2
FROM TableA A INNER JOIN
          TableB B
          ON A.Col1 = B.Col1
WHERE B.Col5 = 'XXX'

31 Aug 2006 6:29 PM

Tom Cooper

Since they are INNER JOINs, there is no difference. If they were LEFT OUTER
JOINs, they would produce different results.

Tom

Show quote

"Stephanie" <Stepha***@discussions.microsoft.com> wrote in message
news:BD47AED0-7449-4CAD-B6BC-33498591DB65@microsoft.com...
> Is there any difference in these 2 ways of coding? Does it matter if the
> qualifier is in the WHERE clause a opposed to being in an "AND" statement
> in
> the JOIN?
>
> Sample A
> SELECT A.Col1, B.Col2
> FROM TableA A INNER JOIN
>          TableB B
>          ON A.Col1 = B.Col1
>          AND B.Col5 = 'XXX'
>
> Sample B
> SELECT A.Col1, B.Col2
> FROM TableA A INNER JOIN
>          TableB B
>          ON A.Col1 = B.Col1
> WHERE B.Col5 = 'XXX'
>

31 Aug 2006 6:45 PM

Alexander Kuznetsov

No difference, and another equivalent way is

Sample B
SELECT A.Col1, B.Col2
FROM TableA A INNER JOIN
(SELECT Col1, COl2 FROM TableB WHERE Col5 = 'XXX') B
ON A.Col1 = B.Col1

31 Aug 2006 8:42 PM

abc

Sample A seems to be preferred by Oracle programmers and Sample B by SQL
programmers. Both styles are support by the SQL:1999 standard.

On Infomix Sample A can sometimes perfoms better than Sample B.

Adrian

Stephanie wrote:
Show quote

> Is there any difference in these 2 ways of coding? Does it matter if the
> qualifier is in the WHERE clause a opposed to being in an "AND" statement in
> the JOIN?
>
> Sample A
> SELECT A.Col1, B.Col2
> FROM TableA A INNER JOIN
>           TableB B
>           ON A.Col1 = B.Col1
>           AND B.Col5 = 'XXX'
>
> Sample B
> SELECT A.Col1, B.Col2
> FROM TableA A INNER JOIN
>           TableB B
>           ON A.Col1 = B.Col1
> WHERE B.Col5 = 'XXX'
>