MySQL query to order by NULL values

Let us first create a table −

mysql> create table DemoTable707 (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentFirstName varchar(100),    StudentMarks int ); Query OK, 0 rows affected (0.59 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable707(StudentFirstName,StudentMarks) values('John',45); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable707(StudentFirstName,StudentMarks) values(NULL,65); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable707(StudentFirstName,StudentMarks) values('Chris',78); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable707(StudentFirstName,StudentMarks) values(NULL,89); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable707(StudentFirstName,StudentMarks) values('Robert',99); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable707(StudentFirstName,StudentMarks) values(NULL,34); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable707(StudentFirstName,StudentMarks) values('Mike',43); Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable707;

This will produce the following output -

+-----------+------------------+--------------+ | StudentId | StudentFirstName | StudentMarks | +-----------+------------------+--------------+ | 1         | John             | 45 | | 2         | NULL           | 65 | | 3         | Chris | 78 | | 4         | NULL | 89 | | 5         | Robert | 99 | | 6         | NULL | 34 | | 7         | Mike | 43 | +-----------+------------------+--------------+ 7 rows in set (0.00 sec)

Following is the query to order by NULL values −

mysql> select *from DemoTable707 order by StudentFirstName IS NULL, StudentFirstName DESC;

This will produce the following output -

+-----------+------------------+--------------+ | StudentId | StudentFirstName | StudentMarks | +-----------+------------------+--------------+ | 5 | Robert | 99 | | 7 | Mike | 43 | | 1 | John | 45 | | 3 | Chris | 78 | | 2 | NULL | 65 | | 4 | NULL | 89 | | 6 | NULL | 34 | +-----------+------------------+--------------+ 7 rows in set (0.00 sec)