WHERE vs HAVING mental model in SQL

Introduction

We use WHERE and HAVING to filter data in a database. WHERE filters rows before grouping, and HAVING filters groups after grouping.

When you want to select only rows that meet a condition before grouping data.

When you want to filter groups created by GROUP BY based on an aggregate condition.

When you need to exclude certain rows from aggregation calculations.

When you want to show only groups with a total count or sum above a certain number.

When you want to combine filtering of individual rows and groups in one query.

Syntax

SQL

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1
HAVING aggregate_condition;

WHERE filters rows before grouping.

HAVING filters groups after grouping.

Examples

This query selects departments with more than 5 employees who earn over 50,000.

SQL

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

This query shows categories where total sales exceed 10,000.

SQL

SELECT category, SUM(sales)
FROM sales_data
GROUP BY category
HAVING SUM(sales) > 10000;

This query selects all products priced below 20 before any grouping.

SQL

SELECT * FROM products
WHERE price < 20;

Sample Program

This example filters sales rows with amount > 60 first, then groups by region, and finally shows only regions with total sales above 200.

SQL

CREATE TABLE sales (
  product VARCHAR(20),
  region VARCHAR(20),
  amount INT
);

INSERT INTO sales VALUES
('Pen', 'East', 100),
('Pen', 'West', 150),
('Pencil', 'East', 50),
('Pencil', 'West', 70),
('Notebook', 'East', 200),
('Notebook', 'West', 180);

SELECT region, SUM(amount) AS total_sales
FROM sales
WHERE amount > 60
GROUP BY region
HAVING SUM(amount) > 200;

Important Notes

WHERE cannot use aggregate functions like SUM or COUNT.

HAVING is used only with GROUP BY or aggregate functions.

Think of WHERE as a filter on raw data, HAVING as a filter on grouped data.

Summary

WHERE filters rows before grouping.

HAVING filters groups after grouping.

Use WHERE for simple conditions, HAVING for conditions on aggregates.