Union Types in SQL
XML schemas support a union type. For instance, you can say that an element must be an int or one of the strings “large”, “small”, or “huge”. Does SQL have any equivalent? That is, is it possible to type a field as containing either an int or one of an enumerated list of strings? Or do I just have to create two fields, one of which is null? How is this customarily handled in SQL?
December 1st, 2005 at 9:48 AM
Elliotte,
SQL does support UNION, but only as a query type, it isn’t structural as it is in C or in an XML schema, so you will need to use multiple fields and NULLs. An SQL UNION is literally the UNION of the tuples of two or more SELECT statements. In most SQL implementations the number and the types of the result set fields must match, and because SQL does allow constants in result sets, you assign NULL or some sentinel value to any field in any select statement that is undefined. This allows you to write queries that do not necessarily use outer join semantics.
So, given two tables:
CREATE TABLE WithString
(
uniqueName char(10) NOT NULL,
stringValue char(50) NOT NULL
);
CREATE TABLE WithInt
(
uniqueName char(10) NOT NULL,
intValue integer NOT NULL
);
SELECT uniqueName, stringValue, NULL FROM WithString
UNION
SELECT uniqueName, NULL, intValue FROM WithInt
ORDER BY 1, 2, 3
There are probably other ways of doing this that could make use of outer joins or other possibly much more complex SQL, but this is the simple answer. The “formal” reason for this limitation has to do with the SQL engine’s ability to construct referential integrity constraints and the invariance of the strong typing of result set columns.
I was able to find some on-line definitions of UNION and UNION ALL.
http://www.w3schools.com/sql/sql_union.asp
http://www.1keydata.com/sql/sqlunion.html
http://www.1keydata.com/sql/sqlunionall.html