HAVING clause in SQL

The HAVING clause in SQL is used to filter records and retrieve only those records that match the requirements or are actually needed.

The HAVING clause in SQL is used to filter records and retrieve only those records that match the requirements or are really needed similar to the WHERE clause. However:

  1. WHERE is the conditional statement that returns the result compared to each line.
  2. HAVING is a conditional statement that returns the collated result for the group created by the GROUP BY clause.

So after GROUP BY, only HAVING can be used and WHERE cannot be used after GROUP BY.

Syntax to use HAVING in SQL

The location of the HAVING clause in an SQL query is as follows:

 SELECT 
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Note : The HAVING clause must follow the GROUP BY clause in a query and precede the ORDER BY clause if used.

Here is the syntax of the SELECT statement including the HAVING clause:

 SELECT cot1, cot2 
FROM bang1, bang2
WHERE [ dieu_kien ]
GROUP BY cot1, cot2
HAVING [ dieu_kien ]
ORDER BY cot1, cot2

Examples of HAVING in SQL

Suppose the NHANVIEN table has the following records:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

To display a record with an age that is greater than or equal to 2 times, run the following command:

 SQL > SELECT ID, TEN, TUOI, DIACHI, LUONG 
FROM NHANVIEN
GROUP BY tuoi
HAVING COUNT(tuoi) >= 2;

The result is:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 2 | Loan | 25 | Hanoi | 1500.00 | +----+----------+-----+-----------+----------+

In the next section, we will take a look at Transaction in SQL , remember to follow it.

Previous article: Frame VIEW in SQL

Next post: TRANSACTION in SQL

4 ★ | 1 Vote | 👨 223 Views
« PREV POST
NEXT POST »