MySQL joins records from two tables with and without join

Question

What is the difference between these two queries:

SELECT `threads`.`id` AS `threads.id` , `posts`.`id` , `posts`.`content` FROM `threads` JOIN `posts` ON `threads`.`id` = `posts`.`thread_id` 

and

 SELECT `threads`.`id` AS `threads.id` , `posts`.`id` , `posts`.`content` FROM `threads` , `posts` WHERE `threads`.`id` = `posts`.`thread_id` 

Both of them return the same data.

Answer 1

WHERE set of sentence filtering results returned by JOIN , so this is the difference.

As long as you use the INNER JOIN , there is no difference in performance or execution plan; in the case of any OUTER JOIN request, a different execution plan appears.

Also pay attention to what is said in the MySql online doc :

Typically, you should use the ON clause for conditions that indicate how to join the tables, and the WHERE clause to limit which rows you want in the result set.

Answer 2

One uses ANSI Joins, the other uses a pre-ansi connection. MOST DB engines compile them into the same execution plan.

Answer 3

In a word: readability.

Running the following code:

 create table #threads ( id int ) create table #posts ( id int, thread_id int, content varchar(10) ) insert into #threads values (1) insert into #threads values (2) insert into #posts values (1, 1, 'Stack') insert into #posts values (2, 2, 'OverFlow') SELECT #threads.id AS 'threads.id' , #posts.id , #posts.content FROM #threads JOIN #posts ON #threads.id = #posts.thread_id SELECT #threads.id AS 'threads.id' , #posts.id , #posts.content FROM #threads, #posts WHERE #threads.id = #posts.thread_id drop table #threads drop table #posts 

at http://data.stackexchange.com/stackoverflow/query/new you will get the same execution plan :)

The only real difference is that inner join ANSI , and from #threads, #posts is the Transact-SQL syntax.

Answer 4

There may be a difference in the internal implementation of these queries, but I doubt it. I would prefer JOIN because it makes the execution plan more obvious.