SUBSELECT vs. INNER JOIN
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' )
3 answers
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 would theoretically have to be executed every record of the main query while the join table would be handled differently. Which leads me to think that join is more performative. 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: http://technet.microsoft.com/en-us/library/ms189575 (V = sql.105).aspx When the queries are equivalent there is no difference in performance. But when the existence condition (EXISTS) has to be checked every record of the main query, the join has better performance.
In your case, an error may occur if the subquery returns more than one record. Unless you use operator "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 can 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.
According to the default **Ansi 92** the correct is to use inner join instead of sub-query.
Not so much for performance but for Standardization. But I believe that the SQL engine is a bit more optimized for inner join than other syntaxes.
Depends a lot on the purpose of your query, but remember that every record sought as comparative in your query with the subquery is broken 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".