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