What is the difference between the WHERE and HAVING clauses?

• WHERE – filters individual rows before any grouping or aggregation happens; it sees column values, not aggregated results.
• HAVING – filters groups after GROUP BY has formed them and aggregates have been computed; it is the only place for conditions like SUM(amount) > 1000000.
• Evaluation order – SQL processes FROM → WHERE → GROUP BY → aggregates → HAVING → SELECT → ORDER BY, which explains why WHERE cannot see aggregates and ORDER BY can see aliases.
• Performance rule – filter as early as possible in WHERE so fewer rows enter the expensive grouping.
• HAVING without GROUP BY – treats the entire table as one implicit group and filters the single aggregate.

What is the logical evaluation order of a SQL query?

The key to understanding WHERE versus HAVING is the logical order in which SQL processes a query, which is not the order you write it. The database evaluates:

1. FROM and any JOINs – assemble the tables.
2. WHERE – filter individual rows.
3. GROUP BY – form groups.
4. Aggregate functions – compute COUNT, SUM, AVG, etc.
5. HAVING – filter groups.
6. SELECT – compute expressions and aliases.
7. ORDER BY – sort the result.

Because WHERE runs at step 2 and aggregates are computed at step 4, WHERE cannot see any aggregate. Because HAVING runs at step 5, it can see the aggregates.

-- This evaluation order explains every WHERE vs HAVING rule
-- Step 2: WHERE filters rows (no aggregates available)
-- Step 4: aggregates computed
-- Step 5: HAVING filters groups (aggregates available)

How does WHERE work?

WHERE filters individual rows before grouping. It can reference column values but not aggregates. It is the first line of defense for reducing the dataset.

-- At National Bank of India: filter to successful transactions this year
SELECT account_id, SUM(amount) AS total_spent
FROM   transactions
WHERE  status = 'SUCCESS'
  AND  txn_date >= '2026-01-01'
GROUP BY account_id;

The WHERE clause removes failed transactions and out-of-range dates before grouping, so the GROUP BY processes fewer rows.

How does HAVING work?

HAVING filters groups after aggregation. It can reference aggregate functions because they have been computed by the time HAVING runs.

-- Keep only accounts whose total exceeds ten lakh
SELECT account_id, SUM(amount) AS total_spent
FROM   transactions
WHERE  status = 'SUCCESS'
  AND  txn_date >= '2026-01-01'
GROUP BY account_id
HAVING SUM(amount) > 1000000;

 account_id | total_spent
------------+------------
 SAV2001    |  1250000.00
 SAV2005    |  2100000.00

What happens if you put an aggregate in WHERE?

Writing an aggregate in WHERE is a syntax error because the aggregate does not exist at the row-filtering stage.

-- ERROR: aggregate functions are not allowed in WHERE
SELECT account_id, SUM(amount)
FROM   transactions
WHERE  SUM(amount) > 1000000
GROUP BY account_id;
-- ERROR: aggregate functions are not allowed in WHERE

What happens if you put a row condition in HAVING?

Using HAVING for a simple row condition that does not involve an aggregate usually still works, but it is wrong practice and often slower.

-- WRONG practice: row condition in HAVING
SELECT account_id, SUM(amount) AS total_spent
FROM   transactions
GROUP BY account_id
HAVING status = 'SUCCESS';  -- this should be in WHERE

-- CORRECT: filter rows in WHERE, filter groups in HAVING
SELECT account_id, SUM(amount) AS total_spent
FROM   transactions
WHERE  status = 'SUCCESS'
GROUP BY account_id
HAVING SUM(amount) > 1000000;

The performance reason: filtering in WHERE first means fewer rows go into the expensive grouping and aggregation. Pushing a row-level filter to HAVING forces the database to group all rows first and then discard.

How do WHERE and HAVING work together?

In practice, most queries use both clauses, each doing its own job. WHERE thins the rows; HAVING thins the groups.

-- Complete example at National Bank of India
-- Goal: high-spending accounts with successful transactions this year
SELECT a.account_id,
       a.holder_name,
       SUM(t.amount) AS total_spent
FROM   accounts a
JOIN   transactions t ON t.account_id = a.account_id
WHERE  t.status = 'SUCCESS'              -- row filter: only successful
  AND  t.txn_date >= '2026-01-01'        -- row filter: this year only
GROUP BY a.account_id, a.holder_name
HAVING SUM(t.amount) > 1000000           -- group filter: high spenders
ORDER BY total_spent DESC;

Can HAVING reference aggregates not in the SELECT list?

Yes. HAVING can filter on any aggregate, even one not selected.

-- Keep groups with more than 5 transactions, but do not show the count
SELECT account_id, SUM(amount) AS total_spent
FROM   transactions
GROUP BY account_id
HAVING COUNT(*) > 5;

What does HAVING without GROUP BY do?

Without GROUP BY, the entire table is treated as one implicit group. HAVING then filters the single aggregate row.

-- Returns the count only if it exceeds 1000, otherwise no rows
SELECT COUNT(*) AS total_accounts
FROM   accounts
HAVING COUNT(*) > 1000;

How do you implement this in Java with JDBC?

Rajesh builds the high-spender report in a Spring service, parameterizing the threshold.

package com.bank.service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class HighSpenderReportService {

    private static final String SQL =
        "SELECT account_id, SUM(amount) AS total_spent " +
        "FROM transactions " +
        "WHERE status = 'SUCCESS' AND txn_date >= ? " +
        "GROUP BY account_id " +
        "HAVING SUM(amount) > ? " +
        "ORDER BY total_spent DESC";

    public List<String> getHighSpenders(
            Connection conn, String fromDate, BigDecimal threshold) throws Exception {
        List<String> result = new ArrayList<>();
        try (PreparedStatement ps = conn.prepareStatement(SQL)) {
            ps.setString(1, fromDate);
            ps.setBigDecimal(2, threshold);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    result.add(rs.getString("account_id")
                        + " : " + rs.getBigDecimal("total_spent"));
                }
            }
        }
        return result;
    }
}

What should a senior engineer know?

• Some databases (MySQL in non-strict mode) allow referencing a SELECT alias in HAVING; standard SQL and PostgreSQL require repeating the aggregate expression.
• The performance rule is fundamental: filter rows in WHERE first to minimize the data entering GROUP BY, then filter groups in HAVING.
• HAVING with multiple aggregate conditions is common: HAVING SUM(amount) > 1000000 AND COUNT(*) > 10.
• In query plans, WHERE typically appears as a filter node before the aggregation node, while HAVING appears as a filter after it.

What are common interview follow-up questions?

• Can WHERE and HAVING use the same column? (Yes, for different purposes: WHERE filters rows by the column value, HAVING filters groups by an aggregate of the column.)
• Can you have HAVING without GROUP BY? (Yes, the entire table is one implicit group.)
• Why is WHERE faster than HAVING for the same condition? (WHERE reduces rows before grouping; HAVING groups everything first then discards.)