3

Do these two queries differ from each other?

Query 1:

SELECT * FROM Table1, Table2 WHERE Table1.Id = Table2.RefId

Query 2:

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Id = Table2.RefId

I analysed both methods and they clearly produced the same actual execution plans. Do you know any cases where using inner joins would work in a more efficient way. What is the real advantage of using inner joins rather than approaching the manner of "Query 1"?

Eren
  • 284
  • 2
  • 10
  • Similar question I once had: http://stackoverflow.com/questions/670980/performance-of-inner-join-compared-to-cross-join – soulmerge Sep 02 '09 at 15:04

6 Answers6

3

The two statements you have provided are functionally equivalent to one another.

The variation is caused by differing SQL syntax standards.

For a really exciting read, you can lookup the various SQL standards by visiting the following Wikipedia link. On the right hand side are references and links to the various dialects/standards of SQL.

http://en.wikipedia.org/wiki/SQL

John Sansom
  • 41,005
  • 9
  • 72
  • 84
  • 3
    The old style outer join is deprecated in later versions .. so it makes sense to use JOIN for all queries. – gbn Sep 02 '09 at 14:57
3

These SQL statements are synonymous, though specifying the INNER JOIN is the preferred method and follows ISO format. I prefer it as well because it limits the plumbing of joining the tables from your where clause and makes the goal of your query clearer.

RC.
  • 27,409
  • 9
  • 73
  • 93
  • also on left joins, a condition in the ON works completely different than the same condition in the WHERE. – KM. Sep 02 '09 at 15:01
1

These will result in an identical query plan, but the INNER JOIN, OUTER JOIN, CROSS JOIN keywords are prefered because they add clarity to the code.

While you have the ability to specifiy join hints using the keywords in the FROM clause, you can do more complicated joins in the WHERE clause. But otherwise, there will be no difference in query plan.

Alex Papadimoulis
  • 2,720
  • 1
  • 20
  • 24
1

I will also add that the first syntax is much more subject to inadvertent cross joins as the queries get complicated. Further the left and right joins in this syntax do not work properly in SQL server and should never be used. Mixing the syntax when you add a left join can also cause problems where the query does not correctly return the results. The syntax in the first example has been outdated for 17 years, I see no reason to ever use it.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
0

Query 1 is considered an old syntax style and its use is discouraged. You will run into problems with you use LEFT and Right joins using that syntax style. Also on SQL Server you can have problems mixing those two different syles together in queries that use view of different formats.

KM.
  • 101,727
  • 34
  • 178
  • 212
0

I have found a significant difference using the LEFT OUTER JOINS and putting the conditions on the joined table in the ON clause rather than the WHERE clause. Once you put a condition on the joined table in the WHERE clause, you defeat the left outer join.

When I was using Oracle, I used the archaic (+) after the joined table (with all conditions including join conditions in the WHERE clause)because that's what I knew. When we became a SQL Server shop, I was forced to use LEFT OUTER JOINs, and I found they didn't work as before until I discovered this behavior. Here's an example:

select  NC.*,
        IsNull(F.STRING_VAL, 'NONE') as USER_ID,
        CO.TOTAL_AMT_ORDERED 
from    customer_order CO
INNER   JOIN VTG_CO_NET_CHANGE NC
ON      NC.CUST_ORDER_ID=CO.ID
LEFT OUTER JOIN USER_DEF_FIELDS F
ON      F.DOCUMENT_ID = CO.ID and
        F.PROGRAM_ID='VMORDENT' and
        F.ID='UDF-0000072' and
        F.DOCUMENT_ID is not null
where   NC.acct_year=2017
halnwheels
  • 31
  • 2