Home >Database >Mysql Tutorial >How does the MySQL GROUP BY clause behave like a DISTINCT clause?

How does the MySQL GROUP BY clause behave like a DISTINCT clause?

WBOY
WBOYforward
2023-09-08 13:09:021285browse

MySQL GROUP BY 子句如何表现得像 DISTINCT 子句?

When we use GROUP BY clause in SELECT statement without using aggregate function, it behaves like DISTINCT clause. For example, we have the following table -

mysql> Select * from testing;
+------+---------+---------+
| id   | fname   | Lname   |
+------+---------+---------+
|  200 | Raman   | Kumar   |
|  201 | Sahil   | Bhalla  |
|  202 | Gaurav  | NULL    |
|  203 | Aarav   | NULL    |
|  204 | Harshit | Khurana |
|  205 | Rahul   | NULL    |
|  206 | Piyush  | Kohli   |
|  207 | Lovkesh | NULL    |
|  208 | Gaurav  | Kumar   |
|  209 | Raman  | Kumar    |
+------+---------+---------+
10 rows in set (0.00 sec)

By using the DISTINCT clause on the "Lname" column, MySQL returns the following result set.

mysql> select Distinct LNAME from testing;
+---------+
| LNAME   |
+---------+
| Kumar   |
| Bhalla  |
| NULL    |
| Khurana |
| Kohli   |
+---------+
5 rows in set (0.00 sec)

Now, by using the GROUP BY clause as shown below, we can get the same result set as that obtained using DISTINCT -

mysql> Select LNAME from testing GROUP BY Lname;
+---------+
| LNAME   |
+---------+
| NULL    |
| Bhalla  |
| Khurana |
| Kohli   |
| Kumar   |
+---------+
5 rows in set (0.04 sec)

We can observe the results returned by MySQL There is a difference between the sets, a MySQL query using the GROUP BY clause returns a result set that is sorted, while a MySQL query using the DISTINCT clause returns an unsorted result set. p>

The above is the detailed content of How does the MySQL GROUP BY clause behave like a DISTINCT clause?. For more information, please follow other related articles on the PHP Chinese website!

Statement:
This article is reproduced at:tutorialspoint.com. If there is any infringement, please contact admin@php.cn delete