SQL and Relational Theory, 2nd Edition

TABLES IN SQL

Note: Throughout this section, by the term table I mean a table value specifically—an SQL table value, that is—and not a table variable (which is what CREATE TABLE and CREATE VIEW create). I’ll discuss table variables in Chapter 5.

Now, I explained in Chapter 2 that SQL doesn’t really have anything analogous to the concept of a relation type at all; instead, an SQL table is just a collection of rows (a bag of rows, in general, not necessarily a set) that are of a certain row type. It follows that SQL doesn’t really have anything analogous to the RELATION type generator, either—though as we know from Chapter 2 it does support other type generators, including ROW, ARRAY, and MULTISET. It does, however, have something called a table value constructor that’s analogous, somewhat, to a relation selector. Here’s an example

     VALUES ( 1 , 2 ), ( 2 , 1 ), ( 1 , 1 ), ( 1 , 2 )

This expression (actually it’s a table literal, though SQL doesn’t use this term) evaluates to a table with four—not three!—rows and two columns. What’s more, those columns have no names. As I’ve already explained, the columns of an SQL table are ordered, left to right; as a consequence, those columns can be, and sometimes have to be, identified by ordinal position instead of name.

By way of another example, consider the following table value constructor invocation:

 VALUES ( 'S1' , 'Smith' , 20 , 'London' ) , ( 'S2' , 'Jones' , 10 , 'Paris' ) , ( 'S3' , 'Blake' , 30 , 'Paris' ) , ( 'S4' , 'Clark' , 20 , 'London' ...