To combine multiple queries into a single result set, Oracle has the UNION and UNION ALL commands. Each query to be combined must have the same number and type of data elements. The UNION command eliminates duplicates and the UNION ALL retains them.
Oracle offers two very useful functions for replacing a NULL with a value easier to work with. This is particularly useful when evaluating columns using CASE or IF statements that would otherwise require multiple conditions.
To replace a NULL value with another use NVL. In the example below, if a spouse exists, the name will be reported and if NULL, it will instead report 'Not Married'.
SELECT NVL(employee_spouse,'Not Married') FROM employees;
To assign one value for a NULL value and another for a non-NULL value, use NVL2. The same query will be reworked to return 'Yes' if married and 'No' if not. The non-NULL option is listed first.
SELECT NVL2(employee_spouse,'Yes','No') FROM employees;