What is the difference between comma separated joins and syntax joins in MySQL?

Question

For example, if I had a Man table with an id column that references the id column in the Work table

What is the difference between these two queries? They give the same results.

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

and

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

thank

Answer 1

There is no difference .

The second view makes the request more readable and makes it very clear which connection matches which condition.

Answer 2

Queries are logically equivalent. The comma operator is equivalent to the [INNER] JOIN .

The comma is the senior operator of style union. The JOIN keyword was added later, and preference is given because it also allows OUTER join operations.

It also allows you to isolate join predicates (conditions) from the WHERE in the ON WHERE . This improves readability.

Answer 3

Beyond better readability, there is another case where explicitly joined tables are better than comma-separated tables.

see 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 ) 

The following query will give me all the columns and rows from both tables

 SELECT * FROM table1, table2 

The following query will give me columns from the first table with a table alias called "table2"

 SELECT * FROM table1 table2 

If you mistakenly forget a comma in a compound separated by commas, the second table will automatically convert to a table alias for the first table. Not in all cases, but there is a chance of something like this.

Answer 4

Using JOINS makes code reading easier because it is self-evident.

There is no difference in speed (I tested it), and the execution plan is the same

If the query optimizer does its job correctly, there should be no difference between the queries. These are just two ways to indicate the same desired result.

Answer 5

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

The JOIN syntax makes it explicit what criteria affect the tables (giving the condition). In addition, the second method is an older standard.

Although in the database they become the same