NULLS FIRST Function in Oracle SQL – PLSQL

The NULLS FIRST Function in Oracle SQL / PLSQL is used to place the NULL records at the beginning in the fetched records.

Syntax for using the NULLS FIRST function in Oracle SQL / PLSQL is ;
SELECT column(s)
ROW_NUMBER() OVER (ORDER BY column NULLS FIRST)
FROM table_name;

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales

Now, suppose we want to see the employee_name and commission and also want that the employees having no commission should be listed first then we can achieve the same using NULLS FIRST function as:

SELECT employee_name
       ,commission
       ,ROW_NUMBER () OVER (ORDER BY commission NULLS FIRST) SNO
FROM employee;

We will get the following result:

Employee_Name Commission SNO
Emp D   1
Emp H   2
Emp G 10 3
Emp E 10 4
Emp F 10 5
Emp A 10 6
Emp C 20 7
Emp B 20 8

Here we can see that we have successfully fetched employees having no commission first in the list.


Tagged , , , , , , , , , , , . Bookmark the permalink.