SQL中的何處和有子句之間有什麼區別？

WHERE和HAVING的主要區別在於過濾時機：1.WHERE在分組前過濾行，作用於原始數據，不能使用聚合函數；2.HAVING在分組後過濾結果，作用於聚合後的數據，可以使用聚合函數。例如查詢中先用WHERE篩選高薪員工再分組統計，再用HAVING篩選平均薪資超6萬的部門時，兩者順序不可調換，WHERE始終先執行，確保僅符合條件的行參與分組，HAVING則根據分組結果進一步過濾最終輸出。

The difference between WHERE and HAVING in SQL comes down to when each filter is applied — especially in relation to grouping. In short:

• WHERE filters rows before they are grouped.
• HAVING filters groups or aggregated results after the grouping is done.

If you're using GROUP BY , that's where this distinction really matters.

Filtering Before Grouping with WHERE

Use WHERE when you want to narrow down which rows go into the grouping process. It works on individual rows, not aggregated values.

For example:

 SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE salary > 50000
GROUP BY department;

Here, only employees earning more than $50k are included before the database groups them by department. The filtering happens early, so it affects the final counts.

You can't use aggregate functions like COUNT() , SUM() , etc., in a WHERE clause — because those values don't exist yet at that stage of the query.

Key points:

• Filters raw data.
• Cannot reference aggregate functions.
• Runs before grouping.

Filtering After Grouping with HAVING

Once you've grouped your data using GROUP BY , you might want to filter based on the result of an aggregation — that's where HAVING steps in.

Example:

 SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

This query returns only departments with more than 10 employees. The count is calculated first, then filtered.

Unlike WHERE , HAVING can include aggregate functions. You can also reference column aliases defined in the SELECT .

Common uses:

• Filter based on group size ( COUNT , SUM , etc.)
• Compare aggregated values (eg, HAVING AVG(salary) > 60000 )
• Use with or without GROUP BY

When to Use Which?

To decide between WHERE and HAVING , ask yourself:

• Do I need to filter individual rows before grouping? → Use WHERE .
• Do I need to filter groups or aggregated results after grouping? → Use HAVING .

Sometimes both can be used together:

 SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING AVG(salary) > 60000;

In this case:

• WHERE narrows the dataset to active employees only.
• HAVING further filters the resulting groups to show only those with an average salary over $60k.

So basically, it's about timing: WHERE filters early, HAVING filters late — especially useful after aggregations.

以上是SQL中的何處和有子句之間有什麼區別？的詳細內容。更多資訊請關注PHP中文網其他相關文章！