are old school joins slower

2 Jun 2005 7:02 PM

ChrisR

The Exec Plans from these two simple queries are identical. Of course theres
not much data here though. Are the old type of joins in query 1 typically
slower, or just not liked/ ANSI standard?

select a.*,titleauthor.title_id
from authors a,titleauthor
where a.au_id = titleauthor.au_id

select a.*,ta.title_id from authors a
inner join titleauthor ta on a.au_id = ta.au_id

TIA, ChrisR

2 Jun 2005 7:26 PM

--CELKO--

No difference between them and infixed joins as far as performance
goes. But the old style is easier to read when you have n-ary
relationships or a Star schema. For fun try to re-write these as
infixed:

SELECT *
FROM A, B, C
WHERE A.x BETWEEN B.y AND C.z;

SELECT *
FROM A, B, C
WHERE A.x IN (B.y, C.z, 42)
AND B.y IN (A.xx, C.zz, 41);

2 Jun 2005 9:11 PM

Ross Presser

On 2 Jun 2005 12:26:20 -0700, --CELKO-- wrote:

> No difference between them and infixed joins as far as performance
> goes. But the old style is easier to read when you have n-ary
> relationships or a Star schema. For fun try to re-write these as
> infixed:

Ugh .... here goes, just for my own challenge amusement:

> SELECT *
> FROM A, B, C
> WHERE A.x BETWEEN B.y AND C.z;

SELECT *
FROM A
INNER JOIN B ON A.x >= B.y
INNER JOIN C ON A.x <= C.z

> SELECT *
> FROM A, B, C
> WHERE A.x IN (B.y, C.z, 42)
> AND B.y IN (A.xx, C.zz, 41);

This one is really pathological ... I can't seem to do it

2 Jun 2005 7:28 PM

oj

There should not be any difference though one is much clearer than the
other.

--
-oj

Show quoteHide quote

"ChrisR" <noem***@bla.com> wrote in message
news:uke6cW6ZFHA.3712@TK2MSFTNGP09.phx.gbl...
> The Exec Plans from these two simple queries are identical. Of course
> theres not much data here though. Are the old type of joins in query 1
> typically slower, or just not liked/ ANSI standard?
>
> select a.*,titleauthor.title_id
> from authors a,titleauthor
> where a.au_id = titleauthor.au_id
>
>
> select a.*,ta.title_id from authors a
> inner join titleauthor ta on a.au_id = ta.au_id
>
>
> TIA, ChrisR
>

2 Jun 2005 7:33 PM

David Portas

"oj" <nospam_ojngo@home.com> wrote in message
news:%23MEWSl6ZFHA.4068@TK2MSFTNGP10.phx.gbl...
> There should not be any difference though one is much clearer than the
> other.

Hopefully it's clear which one you mean ;-)

--
David Portas
SQL Server MVP
--

3 Jun 2005 9:10 AM

oj

that one. can't you see it clearly. ;~)

since it's an inner join, i don't worry too much about it.
now if it's an outer join, i prefer ansi join style.

--
-oj

Show quoteHide quote

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:86qdndw9p8uXwgLfRVn-2g@giganews.com...
> "oj" <nospam_ojngo@home.com> wrote in message
> news:%23MEWSl6ZFHA.4068@TK2MSFTNGP10.phx.gbl...
>> There should not be any difference though one is much clearer than the
>> other.
>
> Hopefully it's clear which one you mean ;-)
>
> --
> David Portas
> SQL Server MVP
> --
>
>

2 Jun 2005 7:32 PM

David Portas

The two statements are equivalent and should result in the same execution
plan and performance. In my experience the optimizer usually treats the
INNER JOIN syntax in exactly the same way as the equivalent join implemented
in a WHERE clause. In fact your first example is still ANSI standard and
perfectly valid. It is only the old outer join *= notation that is
deprecated.

--
David Portas
SQL Server MVP
--