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 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