SQL Server SELECT Performance with JOINS

Question

Hi, in SQL Server, I would like to know if there is any performance difference between doing this (A)...

SELECT a.field1, a.field2, a.field3, b.field1
FROM table1 a INNER JOIN table2 b ON a.IDfield = b.IDfield

and this (B)...

SELECT a.field1, a.field2, a.field3, b.field1
FROM table1 a, table2 b
WHERE a.IDfield = b.IDfield

Well, this is a simplified example. I am working with some 4 joins and I have been doing it like in example B. Is it a bad practice? I simply find it better to write, better to read, but won`t do it any more if it is worse in terms of performance. Of course, I cannot do left joins, right joins this way, but they are not the case in the queries I am doing. Thanks.

Answer 1

No there is no difference - since in the end, both queries will be a INNER JOIN between tables A and B on the a.IDfield = b.IDfield criteria.

But query (A) is the much preferred way of expressing your intent - always use this style when writing new queries! It's the style as described in the ANSI SQL standard, too - so you're definitely better off using this style whenever possible.

Marc

Answer 2

As per my experience, I'd say that it depends!

SQL Server Optimizer is a real mysterious thing. Ideally, there's no difference between the two syntaxes; but, you should use the JOIN keyword, in practice, as Marc said.

Depending on the runtime conditions, SQL Server Optimizer MAY make the second query to run faster than the first one or vice versa. It all depends. You can use the Query Execution Plan to REALLY determine which query performs optimally.

Answer 3

I would under no circumstances ever use the second syntax. It is very prone to accidental cross joins when you have a large number of joins. The first is also far clearer for maintenance. The left and right join syntax that goes along with that style do not work correctly in SQl Server and are being discontinued.

In my opinion it is a very poor practice to use the second syntax. And it's only 17 years out of date besides.

Answer 4

With all other things being equal (same db, same indexes, same statistics, MDOP settings), performance wise there is no difference in the two queries.

You can see this by executing:

SET SHOWPLAN_TEXT ON GO

SELECT a.field1, a.field2, a.field3, b.field1FROM table1 a INNER JOIN table2 b ON a.IDfield = b.IDfield GO

SELECT a.field1, a.field2, a.field3, b.field1FROM table1 a, table2 bWHERE a.IDfield = b.IDfield GO

after executing the above you will see the execution plan is indeed identical for the two queries.

Alternatively you can highlight the statement(s) in management studio, right click, and select "include actual execution plan" to see a graphical representation of the same thing.