Difference Between Using "FROM Table, Table2" X "Join Table ON Table2"

Question

Difference Between Using "FROM Table, Table2" X "Join Table ON Table2"

Navigation

#1 by (2 votes)

6

Considering the two forms of joining tables with a join (any):

SELECT Tabela.A, Tabela.B, Tabela2.C 
FROM Tabela
LEFT JOIN Tabela2 ON Tabela.Id = Tabela2.TabelaId

And a union using from

SELECT Tabela.A, Tabela.B, Tabela2.C 
FROM Tabela, Tabela2
WHERE Tabela.Id = Tabela2.TabelaId

Since the data to be returned is the same.

In one of the querys that run in my application, when using union through from instead of join the performance difference is very high.

Marriage with from: 8s
Join Join: 0.2s

What is the difference between these unions? When to use one instead of the other?

mysql sql join

asked by anonymous 06.04.2017 / 14:47

1 answer

Executing command with another user inside a shell script How to create an alias to execute multiple commands with git?

score 2 · Accepted Answer

When doing this query, and we use LEFT JOIN , it returns the tabela integer and only the records that match the join equality in the tabela2 (or null fields for the unmatched fields):

SELECT Tabela.A, Tabela.B, Tabela2.C 
FROM Tabela
LEFT JOIN Tabela2 ON Tabela.Id = Tabela2.TabelaId

In MySQL, JOIN , CROSS JOIN , and INNER JOIN are syntactic equivalents (which can replace each other). In standard SQL, they are not equivalents . INNER JOIN is used with a ON in the clause, CROSS JOIN is used otherwise.
SOURCE: Documentation - JOIN Syntax

When using the tables in the where clause, we are making a simple JOIN or INNER | CROSS JOIN , with its second example, it would be in one of the following ways:

SELECT Tabela.A, Tabela.B, Tabela2.C 
  FROM Tabela
  JOIN Tabela2 ON Tabela.Id = Tabela2.TabelaId


SELECT Tabela.A, Tabela.B, Tabela2.C 
  FROM Tabela
  INNER JOIN Tabela2 ON Tabela.Id = Tabela2.TabelaId


SELECT Tabela.A, Tabela.B, Tabela2.C 
  FROM Tabela
  CROSS JOIN Tabela2 ON Tabela.Id = Tabela2.TabelaId