Differences between equal sign(=) and IN with subquery
sql-server, sql-server-2012
Solution
There is a small semantic difference. The first query must fail if the subquery matches more than one record. So it has to finish the subquery until the end:
where col1 = (select col1 from table2)
The second query can stop once it encounters a match:
where col1 in (select col1 from table2)
Problem
I have a query it takes 20 seconds to execute, follow my query: ``` SELECT MATLIGA.COD_MAT_FAMILIA FROM ORCAMENTOS.dbo.OR_1INSUMOS INSUMOS INNER JOIN ORCAMENTOS.dbo.OR_MAT_GRUPOS GRUPOS ON (GRUPOS.EMPRESA='01' AND GRUPOS.FILIAL='01' AND GRUPOS.CODIGO_INTERNO = 'HOT' ) INNER JOIN ORCAMENTOS.dbo.OR_MATERIAIS MATER ON (MATER.EMPRESA='01' AND MATER.FILIAL='01' AND MATER.CODIGO_GRUPO=GRUPOS.ID AND MATER.ID = INSUMOS.COD_INSUMO_MATER ) INNER JOIN ORCAMENTOS.dbo.OR_MAT_LIGACAO MATLIGA ON (MATLIGA.EMPRESA='01' AND MATLIGA.FILIAL='01' AND MATLIGA.CODIGO_MATERIAL = INSUMOS.COD_INSUMO_MATER) WHERE INSUMOS.EMPRESA='01' AND INSUMOS.FILIAL='01' AND INSUMOS.COD_INSUMO_MATER IS NOT NULL AND INSUMOS.NUMERO=10865812 AND INSUMOS.OPCAO_SIMULACAO=1 AND INSUMOS.CODIGO_MAQUINA = (SELECT COD_MAQ_PROPOSTA FROM ORCAMENTOS.dbo.OR_1SIMULACOES AS ORC WHERE ORC.NUMERO=10865812 AND ORC.OPCAO_SIMULACAO = 1 AND ORC.EMPRESA='01' AND ORC.FILIAL='01' ) AND INSUMOS.OPCAO_MAQUINA = (SELECT OPCAO_MAQUINA FROM ORCAMENTOS.dbo.OR_1SIMULACOES AS ORC WHERE ORC.NUMERO=10865812 AND ORC.OPCAO_SIMULACAO = 1 AND ORC.EMPRESA='01' AND ORC.FILIAL='01' ) GROUP BY MATLIGA.COD_MAT_FAMILIA ORDER BY 1 ``` In these two lines bellow, if I change the equal signal by (IN), ( = ALL ) or ( = ANY ) it reduces the costs to 1 second. ``` AND INSUMOS.CODIGO_MAQUINA IN (SELECT COD_MAQ_PROPOSTA FROM ORCAMENTOS.dbo.OR_1SIMULACOES AS ORC WHERE ORC.NUMERO=10865812 AND ORC.OPCAO_SIMULACAO = 1 AND ORC.EMPRESA='01' AND ORC.FILIAL='01' ) AND INSUMOS.OPCAO_MAQUINA IN (SELECT OPCAO_MAQUINA FROM ORCAMENTOS.dbo.OR_1SIMULACOES AS ORC WHERE ORC.NUMERO=10865812 AND ORC.OPCAO_SIMULACAO = 1 AND ORC.EMPRESA='01' AND ORC.FILIAL='01' ) ``` Whats the difference between them? Tks.