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' ) ,
( 'S5' , 'Adams' , 30 , 'Athens' )Note that, in order for this expression to be regarded as a fair approximation to its relational counterpart (i.e., a relation literal denoting the relation that’s the current value of relvar S as shown in Figure 1-3), we must:
Ensure, for each column of the table specified by the VALUES expression, that all of the values are of the pertinent type. (In particular, if some given ordinal position in any of the specified rows corresponds to attribute A of the intended relational counterpart, then we must ensure that the same ordinal position in all of those rows corresponds to that same attribute A.)
Ensure that we don’t specify the same row twice.
Note: As you know, in the relational model a heading is a set of attributes. In SQL, by contrast, because columns have a left to right ordering, it would be more correct to regard a heading as a sequence, not a set, of attributes (or columns, rather). If the recommendations of this book are followed, however, this logical difference can mostly (?) be ignored.
What about table assignment and comparison operators? Well, table assignment is a big topic, and I’ll defer the details to Chapter 5. As for table comparisons, SQL has no direct support—not even for equality![47]—but workarounds are available. For example, here’s an SQL counterpart to the Tutorial D comparison S{CITY} = P{CITY}:
NOT EXISTS ( SELECT CITY FROM S
EXCEPT
SELECT CITY FROM P )
AND
NOT EXISTS ( SELECT CITY FROM P
EXCEPT
SELECT CITY FROM S )And here’s a counterpart to the Tutorial D comparison S{SNO} ⊃ SP{SNO}:
EXISTS ( SELECT SNO FROM S
EXCEPT
SELECT SNO FROM SP )
AND
NOT EXISTS ( SELECT SNO FROM SP
EXCEPT
SELECT SNO FROM S )[47] The odd thing is, it does have direct support for equality testing on “multisets”—including, therefore, multisets of rows in particular. (It also has direct support for equality testing on arrays.) Here’s a quote from the standard: “Two multisets A and B are distinct if there exists a value V in the element type of A and B, including the null value [sic], such that the number of elements in A that are not distinct from V does not equal the number of elements in B that are not distinct from V.” (I hope that’s perfectly clear! Note that the extract quoted does indeed define what it means for two multisets to be equal, because—simplifying slightly—if A and B aren’t distinct, then they’re equal.) As noted in Chapter 2, however, a multiset of rows in SQL isn’t the same thing as a table, because it can’t be operated upon by means of SQL’s regular table operators.