Difference between INNER JOIN and WHERE?

Question

First request:

Select * from table1 inner join table2 on table1.Id = table2.Id 

Second request:

 Select * from table1, table2 where table1.Id = table2.Id 

What is the difference between these performance queries that should be used?

Answer 1

The two statements you posted are logically identical. In fact, there is no practical reason to prefer one over the other, it is mainly a matter of personal style and readability. Some people prefer the INNER JOIN syntax ; some prefer just using WHERE .

Link to Using Internal Connections :

In the ISO standard, internal joins can be specified either in FROM or in WHERE. This is the only type of join that ISO supports in the WHERE clause. The internal connections indicated in the WHERE clause are known as old-fashioned internal connections.

Referring to Combine Fundamentals :

Specifying join conditions in FROM allows you to separate them from any other search conditions that may be specified in the WHERE clause and the recommended method for specifying a join.

Persons, I prefer using INNER JOIN . I find it clearer, since I can separate the join conditions from the filter conditions and use a separate join block for each joined table.

Answer 2

To reinforce @Akram’s answer, many prefer the inner join syntax because it makes it easier to distinguish join conditions (how different tables in the FROM clause relate to each other) from filter conditions (those conditions that should be used to reduce the overall result set. In this there is no difference between them, but with larger queries, with a large number of tables, this can improve readability for using the inner join form.

In addition, as soon as you start looking at outer joins, you really need to use the infix left outer join syntax ( left outer join , right outer join ), so many find a symmetry form in using the same style for inner join . There is an older deprecated syntax for performing external joins in the WHERE (using *= ), but support for such joins is dying out.