September 17, 2008 at 12:57 am
Hi guys, I read once that the inner join was better. This morning I tried to prove it and came very short, I could see no difference in client statistics or execution plan between the below queries. Has anybody got any thoughts on this
Select
* FROM
Table1 A
INNER JOIN
Table2 B
ON A.ID = B.ID
VS
SELECT
* FROM
Table1 A, Table2 B
WHERE
A.ID = B.ID
September 17, 2008 at 1:35 am
AFAIK there is no difference regarding the handling of the database engine.
I prefer the inner join: Say you want to change the inner to an outer join. That ist easy whereas to change the where condition is more difficult.
September 17, 2008 at 1:47 am
The comma seperated join, is an ANSI 89 standard join, whilst the other is the newer ANSI 92 standard join.
I dont know about the performance benefits of 92 over 89, however I beleive the 89 standard is being deprecated.
September 17, 2008 at 1:47 am
Using JOIN is considered better for reasons of standardisation and readability, not performance. For a simple query like yours, you will see no difference in the query plans.
Using JOIN is the ANSI standard way of doing it. The old style outer join using *= has been deprecated in SQL2005, and you can only use OUTER JOIN syntax.
From the readability side of things, it makes it obvious which are the JOIN columns, as opposed to those that are genuine WHERE filters.
September 17, 2008 at 1:48 am
Hi there,
In addition, join is used in the ANSI Standard... well that's what I heard...
I didn't even know you could use commas since in almost all books and inforation on the net uses join...
but now that I know that we can use commas... I think I'll stick with join. If you would have a lot of joins it's easier FOR ME to trace which columns came from what table and you can also put the WHERE filtering of each table on their ON clause.
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
June 25, 2019 at 1:38 pm
A comma separated join (CROSS JOIN) is great when you want to end up with ALL COMBINATIONS of both tables, which isn't often. It is great when you're in an environment where the DBA policies do not allow variables (Yes, they exist!). Simply create a 1 row temp table or CTE to contain all variables. Cross joining to a one row table will not increase your volume because you're multiplying x 1. Other than that it's better to use JOIN, LEFT JOIN or OUTER JOIN because they will reduce your volume.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply