等号 (=) 和 IN 与子查询的区别 [英] Differences between equal sign(=) and IN with subquery
本文介绍了等号 (=) 和 IN 与子查询的区别的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个查询需要 20 秒才能执行,请按照我的查询操作:
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)、( = ALL ) 或 ( = ANY ) 改变相等的信号,它会将成本降低到 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' )
它们之间有什么区别?
谢谢.
推荐答案
有一个小的语义差异.如果子查询匹配多个记录,则第一个查询必须失败.所以它必须完成子查询直到结束:
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)
这篇关于等号 (=) 和 IN 与子查询的区别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文