哪个SQL语句更快？ （HAVING vs. WHERE ......） [英] Which SQL statement is faster? (HAVING vs. WHERE...)

问题描述

SELECT NR_DZIALU, COUNT (NR_DZIALU) AS LICZ_PRAC_DZIALU
    FROM  PRACOWNICY
    GROUP BY NR_DZIALU
    HAVING NR_DZIALU = 30

或

SELECT NR_DZIALU, COUNT (NR_DZIALU) AS LICZ_PRAC_DZIALU
    FROM PRACOWNICY
    WHERE NR_DZIALU = 30
    GROUP BY NR_DZIALU

推荐答案

理论（理论上我的意思是 SQL Standard ）说WHERE在返回行之前限制结果集，HAVING在带来所有行之后限制结果集。所以WHERE更快。在这方面符合SQL标准的DBMS，只使用HAVING，你不能把条件放在WHERE上（比如某些RDBMS中的计算列。）

The theory (by theory I mean SQL Standard) says that WHERE restricts the result set before returning rows and HAVING restricts the result set after bringing all the rows. So WHERE is faster. On SQL Standard compliant DBMSs in this regard, only use HAVING where you cannot put the condition on a WHERE (like computed columns in some RDBMSs.)

你可以看到两者的执行计划并自行检查，没有什么能超过它（根据您的数据在特定环境中测量您的特定查询。）

You can just see the execution plan for both and check for yourself, nothing will beat that (measurement for your specific query in your specific environment with your data.)

这篇关于哪个SQL语句更快？ （HAVING vs. WHERE ......）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！