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

Asked

Viewed 130 times

6

Considering the two forms of union of tables through a join(anyone):

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 are the same.

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

  • Union with from: 8s
  • Union with Join: 0.2s

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

mysql sql join

  • I think you should always use JOIN when you need information from two or more tables that relate, JOIN should be optimized precisely for this.

  • Behold that answer. In your example, the first is a LEFT JOIN the second is a CROSS JOIN

  • @Pagotti a union with FROM is equivalent to a Cross Join ?

  • 1

    @LP.Gonçalves It’s called cross implicit Join

  • 1

    @Pagotti is not a CROSS JOIN, for that he could not have the WHERE, due to WHERE Tabela.Id = Tabela2.TabelaId he becomes a INNER JOIN

  • @Leandrogodoyrosa The joins have to do with the clase FROM. The clause WHERE has another function in the SQL. The final result of the query is the same, but the way it behaves within the optimizer is different. Look for "Relational Algebra" to better understand these differences. There this interesting online tool that shows how SQL works internally.

  • @Pagotti please refer to the Wikipédia page you linked on the INNER JOIN implicit, you will see that as much as it is technically the same as making a CROSS JOIN, due to the filter by the clause WHERE the result is the same of a INNER JOIN, and in modern databases what will be done is a INNER JOIN, test see the SQL Server execution plan for example, it may be that in the past it had difference, nowadays it has practically no

Show 2 more comments

1 answer

2


In doing so, and we use the LEFT JOIN, returns to tabela and only those records that match the equality of the join in tabela2 (or null fields for 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 the default 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 link in the clause where, we’re making a simple JOIN or INNER | CROSS JOIN, with his second example, would be in one of the forms:

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

Browser other questions tagged mysql sql join

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