哪个 SQL 语句更快?(拥有与在哪里......) [英] 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 上，仅在不能将条件放在 WHERE 上的情况下使用 HAVING(如某些 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 语句更快?(拥有与在哪里......)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！