mysql – What is the difference between joining tables by JOIN and WHERE?

Question:

What is the difference between joining tables by JOIN or by WHERE ? Examples:

SELECT * FROM clientes c JOIN enderecos e ON c.id = e.id_cliente;

SELECT * FROM clientes c, enderecos e WHERE c.id = e.id_cliente;

Is this just a matter of better organization of the code or is there any difference regarding the performance of the query as well?

Answer:

There is no difference, algebraically the queries are identical and will have the same performance.

Your query below is written in ANSI 89 standard.

SELECT * FROM clientes c, enderecos e WHERE c.id = e.id_cliente;

The same query written in ANSI 92 standard

SELECT * FROM clientes c JOIN enderecos e ON c.id = e.id_cliente;

Personally I prefer to use the ANSI 92 standard for a few reasons:

  • More readable , with the join criteria separate from the WHERE clause, as it is not known at first glance whether conditions in the WHERE clause are filters or joins.
  • Less likely to lose union criteria , in the first query if we don't specify the criteria in the WHERE clause the result will be the Cartesian product between the customer, address tables
  • Evolution , if the ANSI 92 standard specifies specific join operators, why not use them?
  • Flexibility , a join in a WHERE clause that has an effect of INNER JOIN and later needs to be changed to OUTER can be much more complicated
Post Views: 0
Scroll to Top