Difference between these two joining table approaches?

Question

Consider we have two tables, Users and Posts. user_id is the foreign key in Posts table and is primary key in Users table.

Whats the difference between these two sql queries?

select user.name, post.title 
  from users as user, posts as post 
 where post.user_id = user.user_id;

vs.

select user.name, post.title 
  from users as user join posts as post using user_id;

Answer 1

Other than syntax, for the small snippet, they work exactly the same. But if at all possible, always write new queries using ANSI-JOINs.

As for semantically, the comma notation is used to produce a CARTESIAN product between two tables, which means produce a matrix of all records from table A with all records from table B, so two tables with 4 and 6 records respectively produces 24 records. Using the WHERE clause, you can then pick the rows you actually want from this cartesian product. However, MySQL doesn't actually follow through and make this huge matrix, but semantically this is what it means.

A JOIN syntax is the ANSI standard that more clearly defines how tables interact. By putting the ON clause next to the JOIN, it makes it clear what links the two tables together.

Functionally, they will perform the same for your two queries. The difference comes in when you start using other [OUTER] JOIN types.

For MySQL specifically, comma-notation does have one difference

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

However, it would not be wise to bank on this difference.