Oftentimes, a query will return null values for some of the columns in a query. However, to display “null” as the value in the generated report isn’t a very effective way to display this information. Oracle provides an easy solution for this problem.

For example, suppose you are generating a report for a supplier. The table schema may look like this:

Table: SuppDetailsSUPPID	SUPPNAME SUPPADDRESS------	-------- -----------1	ABC1	#1, St. Thomas St..2	ABC2	New Brigade, Raj St..3	ABC3

In this situation, your query is also fetching SuppAddress, the value for the SuppAddress where SuppID = 3 is null:

Select SUPPID, SUPPNAME, SUPPADDRESS from SuppDetails;

To avoid displaying null, try using this query:

Select SUPPID, SUPPNAME, NVL(SUPPADDRESS, SUPPNAME) from SuppDetails;

This returns the SUPPNAME if the SUPPADDRESS is null.

Here’s another example: suppose you’ve got a table with a COMMISSION column in it and there’s a possibility that the column is null. Instead of using this:

Select COMMISSION from TABLE_COMM would return null;

Try this:

Select NVL(COMMISSION,0) from TABLE_COMM would return 0(Zero). 

Using NVL in your queries where you expect null to be returned will also help avoid processing on the front-end code as well.