When to use a JOIN for MySQL (SQL)

Question

I cannot effectively understand the difference between these two queries.

select *
from person p, mytlc m
where p.personid = m.personid and p.personid = 3635

select *
from person p
    join mytlc m on p.personid = m.personid
where p.personid=3635

In this case, I don't think either will be a greater performing query;but, what if the query was more complex handling much more data.

Thanks!

score 5 · Accepted Answer · edited May 23 '17 at 12:19

5

If you want to find more information on this topic, you can try googling 'join vs where'. Here are a couple of other questions that address the same thing:

A quote from the third one is interesting (regarding SQL Server, but they are probably similar in behavior):

If you measure this, you will most likely discover that the two versions use the exact same access plan. SQL Server tries very hard to optimize a query, and in that process, a where clause which equates columns from two tables will be converted to an inner join.

These seems to indicate that technically the join is correct and more efficient than a where, but it doesn't matter in practice because optimization will likely correct the where into a join. However, for cases where it won't optimize, it is better to be explicit, and as indicated by others, join is the right way to do it.

edited May 23 '17 at 12:19

Community

1
1

answered Jul 22 '11 at 19:04

mellamokb

56,094
12
110
136

But don't you end up spending extra time on the where query since you have to convert it into a more optimized form, i.e. an inner join, whereas the inner join is already an inner join? I realize this might be a negligible amount of time, but is this true or am I missing something? – chandsie Jul 22 '11 at 19:10
@chands: I would call that a negligible amount of time compared to actually executing the query and returning the results (especially over a network). In this case it's probably more important to make the query easiest to understand, whichever method that happens to be. I prefer a JOIN for readability because it puts the condition as close as possible to the table names as you read through the query. – mellamokb Jul 22 '11 at 19:15
Yes, I thought just as much about the efficiency. Just wanted a confirmation from someone more versed with databases. And what you say about a JOIN being more readable I definitely have to agree with, and so do others as well as is evidenced by all the other answers. – chandsie Jul 22 '11 at 20:22
@mellamokb Thank you for this post. I prefer join as it seems to solve a much needed optimization before allowing resources to be used in handling the query. I will read more into these articles and see if I can better understand all the types of joins and their impact on performance and accuracy. – Jesse Jul 25 '11 at 13:56

score 4 · Answer 2 · answered Jul 22 '11 at 19:04

4

They are just two different ways of saying the same query. The former is the "old" way of doing joins and is (in my experience) less desirable. The latter is the "new" way of doing joins and is more explicit.

They are functionally equivalent.

answered Jul 22 '11 at 19:04

Daniel DiPaolo

55,313
14
116
115

score 1 · Answer 3 · answered Jul 22 '11 at 19:16

The first is more likely to have an error if you have many joins (the accidental cross join). The first is also likely to create problems when you need to change to an outer join as combining implicit and explicit joins can definintely create incorrect results. Therefore the first is harder to maintain over time. The first is also unclear when you intended a cross join or if it was accidental. This is a real problem in maintenance as you might fix a "bug" that was intentional or think something was intentional that was a genuine bug. I have also found that many people (not everyone) who use the first form don't correctly understand joins and thus often do not understand if they have gotten the correct resultset.

There is literally no reason whatsoever to ever use the first form (there are no gains in using this form and several minuses). I would rewrite it anytime I saw it and never write new code using it.

satyajit · Answer 4 · 2011-07-22T19:25:01.510

There is another way too:

select * from person p join mytlc m on p.personid = m.personid and p.personid=3635

The first approach and the above approach will have same performance just an old and new way of writing queries.Writing joins with ON is an ANSI style. Thats it...

Now you can join extra conditions either to where clause or to the same ON clause after the join condition.Here comes performance into picture and depending upon your query results, the decision is made.

When to use a JOIN for MySQL (SQL)

4 Answers4