Increase the Power of SQL? (4)

Other FirstSQL Extensions

This section describes additional extensions in FirstSQL that provide query capabilities not possible with other RDBMSs. These features allow FirstSQL to handle the SQL requirements of the most sophisticated applications in a natural and convenient way.

Extended View Definitions

View definitions in FirstSQL may contain GROUP BY clauses and UNION operators. Some systems allow GROUP BY in views but few support UNION. This illustrates the problem with non-orthogonal features. UNION is a powerful tool in dealing with normalized data. Lack of support for UNION in views forces users to denormalize their data to achieve appropriate functionality.

Outer Joins

Many DBMSs now support outer joins, but this support is limited and sometimes incorrect. FirstSQL provides both Left and Right outer joins. It also supports embedded outer joins on both sides of an outer join.

While ODBC and many DBMSs support embedded outer joins only on the right hand side, FirstSQL allows embedding on the left hand side. This allows a base table to be outer joined to 2 tables separately. For example, a customer table might be outer joined to both the sales order table and the invoice table. This is not possible in other systems except perhaps using a view. Can your database do this?

MAYBE Operator

The MAYBE operator allows full control over Three Valued Logic. Queries involving nulls can produce unknown results. Unknown results are detected with the MAYBE operator.

In other DBMSs, users must formulate complex queries to detect unknown results. See 3 Valued Results - MAYBE Queries for a more detailed treatment of unknown results and the MAYBE operator.

Two Types of Nulls

FirstSQL is the only system supporting both types of nulls defined by the relational model. Two types of nulls allow more accurate representation and processing of missing information. See Two Types of Nulls for a full description of FirstSQL support of this important relational feature.

Complete Referential Integrity

FirstSQL is a pioneer in providing complete Referential Integrity support in an RDBMS. FirstSQL also fully supports self-referencing and cross-referencing Referential Integrity. See the section, Can Your Database Protect Your Data with Complete Referential Integrity?

Conclusion

These extensions increase the power of SQL by making SQL more orthogonal and by adding relational model features not supported by other systems. Queries that are difficult to formulate or require poor performing work-arounds in other RDBMSs can be expressed naturally using FirstSQL extensions. Queries that stump users, requiring expert help, are easily formulated with FirstSQL. Can your database do this?

Without this power, users use error-prone work-arounds. Worse, it may affect database structure, forcing denormalization, and may require the use of application code to circumvent system limitations. Does your system deliver enough relational power to avoid these problems?

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