2

Lets say I have two tables student, records with the schema being

Students (id, name)
Records (rid,sid,subject,marks)

and I want to print (name, subject,marks).

So I can write the inner join in two ways

> select a.name,b.subject,b.marks from students a, records b where a.id = b.sid;
or
> select a.name,b.subject,b.marks from students a inner join records b on a.id = b.sid;

Obviously, they both are returning the same results and taking same amount of time. So I am not sure if internally they both are same or if there is any scenario where either of those is preferable over the other?

Max
  • 9,100
  • 25
  • 72
  • 109
  • There is no difference. See: http://stackoverflow.com/questions/565620/difference-between-join-and-inner-join – Alex Oct 12 '14 at 17:00
  • possible duplicate of [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) – Andriy M Mar 13 '15 at 08:15

2 Answers2

1

They are the same and are executed the same way. Do a 

EXPLAIN EXTENDED SELECT ...

on your queries and enable the warnings. Then mySQL will give you a warning containing the query after the optimizer had it. There should be the same warning for both queries.

Benvorth
  • 7,416
  • 8
  • 49
  • 70
1

Both are wrong. I assume this is a mistake, and the first where is supposed to be a from:

> select a.name,b.subject,b.marks from students a, records b where a.id = b.sid;
or
> select a.name,b.subject,b.marks from students a inner join records b on a.id = b.sid;

If we disregard this mistake and examine the queries above - these two statements are functionally equivalent, but implicit joins (the first form) has been deprecated for a long while. Hence, it's suggested to use explicit joins (the second form). An added bonus to them is the increased readability of the code - the join conditions are neatly arranged with the joins, and the where clause is left free to handle just the logic of the query.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • I obviously wrote dummy queries for the purpose of this question. But how are queries wrong? – Max Oct 12 '14 at 17:11
  • They have an additional `where` clause instead of a `from` clause, as I stated - check out the corrected queries in my answer. – Mureinik Oct 12 '14 at 17:13