5.18 Set operations

---

5.18 Set operations

Set operators can be used to combine result sets in both Oracle and DB2 UDB. Table 5-7 shows the differences between the products.

Table 5-7: Mapping of set operations

Oracle	DB2 UDB
UNION	UNION
UNION ALL	UNION ALL
MINUS	EXCEPT
INTERSECT	INTERSECT

DB2 UDB supports the ALL option on each operator, allowing duplicates to be preserved. Oracle allows ALL only on UNION.

Null values conversion

In Oracle, function NVL provides a conversion of NULL values to non-null values:

    NVL(TO_CHAR(MANAGER_ID),'No Manager') 

converts all of the NULL values in the manager_id column to the string: "No Manager"

In DB2, use the COALESCE function to convert nulls, as in:

    COALESCE(MANAGER_ID,'No Manager') 

---