Introduction to NonStop SQL/MP

Using Views
How to Use NonStop SQL
113425 Tandem Computers Incorporated 2–5
Although the join shown in Figure 2-3 occurs on the employee number column, that
column does not have to be part of the result. The sample statement selects from the
joined tables only those columns that contain employee names and department names
and only those rows that contain manager names. The result is sorted by department
number.
Using the UNION
Operator
The UNION operator combines the end results of two SELECT statements. You can
use union operations to combine data from logically similar tables.
Suppose, for example, that a table called EMPLOYE1 contains information about
corporate employees in North America. A separate table, EMPLOYE2, contains
information about employees in Asia. To retrieve the names and employee numbers
of both groups of employees who work in department 4000, you can specify the
following query:
>> SELECT FIRST_NAME, LAST_NAME, EMPNUM
+> FROM \NY.$VOL1.PERSNL.EMPLOYE1
+> WHERE DEPTNUM = 4000
>> UNION
>> SELECT FIRST_NAME, LAST_NAME, EMPNUM
+> FROM \TOKYO.$VOL2.PERSNL.EMPLOYE2
+> WHERE DEPTNUM = 4000 ;
To specify a union operation, you must specify the same number of columns in each
select list. Also, columns in corresponding positions must have compatible data types.
Using Views A view is a specification of columns and rows from one or more base tables.
NonStop SQL/MP does not store the data in a view separately but retrieves the data
from the underlying base tables. Thus, a view is a virtual table. The database
administrator (DBA) stores a view definition in the data dictionary with the CREATE
VIEW statement, which assigns a view name to a SELECT statement.
You can select columns and rows from a view to retrieve part of the data represented
by that view. Selecting from a predefined view is simpler and less error prone than
writing a new SELECT statement each time you want to see a particular view of the
data.
Views let you customize the database to suit your business needs. When you use one
database for different applications, each application can access the database through
views that make the database seem designed for that application.
In addition, views require no replication of data. The DBA does not have to keep
many copies of the same data for different users.
If you frequently query the same table or related tables, creating a view saves time and
effort. Instead of repeatedly rewriting the query, you can refer to the named view. In
addition to enhancing productivity, views can improve performance because the DBA
can save the most efficient form of the query as a view.