SUBSELECT vs. INNER JOIN

Question

Is there any recommendation on which of the two is preferable in terms of performance?

SELECT funcionarios.nome
FROM funcionarios
INNER JOIN empresas
  ON empresas.id = funcionarios.empresa_id
WHERE empresas.nome = 'Nome da Empresa'

or

SELECT funcionarios.nome
FROM funcionarios
WHERE empresa_id = ( SELECT id
                     FROM empresas
                     WHERE nome = 'Nome da Empresa' )

Answer 1

The mystery of how Sqlserver operates internally is difficult to solve.

It is quite possible that in some cases the difference is only in syntax and Sql Server operates in the same way.

But the subquery, theoretically, would have to be executed on each record of the main query while the Join table would be treated differently. Which makes me think Join is more performance-oriented. But, according to the link below, there is no difference in performance when the queries are equivalent. (As in the case of your example)

See in: http://technet.microsoft.com/en-us/library/ms189575(v=sql.105). aspx When the consultations are equivalent there is no difference in performance. But when the condition of existence (EXISTS) has to be checked with each record of the main query, Join has better performance.

In your case, an error may occur if the subquery returns more than one record. Unless you use "IN"

SELECT funcionarios.nome
FROM funcionarios
WHERE empresa_id IN ( SELECT id
                     FROM empresas
                     WHERE nome = 'Nome da Empresa' )

In a large and complex query, subquery may make the query more difficult to read. But it is indispensable in other cases.

I only use subquery when the thing cannot be done with Join.

Answer 2

According to the standard **Ansi 92** the correct is to use inner join instead of sub-query.

Not so much for performance as for standardization. But I believe that the SQL engine is a little more optimized for Inner Join than other syntaxes.

Answer 3

It depends a lot on the purpose of your query, but remember that the searched record as comparative in your query with the subquery is again the same procedure, now with an Inner Join it brings the two tables and then makes the comparisons of the values presented in the "ON".