Understanding SQL `WHERE` vs `HAVING`, practical guide for interviews

When preparing for SQL interviews, one of the most common questions revolves around the difference between WHERE and HAVING. While they may seem similar at first, they serve distinct purposes in filtering data. Understanding when and why to use each is crucial for writing correct and efficient SQL queries.

The Basic Difference

• WHERE filters individual rows before any grouping occurs. You cannot use aggregate functions like SUM, AVG, or COUNT in a WHERE clause.
• HAVING filters groups of rows after aggregation. This is where you use aggregate functions to filter grouped data.

Mental Example

Think of your data like a stack of papers:

• WHERE = “I only want the papers where the employee earns more than \$60k.” ✅ Individual row filter
• GROUP BY = “Now I will stack the papers by department.”
• HAVING = “I only want stacks where the total or average salary exceeds \$60k.” ✅ Group filter

---

Example Database Setup

Let's work with a practical example using a simplified company database:

CREATE DATABASE CompanyDB;
GO

USE CompanyDB;
GO

CREATE TABLE Employees (
    EmployeeId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE NOT NULL,
    HireDate DATE NOT NULL,
    DepartmentId INT,
    Salary DECIMAL(10,2) NOT NULL
);
GO

CREATE TABLE Departments (
    DepartmentId INT IDENTITY(1,1) PRIMARY KEY,
    DepartmentName NVARCHAR(50) NOT NULL,
    ManagerId INT NULL
);
GO

CREATE TABLE Projects (
    ProjectId INT IDENTITY(1,1) PRIMARY KEY,
    ProjectName NVARCHAR(100) NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NULL,
    Budget DECIMAL(12,2) NOT NULL
);
GO

CREATE TABLE EmployeeProjects (
    EmployeeId INT NOT NULL,
    ProjectId INT NOT NULL,
    AssignedDate DATE NOT NULL,
    Role NVARCHAR(50) NULL,
    PRIMARY KEY (EmployeeId, ProjectId),
    FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId),
    FOREIGN KEY (ProjectId) REFERENCES Projects(ProjectId)
);
GO

CREATE TABLE Cities (
    CityId INT IDENTITY(1,1) PRIMARY KEY,
    CityName NVARCHAR(100) NOT NULL,
    Country NVARCHAR(100) NOT NULL
);
GO

ALTER TABLE Employees
ADD CityId INT NULL,
FOREIGN KEY (CityId) REFERENCES Cities(CityId);
GO

INSERT INTO Departments (DepartmentName) VALUES
('Engineering'), ('HR'), ('Marketing'), ('Finance'), ('Operations');

INSERT INTO Cities (CityName, Country) VALUES
('New York', 'USA'), ('London', 'UK'), ('Berlin', 'Germany'), ('Tokyo', 'Japan'), ('Madrid', 'Spain');

INSERT INTO Employees (FirstName, LastName, Email, HireDate, DepartmentId, Salary, CityId) VALUES
('Alice','Smith','[e-mail]','2020-03-15',1,70000,1),
('Bob','Johnson','[e-mail]','2019-07-22',2,50000,2),
('Charlie','Brown','[e-mail]','2021-01-10',1,80000,3),
('Diana','King','[e-mail]','2018-11-05',3,60000,4),
('Ethan','White','[e-mail]','2022-06-20',4,55000,5);

INSERT INTO Projects (ProjectName, StartDate, EndDate, Budget) VALUES
('Project Apollo','2023-01-01','2023-12-31',500000),
('Project Zeus','2022-05-01','2023-04-30',300000),
('Project Hera','2023-03-01',NULL,200000),
('Project Poseidon','2023-06-15',NULL,150000);

INSERT INTO EmployeeProjects (EmployeeId, ProjectId, AssignedDate, Role) VALUES
(1,1,'2023-01-05','Lead Developer'),
(1,2,'2022-05-10','Developer'),
(2,2,'2022-05-12','HR Support'),
(3,3,'2023-03-10','Developer'),
(4,4,'2023-06-20','Marketing Lead'),
(5,1,'2023-01-15','Finance Analyst');
GO

---

Filtering Rows with WHERE

If you want employees earning more than \$60k:

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > 60000;

Explanation:

• Filters individual rows before any grouping.
• Result: Alice (70k) and Charlie (80k), assuming our sample data.

---

Grouping Data with HAVING

Suppose you want to find departments with more than one employee earning over \$60k:

SELECT DepartmentId, COUNT(EmployeeId) AS NumEmployees
FROM Employees
WHERE Salary > 60000
GROUP BY DepartmentId
HAVING COUNT(EmployeeId) > 1;

Explanation:

• WHERE Salary > 60000 → filters individual employees.
• GROUP BY DepartmentId → groups employees by department.
• HAVING COUNT(EmployeeId) > 1 → filters only departments with more than 1 employee meeting the condition.

Tip: If this query returns no rows, it may simply mean no department has more than 1 employee above \$60k.

---

Aggregation Example with HAVING

Find departments where the average salary exceeds \$60k:

SELECT DepartmentId, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentId
HAVING AVG(Salary) > 60000;

Key Points:

• HAVING is essential because the filter depends on AVG(Salary) (an aggregate function).
• Using WHERE AVG(Salary) > 60000 would fail because aggregation hasn’t occurred yet.

---

Tricky Exercise: Total Salary per Department

Find departments where the total salary exceeds \$200k and show both total salary and number of employees:

SELECT DepartmentId, SUM(Salary) AS TotalSalary, COUNT(EmployeeId) AS NumEmployees
FROM Employees
GROUP BY DepartmentId
HAVING SUM(Salary) > 200000;

Why HAVING is used:

• The filter depends on SUM(Salary), calculated after grouping.
• WHERE cannot be used here because it only sees individual rows.

---

Quick Mental Check for Interviews

1. Am I filtering individual rows or groups?

• Rows → WHERE
• Groups → HAVING

1. Am I using aggregate functions?

• Yes → probably HAVING
• No → WHERE

1. Flow of SQL:

   1. Start with all rows
   2. Apply WHERE → filters rows
   3. GROUP BY → creates groups
   4. Apply HAVING → filters groups

Understanding WHERE and HAVING is not just about syntax. It’s about knowing when each filter applies in the SQL processing order. Using these concepts correctly will help you solve real-world queries and impress in technical interviews.

Thanks for reading this article, and remember to join GoConnect.dev where you can learn more and connect with other developers.