I turn now to SQL. SQL supports the following more or less self-explanatory system defined scalar types (it also allows users to define their own types, but as I’ve already said I’m more or less ignoring user defined types in this chapter):
BOOLEAN INTEGER CHARACTER(n) SMALLINT CHARACTER VARYING(n) BIGINT CHARACTER LARGE OBJECT(n) NUMERIC(p,q) BINARY(n) DECIMAL(p,q) BINARY VARYING(n) FLOAT(p) BINARY LARGE OBJECT(n)
This isn’t an exhaustive list; other SQL system defined types include an “XML document” type (XML); a variety of “national character string types” (NATIONAL CHARACTER(n), etc.); and a variety of datetime types (DATE, TIME, TIMESTAMP, INTERVAL). However, I’ll ignore such types, mostly, for the purposes of this book. Points arising:
A number of defaults, abbreviations, and alternative spellings—e.g., INT for INTEGER, CHAR for CHARACTER, VARCHAR for CHARACTER VARYING, CLOB for CHARACTER LARGE OBJECT—are also supported.
As you can see, SQL, unlike Tutorial D, requires its various character string types to have an associated length specification.
The same goes for the various BINARY types. Note: BINARY really means bit string, or (perhaps better) byte string; the associated length specification gives the corresponding length in octets.[31] Also, while BINARY LARGE OBJECT can be abbreviated to BLOB, BINARY and BINARY VARYING can’t be abbreviated at all (contrary to expectations, perhaps).
Strictly speaking, CHAR (for example) isn’t really a type as such—rather, it’s a type generator. By contrast, CHAR(25), for example, is a type as such, and it’s obtained by invoking that type generator with the value 25 as sole argument to that invocation. What’s more, analogous remarks apply to everything in the foregoing list except for type BOOLEAN and the various integer types (SMALLINT, INTEGER, BIGINT).[32] For simplicity, however, I’ll overlook this point in what follows (most of the time, at any rate) and continue to refer to CHAR and the rest as if they were indeed types as such.
Literals of more or less conventional format are supported for all of these types.
An explicit assignment operator is supported for all of these types. The syntax is:
SET
<scalar variable ref>=<scalar exp>;Scalar assignments are also performed implicitly when various other operations (e.g., FETCH) are executed. Note: Throughout this book in formal syntax definitions like the one just shown, I use ref and exp as abbreviations for reference and expression, respectively.
An explicit equality comparison operator is also supported for all of these types.[33] The syntax is:
<scalar exp>=<scalar exp>Equality comparisons are also performed implicitly when numerous other operations (e.g., joins and unions, grouping and duplicate elimination operations, and many others) are executed.
Regarding type BOOLEAN in particular, I should point out that although it’s included in the SQL standard, it’s supported by few if any of the mainstream SQL products. Of course, boolean expressions can always appear in WHERE, ON, and HAVING clauses, even if the system doesn’t support type BOOLEAN as such. In such a system, however, no table can have a column of type BOOLEAN, and no variable can be declared to be of type BOOLEAN. As a consequence, workarounds (e.g., “yes/no columns”) might sometimes be needed.
Finally, in addition to the foregoing scalar types, SQL also supports something it calls domains. However, SQL’s domains aren’t types at all; rather, they’re just a kind of factored out “common column definition,” with a number of rather strange properties that are well beyond the scope of this book. You can use them if you like, but don’t make the mistake of thinking they’re true relational domains (i.e., types).
[31] True bit string types—BIT(n) and BIT VARYING(n), where n was the length in bits—were introduced in SQL:1992 but dropped again in SQL:2003.
[32] SQL also supports a ROW type generator, as we know. In fact, it also supports ARRAY, MULTISET, and REF (but, oddly enough, not TABLE) as type generators.
[33] Unfortunately that support is severely flawed, however. First of all, SQL supports coercions (see later), with the consequence that “=” can give TRUE even when the comparands are of different types. Second, in the case of character string types, it’s possible for “=” to give TRUE even when the comparands are of the same type but clearly distinct (see the section COLLATIONS IN SQL). And it’s also possible—for all types, not just character string types—for “=” not to give TRUE even when the comparands aren’t distinguishable; in particular, this happens when (but not only when) the comparands are both null. Also, for certain types not discussed in detail in this book, including type XML and certain user defined types, “=” isn’t defined at all.