What's the difference between comma separated joins and join on syntax in MySQL?


Mysql Problem Overview

For example if I were to have a table "Person" with a column "id" that references a column "id" in table "Worker"

What would the difference between these two queries be? They yield the same results.

FROM Person 
JOIN Worker 
  ON Person.id = Worker.id;


FROM Person, 
WHERE Person.id = Worker.id;


Mysql Solutions

Solution 1 - Mysql

There is no difference at all.

First representation makes query more readable and makes it look very clear as to which join corresponds to which condition.

Solution 2 - Mysql

The queries are logically equivalent. The comma operator is equivalent to an [INNER] JOIN operator.

The comma is the older style join operator. The JOIN keyword was added later, and is favored because it also allows for OUTER join operations.

It also allows for the join predicates (conditions) to be separated from the WHERE clause into an ON clause. That improves (human) readability.


This answer says that the two queries in the question are equivalent. We shouldn't mix old-school comma syntax for join operation with the newer JOIN keyword syntax in the same query. If we do mix them, we need to be aware of a difference in the order of precedence.

excerpt from MySQL Reference Manual


> INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table). > > 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. Information about dealing with this problem is given later in this section.

Solution 3 - Mysql

Beside better readability, there is one more case where explicitly joined tables are better instead of comma-separated tables.

let's see an example:

Create Table table1
    ID int NOT NULL Identity(1, 1) PRIMARY KEY ,
    Name varchar(50)

Create Table table2
    ID int NOT NULL Identity(1, 1) PRIMARY KEY ,
    ID_Table1 INT NOT NULL

Following query will give me all columns and rows from both tables

FROM table1, table2

Following query will give me columns from first table with table alias called 'table2'

FROM table1 table2

If you mistakenly forget comma in comma-separated join, second table automatically convert to table alias for first table. Not in all cases, but there is chances for something like this

Solution 4 - Mysql

Using JOINS makes the code easier to read, since it's self-explanatory.

In speed there is no difference (I tested it) and the execution plan is the same

If the query optimizer is doing its job right, there should be no difference between those queries. They are just two ways to specify the same desired result.

Solution 5 - Mysql

The SELECT * FROM table1, table2, etc. is good for a couple of tables, but it becomes exponentially harder as the number of tables increases.

The JOIN syntax makes it explicit what criteria affects which tables (giving a condition). Also, the second way is the older standard.

Although, to the database, they end up being the same


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionBig MoneyView Question on Stackoverflow
Solution 1 - MysqlSateesh PagoluView Answer on Stackoverflow
Solution 2 - Mysqlspencer7593View Answer on Stackoverflow
Solution 3 - MysqlveljasijeView Answer on Stackoverflow
Solution 4 - MysqlvhadalgiView Answer on Stackoverflow
Solution 5 - MysqlVinayak PahalwanView Answer on Stackoverflow