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' )
The mystery of how SqlServer works internally is difficult to solve.
It is quite possible that in some cases the difference is only in the syntax and the Sql Server operates in the same way.
But the subquery would theoretically have to be executed with every record of the main query while the join table would be treated differently. Which makes me think that the join is more performative. But, according to the link below, there is no performance difference when the queries are equivalent. (As in the case of your example)
See in: link When queries are equivalent there is no difference in performance. But when the existence condition (EXISTS) has to be checked with each record of the main query, the join performs better.
In your case, an error might occur if the subquery returns more than one record. Unless you use the "IN" operator
SELECT funcionarios.nome
FROM funcionarios
WHERE empresa_id IN ( SELECT id
FROM empresas
WHERE nome = 'Nome da Empresa' )
In a large, complex query, subquery can make the query harder to read. But it is indispensable in other cases.
I only use subquery when the thing can not be done with Join.
According to the **Ansi 92** pattern the correct one is to use inner join instead of sub-query.
Not so much by performance but by standardization. But I believe the SQL engine is a bit more optimized for the inner join than other syntaxes.
It depends a lot on the purpose of your query, but remember that when you search the comparative query in your query with the subquery, the same procedure is repeated again, now with an inner join it brings the two tables together and then makes the comparisons of the values displayed in the "ON".