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

<>

This question already has an answer here:

Answers


There is no difference at all.

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


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.


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


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.


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


Need Your Help

Picking up a VB6 project for a non-VB6 guy

.net sql-server vb6

I have been given the task of modifying a VB6 project. Nothing incredibly serious, adding a couple forms and fixing a few bugs for the most part. The project uses SQL Server (if that is of any rele...

How do I get a list of domain user accounts with win32 api?

c++ winapi active-directory

How do I get a list of domain user accounts with win32 api?