How will GROUP BY clause perform without an aggregate function?
When we use GROUP BY clause in the SELECT statement without using aggregate functions then it would behave like DISTINCT clause. For example, we have the following table −
mysql> Select * from Student_info;
+------+---------+------------+------------+
| id | Name | Address | Subject |
+------+---------+------------+------------+
| 101 | YashPal | Amritsar | History |
| 105 | Gaurav | Chandigarh | Literature |
| 125 | Raman | Shimla | Computers |
| 130 | Ram | Jhansi | Computers |
| 132 | Shyam | Chandigarh | Economics |
| 133 | Mohan | Delhi | Computers |
| 150 | Saurabh | NULL | Literature |
+------+---------+------------+------------+
7 rows in set (0.00 sec)
By using the DISTINCT clause on column ‘Address’, MySQL returns the following result set.
mysql> Select DISTINCT ADDRESS from Student_info;
+------------+
| ADDRESS |
+------------+
| Amritsar |
| Chandigarh |
| Shimla |
| Jhansi |
| Delhi |
| NULL |
+------------+
6 rows in set (0.07 sec)
Now, by using GROUP BY clause as follows, we can get the same result set as we got by using DISTINCT −
mysql> Select ADDRESS from Student_info GROUP BY Address;
+------------+
| ADDRESS |
+------------+
| NULL |
| Amritsar |
| Chandigarh |
| Delhi |
| Jhansi |
| Shimla |
+------------+
6 rows in set (0.00 sec)
We can observe a difference between both the result sets returned by MySQL that the result set returns by MySQL query using GROUP BY clause is sorted and in contrast, the result set return by MySQL query using DISTICT clause is not sorted.
Published on 22-Feb-2018 07:53:38
