Increase the Power of SQL? (2)

View Capabilities in Queries

Views are a very useful feature of relational systems. In most cases, views are used to control or direct access and for their ease-of-use characteristics. Relational views are like macros in other systems.

An often overlooked aspect of views is that they provide capabilities not available in normal SQL queries. Many useful queries can only be accomplished using views (examples are given below). Unfortunately, query formulators in larger shops are rarely authorized to create views.

View provide useful features not otherwise available in queries:

A query can be embedded in the FROM clause
The embedded query can be given a name
The result columns of the embedded query can be renamed

Views have disadvantages also:

Views must be cataloged, created in separate steps
Users must have authorization to create views
Views can't have parameters; they can't include outer references or program parameters (? in ODBC or Embedded SQL)

FirstSQL extends SQL to allow full queries (SELECT ... FROM ... WHERE ...) to be embedded in the FROM clause. This eliminates the disadvantages of views and provides many of the benefits. Embedded FROM queries can be renamed using a correlation name and the columns of the embedded query can be renamed.

A simple view definition:

CREATE VIEW namea (nameb, namec) AS
SELECT col1, col2 / col3
FROM   tab1 ;

could be referenced in a query as follows:

SELECT *
FROM   namea
WHERE  namec > 2 ;

This query can be expressed with an embedded FROM query as:

SELECT *
FROM   (SELECT col1, col2 / col3
        FROM   tab1
        AS     nameb, namec) namea
WHERE  namec > 2 ;

Some situations where embedded queries in the FROM clause are useful:

GROUP BY on an Expression - most SQL systems, including FirstSQL, only allow column names in the GROUP BY list, however there are queries that need to group by an expression. A few systems do allow expressions in the GROUP BY list, but this leads to difficult and confusing formulations - the expression must be repeated exactly in the SELECT list. The solution is to use a view to give the expression a column name or use an embedded query in the FROM clause.
Joining to a GROUP BY - another useful operation is to join a table to a grouping of another table. For example, joining a department table to summary information from an employee table grouped by department - print for each department: department name, count of employees in the department and the average salary of those employees. This can be accomplished with a view if GROUP BY is allowed in views or with an embedded GROUP BY query in the FROM clause.
Additional operations possible with embedded queries in the FROM clause:
- Joining with a UNION
- GROUP BY of a UNION
- Embedded GROUP BY - GROUP BY of a GROUP BY

The next section, General Use of Single Valued Subqueries, describes a FirstSQL extension that allows a subquery returning a single value to be used anywhere a scalar value can..

Return to Contents Page: Increase SQL Power? Return to Can your Database do this?