SQL Interview Questions

The HAVING clause in SQL is used to filter the results of a query that includes a GROUP BY clause. It is similar to the WHERE clause but is applied after the aggregation has been performed. The HAVING clause allows you to filter groups of data, whereas the WHERE clause filters rows of data before any groupings are made.

Key Differences between WHERE and HAVING

1. Purpose:

   • WHERE Clause: Filters rows before any grouping or aggregation is performed. It is used to set conditions on individual rows.
   • HAVING Clause: Filters groups after the aggregation is performed. It is used to set conditions on aggregated data.

2. Usage Context:

   • WHERE Clause: Used with SELECT, UPDATE, DELETE, etc., to filter rows.
   • HAVING Clause: Used only with SELECT statements that include GROUP BY to filter the aggregated results.

3. Filter Scope:

   • WHERE Clause: Cannot use aggregate functions directly.
   • HAVING Clause: Can use aggregate functions directly to filter groups based on aggregated values.

Examples

Suppose we have a table sales with the following structure:

sales
+----+---------+--------+-------+
| id | product | amount | year  |
+----+---------+--------+-------+
| 1  | A       | 100    | 2023  |
| 2  | B       | 150    | 2023  |
| 3  | A       | 200    | 2024  |
| 4  | B       | 100    | 2024  |
| 5  | C       | 50     | 2023  |
+----+---------+--------+-------+

Using WHERE Clause

To filter rows where the amount is greater than 100:

SELECT product, amount, year
FROM sales
WHERE amount > 100;

Result:

+---------+--------+-------+
| product | amount | year  |
+---------+--------+-------+
| B       | 150    | 2023  |
| A       | 200    | 2024  |
+---------+--------+-------+

Using HAVING Clause

To filter products with total sales greater than 150:

SELECT product, SUM(amount) AS total_sales
FROM sales
GROUP BY product
HAVING SUM(amount) > 150;

Result:

+---------+-------------+
| product | total_sales |
+---------+-------------+
| A       | 300         |
| B       | 250         |
+---------+-------------+

Combined Usage of WHERE and HAVING

To filter rows first where the year is 2023, and then filter groups where total sales are greater than 100:

SELECT product, SUM(amount) AS total_sales
FROM sales
WHERE year = 2023
GROUP BY product
HAVING SUM(amount) > 100;

Result:

+---------+-------------+
| product | total_sales |
+---------+-------------+
| B       | 150         |
+---------+-------------+

Summary

• WHERE Clause:

  • Filters individual rows before aggregation.
  • Used with conditions on columns directly.
  • Cannot use aggregate functions directly.

• HAVING Clause:

  • Filters groups after aggregation.
  • Used with conditions on aggregate functions.
  • Requires a GROUP BY clause to be meaningful.

Understanding the distinction between WHERE and HAVING is crucial for writing efficient and accurate SQL queries, especially when working with aggregated data.