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?

Improve this question
3
  • 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. Commented Jul 17, 2014 at 9: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. Commented Jul 17, 2014 at 9: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. Commented Jul 17, 2014 at 9:42

1 Answer 1

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

"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)
Was referring to left/right
I understand that, but you can't use left/right when using implicit joins.
That was exactly my point
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.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.