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,387
  • 10
  • 47
  • 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. –  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
  • 975
  • 5
  • 16
  • "*an implicit join can have undesirable affects, as the direction of the join is not clear*" - you can't write an outer join using implicits join in the where clause if you stick with standard SQL (some DBMS do have a proprietary syntax to express an outer join in the where clause) –  Jul 17 '14 at 09:29
  • 1
    Was referring to left/right – Daniel Dawes Jul 17 '14 at 09:33
  • I understand that, but you can't use `left`/`right` when using implicit joins. –  Jul 17 '14 at 09:34
  • 1
    That was exactly my point – Daniel Dawes Jul 17 '14 at 09:34
  • Also, @a_horse_with_no_name, you've marked my question as a dupe and linked it to a question regarding Oracle. Thanks for that. FYI, this community functions better when people are constructive in their contributions. – Adam K Dean Jul 17 '14 at 09:35
  • @AdamKDean: it's still a duplicate and an equivalent questions exists for SQL Server (and MySQL and probably others as well. See here: https://stackoverflow.com/search?q=inner+join+where+) –  Jul 17 '14 at 09:37
  • Thanks, but in future, consider pointing people in the right direction. – Adam K Dean Jul 17 '14 at 09:41