Returning all possible types using OUTER

When Db2® returns a structured type row value, the application does not necessarily know which attributes that particular instance contains or can contain.

For example, when you return a person, that person might have the attributes of only a person, or it might have attributes of an employee, manager, or other subtype of person.

If your application needs to obtain the values of all possible attributes within one SQL query, you can use the keyword OUTER in the table reference.

About this task

OUTER (table-name) and OUTER(view-name) return a virtual table that consists of the columns of the table or view followed by the additional columns introduced by each of its subtables, if any. The additional columns are added on the right side of the table, traversing the subtable hierarchy in the order of depth. Subtables that have a common parent are traversed in the order in which their corresponding types were created. The rows include all the rows of table-name and all of the additional rows of the subtables of table-name. Null values are returned for columns that are not in the subtable for the row.

You might use OUTER, for example, when you want to see information about people who tend to achieve above the norm. The following query returns information from the Person table hierarchy that have either a high salary Salary or a high grade point average GPA:

   SELECT *
      FROM OUTER(Person) P
      WHERE P.Salary > 200000
      OR P.GPA > 3.95 ;

Using OUTER(Person) enables you to refer to subtype attributes, which is not otherwise possible in Person queries.

The use of OUTER requires the SELECT privilege on every subtable or view of the referenced table because all of their information is exposed through its usage.

Suppose that your application needs to see not just the attributes of these high achievers, but what the most specific type is for each one. You can do this in a single query by passing the object identifier of an object to the TYPE_NAME built-in function and combining it with an OUTER query, as follows:

   SELECT TYPE_NAME(DEREF(P.Oid)), P.*
      FROM OUTER(Person) P
      WHERE P.Salary > 200000 OR
      P.GPA > 3.95 ;

Because the Address column of the Person typed table contains structured types, you would have to define additional functions and issue additional SQL to return the data from that column. Assuming you perform these additional steps, the preceding query returns the following output, where Additional Attributes includes GPA and Salary:

1                  OID           NAME                 Additional Attributes
------------------ ------------- --------------------  ...
PERSON_T           a             Andrew                ...
PERSON_T           b             Bob                   ...
PERSON_T           c             Cathy                 ...
EMPLOYEE_T         d             Dennis                ...
EMPLOYEE_T         e             Eva                   ...
EMPLOYEE_T         f             Franky                ...
MANAGER_T          i             Iris                  ...
ARCHITECT_T        l             Leo                   ...
EMPLOYEE_T         s             Susan                 ...