sql-server - 等号 (=) 和 IN 与子查询的区别

Question

我有一个查询需要 20 秒才能执行，请按照我的查询操作:

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)、( = ALL ) 或 ( = ANY ) 更改相等信号，它将成本降低到 1 秒。

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' )  

它们之间有什么区别？

Tks。

Answer 1

有一个小的语义差异。如果子查询匹配多个记录，则第一个查询必须失败。所以它必须完成子查询直到结束:

where col1 = (select col1 from table2)

一旦遇到匹配项，第二个查询就可以停止:

where col1 in (select col1 from table2)