What does MySQL return when we use DISTINCT clause on a column with multiple NULL values?

When we use the DISTINCT clause on a column with multiple NULL values, MySQL will treat all NULL values ​​as the same.

This means that based on the properties of the DISTINCT clause, MySQL will return only one NULL value in the result set and eliminate other NULL values. Consider the example of a table named "testing" where there are multiple NULL values ​​in the column "Lname".

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    |
+-----+---------+----------+
8 rows in set (0.00 sec)

mysql> SELECT DISTINCT Lname from testing;
+---------+
| Lname   |
+---------+
| Kumar   |
| Bhalla  |
| NULL    |
| Khurana |
| Kohli   |
+---------+
5 rows in set (0.00 sec)

As can be seen from the above query, when we use the DISTINCT clause on the column 'Lname' which has four NULL values, MySQL returns only one NULL and eliminates the other values.

The above is the detailed content of What does MySQL return when we use DISTINCT clause on a column with multiple NULL values?. For more information, please follow other related articles on the PHP Chinese website!