The World of MySQL & MariaDB

SQL HAVING Clause Explained: Syntax, Examples, and Best Practices

Operators and Clauses
目次

1. What is the HAVING Clause?

The HAVING clause in SQL is used to apply conditions to aggregated results after grouping data. It is typically used together with the GROUP BY clause and serves to filter data after aggregation. By using HAVING, you can extract only those groups that meet specific criteria. For example, HAVING can be used when you want to select customers whose total sales exceed a certain amount or groups whose average scores surpass a specific threshold. Unlike the WHERE clause, which applies conditions to individual rows before aggregation, HAVING applies conditions to the aggregated results after grouping.

Example of Using HAVING Clause

For instance, the following query extracts customers whose total sales exceed 10,000 yen:
SELECT customer_id, SUM(sales) AS total_sales
FROM sales_table
GROUP BY customer_id
HAVING SUM(sales) > 10000;
This query uses the SUM function to calculate each customer’s total sales and extracts only those whose totals exceed 10,000 yen.

2. Basic Syntax and Usage of the HAVING Clause

The basic syntax of the HAVING clause is as follows:
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;
This syntax groups data using the GROUP BY clause and filters the aggregated results by specifying conditions with the HAVING clause. For example, the following query extracts customers who placed at least 5 orders from the orders table:
SELECT customer_id, COUNT(order_id) AS order_count
FROM orders_table
GROUP BY customer_id
HAVING COUNT(order_id) >= 5;
Here, the COUNT function counts the number of orders for each customer, and only those with 5 or more orders are filtered.

3. Examples of Applying the HAVING Clause

The HAVING clause is a powerful tool for advanced data analysis when combined with aggregate functions. Below are some specific examples.

Example 1: Filtering by Total Sales

To extract products with sales exceeding 10,000 yen, use the SUM function as follows:
SELECT product_id, SUM(sales) AS total_sales
FROM products_table
GROUP BY product_id
HAVING SUM(sales) > 10000;
This query calculates the total sales for each product and extracts those with totals greater than 10,000 yen.

Example 2: Filtering by Number of Orders

To extract customers who placed 10 or more orders:
SELECT customer_id, COUNT(order_id) AS order_count
FROM orders_table
GROUP BY customer_id
HAVING COUNT(order_id) > 10;
This query calculates the number of orders for each customer and extracts only those who placed 10 or more orders.

4. Difference Between HAVING and WHERE

Both the HAVING clause and the WHERE clause perform filtering, but they are applied at different stages.

Difference in Application Timing

  • WHERE clause: Applied before grouping, filtering individual rows.
  • HAVING clause: Applied after grouping, filtering aggregated results.
For example, when combining WHERE and HAVING in a query, you might first filter data where sales are at least 1,000 yen using WHERE, and then use HAVING to extract groups where total sales exceed 5,000 yen.
SELECT customer_id, SUM(sales) AS total_sales
FROM sales_table
WHERE sales >= 1000
GROUP BY customer_id
HAVING SUM(sales) > 5000;
In this query, the data filtered by the WHERE clause is grouped with GROUP BY, and the HAVING clause is then applied to extract only customers with total sales exceeding 5,000 yen.

5. Important Notes When Using the HAVING Clause

Must Be Used with Aggregate Functions

Because the HAVING clause filters aggregated results, it must be used together with aggregate functions such as SUM or COUNT. For conditions on individual rows, the WHERE clause is more appropriate.

Using Aliases

In the HAVING clause, you can use aliases specified with AS in condition expressions. For example, you can assign an alias to total sales and use it as follows:
SELECT customer_id, SUM(sales) AS total_sales
FROM sales_table
GROUP BY customer_id
HAVING total_sales > 10000;
This query extracts customers whose total sales exceed 10,000 yen.

6. Summary: How to Use the HAVING Clause

The HAVING clause is a powerful tool for flexibly filtering aggregated data by applying conditions. It is particularly useful when analyzing aggregated data such as sales or order counts, enabling efficient data analysis. By understanding its differences from the WHERE clause and using them appropriately together, you can maximize the flexibility of your SQL queries.