Assign a default value in SQL to a selected variable when null is returned

To avoid constantly checking if a value is null when a ResultSet is processed, we can assign a default value to any possible null variables by using nvl (tableColumnName, defaultValueFortheType). The defaultValueFortheType means that  if it is a String type, then a default String value should be given. or if it is a integer type, a default int value should be given. Following are some examples.

SELECT firstname, nvl (middlename, ' '), lastname, ssn, nvl (age, 0) FROM person where country = 'Canada';

SELECT * from person, executives WHERE person.firstname = executives.firstname and nvl (person.middlename, 'aaaaaa') = nvl (executives.middlename, 'aaaaaa') and person.lastname = executives.lastname;

We can also use the coalesce() function to achieve this. It takes two or more parameters and will return the first not null value.

SELECT firstname, coalesce (middlename, ' '), lastname, ssn, coalesce (age, 0) FROM person where country = 'Canada';

----------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.