Sorting values in a MySQL database is a very common task when you have to deal with data. However, when it comes to sorting data that include null values, there are some important considerations to keep in mind.

Depending on the specific needs of your project, you may want null values to be sorted either at the beginning or at the end of the results. In this article, we’ll explore how to sort values in a MySQL database with null values so that null values go first or last.

Sample Data

To better showcase the results of this example, we can use the following script to generate some sample data:

CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

INSERT INTO users (id, name, age) VALUES
(1, 'John', NULL),
(2, 'Jane', 25),
(3, 'Bob', 40),
(4, 'Alice', 30),
(5, 'Sarah', NULL),
(6, 'David', 35),
(7, 'Emma', 22),
(8, 'Tom', NULL),
(9, 'Grace', 28),
(10, 'Michael', 45);

This script creates a table called users with three columns: ‘id’, ‘name’, and ‘age’. We’re inserting ten rows of data, including some null values in the “age” column.

Sorting null values first

To sort null values first, we can use the IS NULL operator in combination with the ASC keyword to sort the null values in ascending order:

SELECT * FROM users ORDER BY age IS NULL ASC, age ASC;

This will produce the following result:

+----+---------+------+
| id |  name   | age  |
+----+---------+------+
|  1 | John    | NULL |
|  5 | Sarah   | NULL |
|  8 | Tom     | NULL |
|  7 | Emma    |   22 |
|  2 | Jane    |   25 |
|  9 | Grace   |   28 |
|  4 | Alice   |   30 |
|  6 | David   |   35 |
|  3 | Bob     |   40 |
| 10 | Michael |   45 |
+----+---------+------+

As you can see, the null values in the ‘age’ column appear first in the results, followed by the non-null values sorted in ascending order.

Sorting null values last

Now, in order to sort null values last, we can use the IS NULL operator in combination with the DESC keyword to sort the null values in descending order:

SELECT * FROM users ORDER BY age IS NULL DESC, age ASC;

This will produce the following result:

+----+---------+------+
| id |  name   | age  |
+----+---------+------+
|  7 | Emma    |   22 |
|  2 | Jane    |   25 |
|  9 | Grace   |   28 |
|  4 | Alice   |   30 |
|  6 | David   |   35 |
|  3 | Bob     |   40 |
| 10 | Michael |   45 |
|  1 | John    | NULL |
|  5 | Sarah   | NULL |
|  8 | Tom     | NULL |
+----+---------+------+

As you can see, the non-null values in the ‘age’ column appear first in the results, sorted in ascending order, followed by the non-null values.

To do so, we use the same SELECT statement as before, but we modify the ORDER BY clause and we break it in the following parts:

  • the first part of the ORDER BY clause uses the IS NULL operator to identify any null values in the ‘age’ column, then we order this expression in descending order, so that all null values appear last in the results,
  • the second part of the ORDER BY clause sorts the remaining non-null values in ascending order.

Conclusion

Sorting values in a MySQL database with null values requires special consideration to ensure that null values are sorted correctly. By using the IS NULL operator in combination with the ASC or DESC keyword, we can sort null values first or last depending on our specific needs.

Categorized in:

Tagged in:

, ,