Here repeated from the section SCALAR vs. NONSCALAR TYPES is an example of a tuple variable definition:

     VAR STV TUPLE { STATUS INTEGER , SNO CHAR , CITY CHAR , SNAME CHAR } ;

The expression TUPLE {...} here is, as you’ll recall, an invocation of the TUPLE type generator. SQL has a corresponding ROW type generator (though it calls it a type constructor). Here’s an SQL analog of the foregoing Tutorial D example:

     DECLARE SRV /* SQL row variable */
             ROW ( SNO    VARCHAR(5) ,
                   SNAME  VARCHAR(25) ,
                   STATUS INTEGER ,
                   CITY   VARCHAR(20) ) ;

Unlike tuples, however, rows in SQL have a left to right ordering to their components;^[36] in the case at hand, there are actually 24 (= 4 * 3 * 2 * 1) different row types all consisting of the same four components (!).

SQL also supports row assignment. Recall this Tutorial D tuple assignment:

     STV := TUPLE FROM ( S WHERE SNO = 'S1' ) ;

Here’s an SQL row assignment analog:

     SET SRV = ( S WHERE SNO = 'S1' ) ;

The expression on the right side here is a row subquery—i.e., it’s a table expression, syntactically speaking, but it’s one that’s acting as a row expression. That’s why there’s no explicit counterpart to Tutorial D’s TUPLE FROM (see the discussion of subqueries and coercion in the section “SQL Type Checking and Coercion” a couple of pages back).

Row assignments are also involved, in effect, in SQL UPDATE statements (see Chapter 3).

Turning to tables: Interestingly, SQL doesn’t really have a TABLE type generator (or type constructor, as SQL would probably call it) at all!—i.e., it has nothing directly analogous to the RELATION type generator described earlier in this chapter. However, it does have a mechanism, CREATE TABLE, for defining what by rights should be called table variables. For example, recall this definition from the section SCALAR vs. NONSCALAR TYPES:

     VAR S BASE
           RELATION { SNO CHAR , SNAME CHAR , STATUS INTEGER , CITY CHAR }
           KEY { SNO } ;

Here’s an SQL analog:

     CREATE TABLE S
          ( SNO    VARCHAR(5)   NOT NULL ,
            SNAME  VARCHAR(25)  NOT NULL ,
            STATUS INTEGER      NOT NULL ,
            CITY   VARCHAR(20)  NOT NULL ,
            UNIQUE ( SNO ) ) ;

Note carefully, however, that there’s nothing—no sequence of linguistic tokens—in this example that can logically be labeled “an invocation of the TABLE type constructor.” (This fact might become more apparent when you realize that the specification UNIQUE(SNO), which defines a certain integrity constraint on suppliers, doesn’t have to come after the column definitions but can appear almost anywhere—e.g., between the definitions of columns SNO and SNAME. Not to mention the NOT NULL specifications on the individual column definitions, which also define certain integrity constraints.) In fact, to the extent that the variable S can be regarded (in SQL) as having any type at all, that type is nothing more than bag of rows, where the rows in question are of type ROW (SNO VARCHAR(5), SNAME VARCHAR(25), STATUS INTEGER, CITY VARCHAR(20)).

That said, I should say too that SQL does support something it calls “typed tables.” The term isn’t very appropriate, however, because if TT is a “typed table” that has been defined to be “of type T,” then TT is not of type T, and neither are its rows! More important, I think you should avoid such tables anyway, because they’re inextricably intertwined with SQL’s support for pointers, and pointers are explicitly prohibited in the relational model.^[37] In fact, if some table has a column whose values are pointers to rows in some “target” table, then that table can’t possibly represent a relation in the relational model sense.^[38] As I’ve just indicated, however, such tables are unfortunately permitted in SQL; the pointers are called reference values, and the columns that contain them are said to be of some REF type. Quite frankly, it’s not clear why these features are included in SQL at all; certainly there seems to be no useful functionality that can be achieved with them that can’t equally well—in fact, better—be achieved without them. Strong recommendation: Don’t use them, or any features related to them.