Difference between conventional SELECT and INNER JOIN

Asked

Viewed 50 times

3

Model of the structure of the tables:

Tabela: Bancos
Cod
Nome

Tabela: OperacoesBanco
Cod
CodBanco
ValorOp
Historico

Well I have these two models of SELECT:

Example 1

SELECT
 b.Nome,
 o.ValorOp,
 o.Historico
FROM
Bancos b,
 OperacoesBanco o
WHERE
 b.Cod = o.CodBanco

Example 2

SELECT
 *
FROM
 Bancos
INNER JOIN
 OperacoesBanco
WHERE
 Bancos.Cod = OperacoesBanco.CodBanco

Good these two models of SELECT work and return me the same answer. What I want to know is whether they have a performance difference in a large comic book or how best to use them.

Summarizing which would be the best?

mysql sql mysqli
  • 3

    I do not believe that there is any difference in performance, even because the optimisers will take care of this aspect. I prefer the option by spelling out INNER JOIN to make it easier for humans to read (explicit vs implicit). Anyway you can eliminate your doubt by evaluating the query plan generated by ANALYZE for both.

1 answer

3


This depends on the bank engine. Semantically speaking, the JOIN is clearer, but from the performance point of view you need to see the query execution plan.

I set up this table from your example and extract the execution plan from both queries and the result was the same:

Schema (Mysql v5.7)

create table Bancos (
  Cod int primary key,
  Nome varchar(50)
);

create table OperacoesBanco (
  Cod int primary key,
  CodBanco int references Bancos(Cod),
  ValorOp float,
  Historico varchar(100)
);

Query #1

    explain SELECT
     b.Nome,
     o.ValorOp,
     o.Historico
    FROM
    Bancos b,
     OperacoesBanco o
    WHERE
     b.Cod = o.CodBanco;

| id  | select_type | table | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra       |
| --- | ----------- | ----- | ---------- | ------ | ------------- | ------- | ------- | --------------- | ---- | -------- | ----------- |
| 1   | SIMPLE      | o     |            | ALL    |               |         |         |                 | 5    | 100      | Using where |
| 1   | SIMPLE      | b     |            | eq_ref | PRIMARY       | PRIMARY | 4       | test.o.CodBanco | 1    | 100      |             |

Query #2

explain SELECT
 *
FROM
 Bancos
INNER JOIN
 OperacoesBanco
WHERE
 Bancos.Cod = OperacoesBanco.CodBanco;

    | id  | select_type | table          | partitions | type   | possible_keys | key     | key_len | ref                          | rows | filtered | Extra       |
    | --- | ----------- | -------------- | ---------- | ------ | ------------- | ------- | ------- | ---------------------------- | ---- | -------- | ----------- |
    | 1   | SIMPLE      | OperacoesBanco |            | ALL    |               |         |         |                              | 5    | 100      | Using where |
    | 1   | SIMPLE      | Bancos         |            | eq_ref | PRIMARY       | PRIMARY | 4       | test.OperacoesBanco.CodBanco | 1    | 100      |             |

So for this table model, the result is the same from the point of view of plan and performance. Of course, when adding other criteria this can change according to how the engine of the bank, but simply analyzing the link of the tables, for the mysql, the performance will be the same.

Ran and exported from DB Fiddle: https://www.db-fiddle.com/

Browser other questions tagged mysql sql mysqli

You are not signed in. Login or sign up in order to post.