SELECT * FROM dbo.table1,
dbo.table2 AS T2,
dbo.table3 AS T3,
dbo.table4 AS T4
WHERE dbo.table1.ID = T2.ID
AND T2.ID = T3.ID
AND T3.ID = T4.ID
(OR)
SELECT
*
FROM dbo.table1 T1
INNER JOIN dbo.table2 T2 ON T1.ID = T2.ID
INNER JOIN dbo.table3 T3 ON T2.ID = T3.ID
INNER JOIN dbo.table4 T4 ON T3.ID = T4.ID
Both have no difference.It is better to stay away from “comma joins” because a) the ANSI join syntax is more expressive and you’re going to use it anyway for LEFT JOIN, and mixing styles is asking for trouble, so you might as well just use one style; b) ANSI style is clearer.
Both will take same time to execute, there is no performance difference .
Without Join keyword it behave as Cross Joins, produce results that consist of every combination of rows from two or more tables. That means if table table2 has 6 rows and table table3 has 3 rows, a cross join will result in 18 rows. There is no relationship established between the two tables – you literally just produce every possible combination.
With an inner join, column values from one row of a table are combined with column values from another row of another (or the same) table to form a single row of data.
If a WHERE clause is added to a cross join, it behaves as an inner join as the WHERE imposes a limiting factor.
In both the cases you mentioned above, there wont be any difference in the way sql engine executes them in the background. The only thing affects on performance is how effective are your indexes on joining columns in case of join and where clause in case of comma separated tables names.
So just make sure you have proper indexes,statistics updated etc
And one more important thing is you are using select "*", if possible try to use only the columns you are interested.
Both are joins, first is implicit, which will perform cross join as pointed in previous answer, the latter one is an explicit inner join notion. Though it should not make a difference in terms of performance.
