What are the difference between these two SQL queries?

Question

What are the differences between these two SQL queries?

Query #1:

select mytab.name, mytab.age, films.title, films.author 
from films, mytab 
where films.id = mytab.id;

Query #2:

select mytab.name, mytab.age, films.title, films.author 
from films inner join mytab 
on films.id = mytab.id;

First is a normal SQL query using 'where' statement. The second is using inner join. The result of both queries is exactly the same.

films -> id, title author
mytab -> id, name, age

It`s the poorest example as is possible.

Please note that there is no difference in execution speed, because people sure seem to worry about that a lot. — Andy Lester, Jan 19 '14 at 02:35
They are equivalent and (should) result in identical query plans. — wildplasser, Jan 19 '14 at 10:29

Mitch Wheat · Answer 1 · 2014-01-19T01:49:13.293

2

They are logically equivalent, and will produce the same results.

The first uses older join syntax.

Second uses the ANSI-92 join syntax, and is the preferred style.

edited Jan 19 '14 at 01:49

answered Jan 19 '14 at 01:32

Mitch Wheat

score 2 · Accepted Answer · edited May 23 '17 at 10:31

2

Both Queries will yield same results but the only difference is in syntax,

1st query uses old syntax for join where you describe the relation in WHERE clause .

2nd Query uses newer ANSI syntax, where relation between tables are defined in ON clause.

Second syntax is preferred though.

Read Here for more information.

edited May 23 '17 at 10:31

Community

answered Jan 19 '14 at 01:36

M.Ali

2 Answers2