Performance degrading placing join in WHERE instead of FROM block (using =, =*, *=)

[tekanet]

Hello folks,
first of all I really don't know how you gurus call this way of
writing joins:

SELECT
A.FIELD,
B.FIELD
FROM
TABLE_A A,
TABLE_B B
WHERE
A.ID_FIELD = B.ID_FIELD

I find this way very useful and readable. It works also with left and
right Joins (using *= or =* instead of = )

A friend of mine found that the inner join way (using = ) in Access is
much more slower than using the classic INNER JOIN TABLE ON FIELD
sintax. My question is: was MSSQL Server studied for using the short
way, or it is just a workaround found by someone? Is there a
performance degrade folllowing this way?

TIA,
tK

[Erland Sommarskog]

tekanet (te*****@inwind.it) writes:

first of all I really don't know how you gurus call this way of
writing joins:

SELECT
A.FIELD,
B.FIELD
FROM
TABLE_A A,
TABLE_B B
WHERE
A.ID_FIELD = B.ID_FIELD

I find this way very useful and readable.
The alternative way of writing this in so-called ANSI JOINS is:

SELECT a.field, b.field
FROM table_a a
JOIN table_b b ON a.id_field = b.id_field

These two are equvialent, both in terms of function and performance. The
optimizer will normalize both to the same internal representation.

Which one you prefer is a matter of taste. I used the method with
the join condition in the WHERE clause for many years, and I was
skeptic when I first saw the JOIN syntax. But I've changed my mind.
For a query that joins 7-8 tables and with multi-column conditions,
the JOIN syntax gives you a lot better overview, and it is also easier
to verify that you have included all conditions. The WHERE clause is
then left to proper filtering.

But, again, this is a matter of taste. Both ways of writing the JOIN is
OK by SQL Server and by ANSI.
It works also with left and right Joins (using *= or =* instead of = )

But when it comes to outer joins, it's a whole other story. In short,
don't use *= and =*. They are deprecated, and there are all sorts of
issues with them. When it comes to outer joins, the ANSI syntax really
shines. You can do a full join, which you can't do with *=*, because
there is no such operator. You can outer join to a pair of tables which
is the result of an inner join. You can control evaluation order (which
matters for outer joins.) There is a whole lot more you can do - and
you can actuall see what you are doing. (Complex queries with *= are
far from clear-cut.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp