SQL supports rows, not tuples; in particular, it supports row types, a row type constructor, and row value constructors, which are analogous, somewhat, to Tutorial D’s tuple types, TUPLE type generator, and tuple selectors, respectively. (Row types and row type constructors, though not row value constructors, were also discussed in Chapter 2.) But these analogies are loose at best, because, crucially, rows, unlike tuples, have a left to right ordering to their components. For example, the expressions ROW(1,2) and ROW(2,1)—both of which are legitimate row value constructor invocations in SQL—represent two different SQL rows. Note: The keyword ROW in an SQL row value constructor invocation is optional; in practice, it’s almost always omitted.

Thanks to that left to right ordering, row components (“fields”) in SQL can be, and indeed are, identified by ordinal position instead of by name. For example, consider the following row value constructor invocation (actually it’s a row literal, though SQL doesn’t use that term):

( 'S1' , 'Smith' , 20 , 'London' )

This row clearly has (among other things) a component with the value ‘Smith’; logically speaking, however, we can’t say that component is “the SNAME component,” we can only say it’s the second component.

I should add that rows in SQL always contain at least one component; SQL has no analog of the 0-tuple of the relational model (there’s no “0-row”).

As discussed in Chapter 2—recall the example involving the SQL row variable SRV—SQL also supports a row assignment operation.^[40] In particular, such assignments are involved (in effect) in SQL UPDATE statements. For example, the following UPDATE statement—

     UPDATE S
     SET    STATUS = 20 , CITY = 'London'
     WHERE  CITY = 'Paris' ;

—is defined to be logically equivalent to this one (note the row assignment in the second line):

     UPDATE S
     SET  ( STATUS , CITY ) = ( 20 , 'London' )
     WHERE  CITY = 'Paris' ;

As for comparison operations, most boolean expressions in SQL, including (believe it or not) simple “scalar” comparisons in particular, are actually defined in terms of rows rather than scalars. Here’s an example of a SELECT expression in which the WHERE clause contains an explicit row comparison:

     SELECT SNO
     FROM   S
     WHERE  ( STATUS , CITY ) = ( 20 , 'London' )

This SELECT expression is logically equivalent to the following one:

     SELECT SNO
     FROM   S
     WHERE  STATUS = 20 AND CITY = 'London'

As another example, the expression

     SELECT SNO
     FROM   S
     WHERE  ( STATUS , CITY ) <> ( 20 , 'London' )

is logically equivalent to:

     SELECT SNO
     FROM   S
     WHERE  STATUS <> 20 OR CITY <> 'London'

Note carefully in the expanded form of this example that the two individual comparisons in the WHERE clause are connected by OR, not AND.

Moreover, since row components have a left to right ordering, SQL is also able to support “<” and “>” as row comparison operators. Here’s an example:

     SELECT SNO
     FROM   S
     WHERE  ( STATUS , CITY ) > ( 20 , 'London' )

This expression is logically equivalent to:

     SELECT SNO
     FROM   S
     WHERE  STATUS > 20 OR ( STATUS = 20 AND CITY > 'London' )

In practice, however, the vast majority of row comparisons involve rows of degree one, as here:

     SELECT SNO
     FROM   S
     WHERE  ( STATUS ) = ( 20 )

Now, all of the comparand expressions in the examples so far have been, specifically, row value constructor invocations. But now I need to explain that SQL has a syntax rule to the effect that if such an invocation consists of a single scalar expression enclosed in parentheses, then the parentheses can optionally be dropped, as here:

     SELECT SNO
     FROM   S
     WHERE  STATUS = 20

The “row comparison” in the WHERE clause in this example is thus effectively a scalar comparison (STATUS and 20 are both scalar expressions). Strictly speaking, however, there’s no such thing as a scalar comparison in SQL; the expression STATUS = 20 is still technically a row comparison (and the “scalar” comparands are effectively coerced to rows), so far as SQL is concerned.

Recommendation: Unless the rows being compared are of degree one (and are thus effectively scalars), don’t use the comparison operators “<”, “<=”, “>”, and “>=”; they rely on left to right column ordering, they have no direct counterpart in the relational model, and in any case they’re seriously error prone. (It’s relevant to note in this connection that when this functionality was first proposed for SQL, the standardizers had great difficulty in defining the semantics properly; in fact, it took them several iterations before they got it right.)