2

I am curious to learn the difference between selecting from two tables and joining in the WHERE clause and explicitly using a JOIN clause.

For what it's worth, I'm using MSSQL not Oracle so I'm interested in the inner workings of this in a MSSQL so unless this causes the same effect as Oracle, this question doesn't help me.

Take for example the following two statements:

SELECT a.Field1, a.Field2, b.Field1
FROM tbl_ExampleA a, tbl_ExampleB b
WHERE a.Field1 = b.Field1

and

SELECT a.Field1, a.Field2, b.Field1
FROM tbl_ExampleA a
    INNER JOIN tbl_ExampleB b
    ON a.Field1 = b.Field1

They return the same results, but behind the scenes, what is happening differently?

Community
  • 1
  • 1
Adam K Dean
  • 7,245
  • 10
  • 45
  • 68
  • Okay, see, that link /is/ constructive. Thanks. Your initial dupe was not helpful to me, you should really consider commenting after marking questions as dupes. Yes, we're adding noise to the system, but we're still developers looking for intelligent answers and being told to "move along" is a great way to not be constructive. Just an FYI. – Adam K Dean Jul 17 '14 at 09:40
  • Well you did **not** mention that you were using SQL Server in your initial question. So I picked the first hit when searching. Had you mentioned SQL Server, I would have chosen the other question. – a_horse_with_no_name Jul 17 '14 at 09:42
  • Well that is the reason we have commenting capabilities, to clarify the situation. Not just blanket mark items as dupe. Anyway, this discussion is also non-constructive. Thanks for your moderation. – Adam K Dean Jul 17 '14 at 09:42

1 Answers1

1

The first example is an implicit join and should be avoided because it can cause confusion when maintaining scripts later on, additionally, when the query becomes more complex, an implicit join can have undesirable affects, as the direction of the join is not clear (Left/Right?), or you could end up inadvertently cross joining data.

Explicit joins are generally a more acceptable means of writing a query, due to clarity and a more reliable result set depending on the vendor

Daniel Dawes
  • 935
  • 5
  • 16