WHERE和HAVING的主要區(qū)別在於過(guò)濾時(shí)機(jī)：1.WHERE在分組前過(guò)濾行，作用於原始數(shù)據(jù)，不能使用聚合函數(shù)；2.HAVING在分組後過(guò)濾結(jié)果，作用於聚合後的數(shù)據(jù)，可以使用聚合函數(shù)。例如查詢(xún)中先用WHERE篩選高薪員工再分組統(tǒng)計(jì)，再用HAVING篩選平均薪資超6萬(wàn)的部門(mén)時(shí)，兩者順序不可調(diào)換，WHERE始終先執(zhí)行，確保僅符合條件的行參與分組，HAVING則根據(jù)分組結(jié)果進(jìn)一步過(guò)濾最終輸出。

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中的何處和有子句之間有什麼區(qū)別？的詳細(xì)內(nèi)容。更多資訊請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！