17

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;
philipxy
  • 14,867
  • 6
  • 39
  • 83
Mr Coder
  • 8,169
  • 5
  • 45
  • 74

4 Answers4

16

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.

Community
  • 1
  • 1
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • In MySQL, there is a subtle difference between `,` (== `STRAIGHT_JOIN`) and `JOIN`. – Ted Hopp Mar 14 '11 at 03:18
  • so comma-notation is same as inner join ? and will Cartesian product take more resources then join on ?? – Mr Coder Mar 14 '11 at 03:35
  • @jason [cartesian product](http://en.wikipedia.org/wiki/Cartesian_product) is a mathematical operation between two sets, used to describe the SET-based operation in SQL. Your two queries are for all intensive purposes the same, so neither will take more resources – RichardTheKiwi Mar 14 '11 at 03:37
  • MySQL will notice the WHERE clause and not do any more work than it has to. It's _equivalent_ to a full Cartesian product followed by a WHERE filter, but that's not what it actually does. This is all detailed in the [JOIN Syntax section](http://dev.mysql.com/doc/refman/5.5/en/join.html) of the MySQL manual. [What @Richard said while I was writing this. :)] – Ted Hopp Mar 14 '11 at 03:40
  • I will like to learn how sql query gets interpreted , which part of query has higher preference and get executed first any links to such resources would be helpful thanks . – Mr Coder Mar 14 '11 at 03:49
  • As long as the result is equivalent, theoretically the arrangements of the parts is inconsequential. Any difference exposes a failure of the query optimizer (that will hopefully be detected and corrected in the next version.) That's one of the consequences of SQL being a declarative language rather than a procedural language. – dkretz Mar 14 '11 at 04:41
  • `,` is not `straight_join`. – philipxy Oct 17 '18 at 11:37
4
where post.user_id = user.user_id

Here you are making a conditional statement

from users as user join posts as post using user_id

Here you are joining two tables using the foreign key

At the end is just the same but JOIN is better used for more advanced queries...

brisonela
  • 125
  • 2
  • 2
  • 10
3

In MySQL JOIN syntax, CROSS JOIN, INNER JOIN, and JOIN are all the same. A comma-separated table list is a JOIN.

Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
0

The MySQL manual on page https://dev.mysql.com/doc/refman/5.5/en/join.html makes this point about the difference between the two approaches:

However, the precedence of the comma operator is less than that of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur.

Captain Payalytic
  • 1,061
  • 8
  • 9