What's the difference between comma separated joins and join on syntax in MySQL?
Solution 1
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
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.
FOLLOWUP
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
https://dev.mysql.com/doc/refman/5.6/en/join.html
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 formUnknown column 'col_name' in 'on clause'
may occur. Information about dealing with this problem is given later in this section.
Solution 3
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
SELECT
*
FROM table1, table2
Following query will give me columns from first table with table alias called 'table2'
SELECT
*
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
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
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
Big Money
Unity // Node C# // CoffeeScript // JavaScript // Go
Updated on November 25, 2020Comments
-
Big Money over 3 years
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.
SELECT * FROM Person JOIN Worker ON Person.id = Worker.id;
and
SELECT * FROM Person, Worker WHERE Person.id = Worker.id;
Thanks
-
Liam over 9 yearsIs there any performance difference? How about in PostgreSQL?
-
Sateesh Pagolu about 9 yearsThere is absolutely no difference in performance. Final version prepared by SQL Server would be the same in both the cases.
-
Gal over 8 yearsexponentially harder? how? and for who?
-
wobbily_col over 8 yearsFor larger queries I would say that the first way is more readable. If you have a mix left joins and some inner joins, then it is far more consistent to have the joins condition on the join clause, rather than some in the joins and some in the where clause.
-
Stephen York over 5 yearsThe first is way more readable and explicit
-
Captain Payalytic over 5 yearsThis answer is not correct. In the MySQL manual on the page dev.mysql.com/doc/refman/5.5/en/join.html it states: "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"
-
spencer7593 over 5 years@CaptainPayalytic: The two queries given in the question are logically equivalent. As you point out, there is actually a difference between the two notations. (I concur with you that this answer is wrong. There is, in fact, a difference between the two queries... we could say that one uses the old school comma syntax for the join operation, the other uses the JOIN keyword.)
-
philipxy over 5 yearscomma is CROSS JOIN or INNER JOIN ON 1=1. It has lower precedence than keyword joins. INNER JOIN ON is defined in terms of comma & WHERE.
-
youcantryreachingme over 5 yearsFurther to earlier comments, using the first format you can group all join conditions into
ON
clauses, leaving yourWHERE
clauses to specify filters for your data (with the caveat that it's possible to write join conditions that act as filters, but in general, keeping to this practice makes the code more readable). -
Matt Arnold about 5 yearsIf
table1
andtable2
are unrelated, I would rate theFROM
+ comma syntax here above the alternative and more verboseINNER JOIN table2 ON 1 = 1
since theON
predicate is redundant. -
spencer7593 almost 5 years@MattArnold : with MySQL, the
ON
clause is optional. theON 1=1
has no effect and is unnecessary. Also, the INNER keyword is optional, and has no effect. i.e.FROM table1 JOIN table2
is sufficient, although in this case, with no join condition in the ON or WHERE clause, we could include the optionalCROSS
keyword as an aid to the future reader. To help the reader comprehend that the absence of a join condition was intentional. i.e.FROM table1 CROSS JOIN table2
. I think this form communicates our intent more clearly than the comma syntax. -
Raymond Nijland over 4 yearsRun a
EXPLAIN <query>/SHOW WARNINGS
and you will see those queries optimizes/internally rewriten (notice the warning message) the same -> db-fiddle.com/f/us9ffeFw7vcMVt2WoNXQzm/0 -
Sнаđошƒаӽ about 4 years@Gal I think it is just an indian way of saying that comma separated table names can be written in a single line... ¯\_(ツ)_/¯ That's all I can make out of that statement, coz I am only guessing - only the author can know the meaning of that..
-
amy about 3 yearsAre they also the same in Oracle?
-
Jeremy Ray Brown about 3 yearsEasier to read is a great explanation. Code should always be written so it is easier to read. I agree with you 100%