A view that combines information from several tables
You can create a view that contains a union of more than one table. A union of more than one table is called a join.
Db2 provides two types of joins—an outer join and an inner join. An outer join includes rows in which the values in the join columns don't match, and rows in which the values match. An inner join includes only rows in which matching values in the join columns are returned.
Examples
- Example
- The following example is an inner join of columns from the DEPT and EMP tables. The WHERE clause limits the view to just those columns in which the MGRNO in the DEPT table matches the EMPNO in the EMP table:
CREATE VIEW MYVIEW AS SELECT DEPTNO, MGRNO, LASTNAME, ADMRDEPT FROM DEPT, EMP WHERE EMP.EMPNO = DEPT.MGRNO;The result of executing this CREATE VIEW statement is an inner join view of two tables, which is shown below:
DEPTNO MGRNO LASTNAME ADMRDEPT ====== ====== ======== ======== A00 000010 HAAS A00 B01 000020 THOMPSON A00 C01 000030 KWAN A00 D11 000060 STERN D11 - Example
- Suppose that you want to create the view in the preceding example, but you want to include only those departments that report to department A00. Suppose also that you prefer to use a different set of column names. Use the following CREATE VIEW statement:
CREATE VIEW MYVIEWA00 (DEPARTMENT, MANAGER, EMPLOYEE_NAME, REPORT_TO_NAME) AS SELECT DEPTNO, MGRNO, LASTNAME, ADMRDEPT FROM EMP, DEPT WHERE EMP.EMPNO = DEPT.MGRNO AND ADMRDEPT = 'A00';You can execute the following SELECT statement to see the view contents:
SELECT * FROM MYVIEWA00;When you execute this SELECT statement, the result is a view of a subset of the same data, but with different column names, as follows:
DEPARTMENT MANAGER EMPLOYEE_NAME REPORT_TO_NAME ========== ======= ============= ============== A00 000010 HAAS A00 B01 000020 THOMPSON A00 C01 000030 KWAN A00
