SQL Having Clause

Like the WHERE clause, the HAVING clause in SQL is used to put a condition on a statement. In SQL, we cannot use the WHERE statement with aggregated statements such as GROUP BY. Thus, we use the HAVING clause to set a condition. In this tutorial, we will be talking about the SQL HAVING clause. Also, you will learn the syntax of the HAVING clause and understand how to use it with a relevant example. So without further ado, let's get started!

SQL HAVING Clause

In sort HAVING is a replacement of the WHERE clause for the GROUP BY statements.

HAVING Syntax

SELECT column_name_1, column_name_2...
FROM table_name
WHERE condition
GROUP BY column_name_n
HAVING condition
ORDER BY column_name_n;

Example For the above query, consider the following two sample tables: Students

+------+--------+------+--------+-------+----------+
| id   | name   | age  | grades | marks | Trade    |
+------+--------+------+--------+-------+----------+
|    1 | Luffy  |   16 | A      |   970 | Science  |
|    2 | Naruto |   18 | A      |   960 | Humanity |
|    3 | Zoro   |   20 | A      |   940 | Commerce |
|    4 | Sanji  |   21 | B      |   899 | Humanity |
|    5 | Nami   |   17 | B      |   896 | Science  |
|    6 | Robin  |   20 | B      |   860 | Humanity |
|    7 | Goku   |   27 | B      |   860 | Humanity |
+------+--------+------+--------+-------+----------+

Library

+---------+-------------------+------------+
| Book_ID | Book_Name         | Student_ID |
+---------+-------------------+------------+
|    1124 | One Hundred years |          1 |
|    1104 | The Great Escape  |          2 |
|    1209 | Beloved           |          6 |
|    1111 | Hollow            |          4 |
|    2351 | Invisible Man     |          3 |
|    1034 | A Passage         |          2 |
|    1211 | Hero              |          6 |
|    1188 | Your Name         |          5 |
|    1211 | Hero              |          6 |
|    1000 | My Hope           |          8 |
|    1000 | Go Away           |         10 |
+---------+-------------------+------------+

Query: Show the name, id, age, and the number of books of those students who have borrowed more than 1 book from the library.

SELECT id, name, age, COUNT(Book_Name) AS BOOKS
FROM Students Join Library
ON id = Student_ID
GROUP BY name
HAVING BOOKS>1;

Output

+------+--------+------+-------+
| id   | name   | age  | BOOKS |
+------+--------+------+-------+
|    2 | Naruto |   18 |     2 |
|    6 | Robin  |   20 |     3 |
+------+--------+------+-------+

In the above query, if we had used WHERE BOOKS > 1 instead of HAVING BOOKS>1 , then the SQL would have thrown some error.

Key Points to Remember

The following are some of the key points that you should keep in mind while working with the Having clause:

• HAVING is a replacement for the WHERE statement for the GROUP BY clause.
• HAVING is used to put conditions.

To Sum it Up

This tutorial aims to help you understand the HAVING Clause in SQL, which is a replacement for the WHERE clause. Also, we have shared an example to help you understand the use of the HAVING clause in a SQL query. We hope that you have developed a clear understanding of the HAVING clause. In case you have any doubts or queries, you can let us know by sharing them in the comments section below. People are also reading:

• UNIONS Clause in SQL
• SQL NULL Values
• SQL Sequences
• Sub Queries in SQL
• SQL Injection
• SQL JOINS
• Operators in SQL
• RDBMS Database