Comparison of different SQL implementations

I don't have access to the official ISO standard text, but Whitemarsh Information Systems Corporation provides a rather final draft as a zip-archive, containing several files. Most important to this page is the file 5WD-02-Foundation-2003-09.pdf.

SQL:2003 is very new, and the only book currently covering the subject is in German which I was never any good at. Therefore, I also use the following book as reference:
Jim Melton and Alan Simon: SQL:1999—Understanding Relational Language Components (ISBN 1-55860-456-1).

SQL:2003 has a rather complicated set of rules governing when a view is updatable, basically saying that a view is updatable, as long as the update-operation translates into an unambiguous change.

SQL-92 was more restrictive, specifying that updatable views cannot be derived from more than one base table.

DECLARE cursorname CURSOR FOR
  SELECT ... FROM ... WHERE ...
  ORDER BY column_name1,column_name2,...

As such, the standard doesn't allow ORDER BY anywhere else than in cursor declarations. Special exceptions exist, such as the ORDER BY part of window functions (including ROW_NUMBER() OVER... and RANK() OVER...).

The standard doesn't specify how NULLs should be ordered in comparison with non-NULL values, except that NULLs are to be considered equal in the ordering, and that NULLs should sort either above or below all non-NULL values.

If your application is stateful (in contrast to web applications which normally have to be seen as stateless), then you might look at cursors (core feature ID E121) instead. This involves:

• DECALARE cursor-name CURSOR FOR ...
• OPEN cursor-name
• FETCH ...
• CLOSE cursor-name

• The fast variant:

  One of the major additions in SQL:2003 is the addition of non-core (i.e. optional) OLAP (online analytic processing) features. If the DBMS supports elementary OLAP (feature ID F611), then the top-n query may be formulated using a window function, such as RANK() OVER:

  SELECT * FROM (
  SELECT
    RANK() OVER (ORDER BY age ASC) AS ranking,
    person_id,
    person_name,
    age
  FROM person
) AS foo
WHERE ranking <= 3

  (Change ASC to DESC in the position marked like this in order to get a top-3 oldest query instead.)

• The slow variant:

  If the DBMS doesn't support the elementary OLAP features, then the top-n solution may be obtained in an alternative way which so slow that it's not a real option in most situations:

  Correlated subquery method, mentioned in the book Practical Issues in Database Management (chapter 9: Quota Queries) by Fabian Pascal (who, again, quotes Date for the solution):

  SELECT * FROM person AS px
WHERE (
  SELECT COUNT(*)
  FROM person AS py
  WHERE py.age < px.age
) < 3;

  The query may make more sense if the objective is re-phrased as "Find all persons (px) such that the number of younger, other persons (py) is less than 3".

  (Change < to > in the position marked like this in order to get a top-3 oldest query instead.)

In the article Going To Extremes by Joe Celko, there is a description of yet another principle for performing quota queries, using scalar subqueries. Scalar subqueries are more tedious to write but might yield better performance on your system.

Alternatively, you may use a cursor (core feature ID E121), if the programming environment permits it. This involves:

• DECALARE cursor-name CURSOR FOR ...
• OPEN cursor-name
• FETCH RELATIVE number-of-rows-to-skip ...
• CLOSE cursor-name

INSERT INTO tablename
VALUES (0,'foo') , (1,'bar') , (2,'baz');

— which can be seen as a shorthand for

INSERT INTO tablename VALUES (0,'foo');
INSERT INTO tablename VALUES (1,'bar');
INSERT INTO tablename VALUES (2,'baz');

The standard says that a BOOLEAN may be one of the following literals:

• TRUE
• FALSE
• UNKNOWN or NULL (unless prohibited by a NOT NULL constraint)

The DBMS may interpret NULL as equivalent to UNKNOWN. It is unclear from the specification if the DBMS must support UNKNOWN, NULL or both as boolean literals. In this author's opinion, you should forget about the UNKNOWN literal in order to simplify the situation and let the normal SQL three-way logic apply.

It's defined that TRUE > FALSE (true larger than false).

• Return with an exception state if the inserted string is too long, unless the characters exceeding the limit are all spaces.
• Pad CHAR columns with spaces if the inserted string is shorter than the specified CHAR-length.
• Pad with trailing spaces as needed when casting or comparing to other string-like values (e.g. VARCHARs).

Examples of TIMESTAMP literals:

• TIMESTAMP '2003-07-29 13:19:30'
• TIMESTAMP '2003-07-29 13:19:30.5'

Examples of TIMESTAMP WITH TIME ZONE literals:

• TIMESTAMP '2003-07-29 13:19:30+02:00'
• TIMESTAMP '2003-07-29 13:19:30.5+02:00'

It's strange that TIMESTAMP WITH TIME ZONE literals are not represented as, e.g., TIMESTAMP WITH TIME ZONE '2003-07-29 13:19:30+01:00', but according to Melton & Simon's book, they aren't.

1. To comply with Core SQL (Feature E021-06), the DBMS must support an 'ordinary' SUBSTRING function which extracts characters from a string:
   SUBSTRING(input FROM start-position [FOR length])
   Strings start at position 1. The start-position argument is a numeric value, as is the optional length-argument.

   (SQL:2003 specifies an extra optional argument—USING x—that has to do with Universal Character Sets, e.g. Unicode. x may be one of OCTETS or CHARACTERS.)

   The result is NULL if any of the arguments is NULL.

   Some cases of out-of-range values for start-position and length are allowed. Examples:

   • SUBSTRING('12345' FROM 6) yields the empty string.
   • A start-position less than 1 effectively lowers the value of length.

   For an exact definition: see item three in the "General Rules" part of section 6.29 in the standard.

2. The DBMS may optionally offer a regular expression variant (Feature T581) of SUBSTRING:
   SUBSTRING(input SIMILAR pattern ESCAPE escape-char)
   Pattern deserves some explanation. It's a string which needs to consist of three parts: A part matching before the wanted sub-string, the wanted substring, and a part matching after the wanted substring.
   The parts must be separated by a combination of the indicated escape-char (escape-character) and a double-quote ("). Example:
    SUBSTRING('abc' SIMILAR 'a#"b#"c' ESCAPE '#')
   should yield
    b
   The pattern description rules in SQL don't completely resemble POSIX regular expressions, as far as I can see.

where may be one of LEADING, TRAILING or BOTH—or omitted which implies BOTH.

characters indicates what character(s) to remove from the head and/or tail of the string. It may be omitted which implies the value ' ' (space character).

In other words, the shortest form is TRIM(string_to_be_trimmed) which in effect means TRIM(BOTH ' ' FROM string_to_be_trimmed).

Trimming NULL returns NULL.

SELECT LOCALTIMESTAMP ...
or
SELECT LOCALTIMESTAMP(precision) ...

Note that "SELECT LOCALTIMESTAMP() ..." is illegal: If you don't care about the precision, then you must not use any parenthesis.

If the DBMS supports the non-core time zone features (feature ID F411), then it must also provide the functions CURRENT_TIMESTAMP and CURRENT_TIMESTAMP(precision) which return a value of type TIMESTAMP WITH TIME ZONE. If it doesn't support time zones, then the DBMS must not provide a CURRENT_TIMESTAMP function.

string1 || string2

If at least operand is NULL, then the result is NULL.

It's unclear to me if the DBMS is allowed to try to automatically cast the operands to concatenation-compatible types.

A column—or a set of columns—which is subject to a UNIQUE constraint must also be subject to a not NULL constraint, unless the RDBMS implements an optional "NULLs allowed" feature (Feature ID 591). The optional feature adds some additional characteristics to the UNIQUE constraint:

1. Columns involved in a UNIQUE constraint may also have NOT NULL constraints, but they do not have to.
2. If columns with UNIQUE constraints do not also have NOT NULL constraints, then the columns may contain any number of NULL-'values'. (Logical consequence of the fact that NULL<>NULL.)
   In SQL:2003 parlance, the constraint is satisfied, if

   there are no two rows in [the relation] such that the value of each column in one row is non-null and is not distinct from the value of the corresponding column in the other row

When creating a table, an IDENTITY clause may be declared for certain types of columns (INTEGER being one):

CREATE TABLE tablename (
  tablename_id INTEGER GENERATED ALWAYS AS IDENTITY
  ...
)

or

CREATE TABLE tablename (
  tablename_id INTEGER GENERATED BY DEFAULT AS IDENTITY
  ...
)

The column with the IDENTITY attribute will be given values in increasing order, possibly with 'holes' (...,3,4,7,...).

A base table may at most contain one column with the IDENTITY attribute. NOT NULL is implied for an IDENTITY column. Normally, a column declared with IDENTITY will also be declared PRIMARY KEY, but it's not implied.

The examples differ in their 'ALWAYS' vs. 'BY DEFAULT' clauses:

• When ALWAYS is specified, the user cannot specify a value for the column which means that the RDBMS can guarantee successful insertion of a unique value on each table insert.
• When BY DEFAULT is specified, the user may manually specify what value to put in the identity field of a row. The flip side is that the RDBMS cannot guarantee that this will work.

The standard specifies several extended options which may be declared for a generated IDENTITY column.

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'

or (often more relevant):

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
  AND TABLE_SCHEMA='SCHEMA-NAME'

See a warning about potential case sensitivity problems below.

SELECT column_name,data_type,column_default,is_nullable
FROM
  information_schema.tables AS t
  JOIN
  information_schema.columns AS c ON
    t.table_catalog=c.table_catalog AND
    t.table_schema=c.table_schema AND
    t.table_name=c.table_name
WHERE
  t.table_name='TABLE-NAME'

—or like this (more verbose):

SELECT
  column_name,
  data_type,
  character_maximum_length,
  numeric_precision,
  column_default,
  is_nullable
FROM
  information_schema.tables as t
  JOIN
  information_schema.columns AS c ON
    t.table_catalog=c.table_catalog AND
    t.table_schema=c.table_schema AND
    t.table_name=c.table_name
WHERE
    c.table_schema='TABLE-SCHEMA'
  AND
    c.table_name='TABLE-NAME'

To get information about constraints, involved columns and (possibly) referenced columns, a query like this may be used:
SELECT
  tc.CONSTRAINT_NAME,
  CONSTRAINT_TYPE,
  ccu.COLUMN_NAME,
  rccu.COLUMN_NAME,
  rccu.TABLE_CATALOG,
  rccu.TABLE_SCHEMA,
  rccu.TABLE_NAME,
  CHECK_CLAUSE
FROM
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
  LEFT JOIN
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON
    tc.CONSTRAINT_CATALOG=ccu.CONSTRAINT_CATALOG AND
    tc.CONSTRAINT_SCHEMA=ccu.CONSTRAINT_SCHEMA AND
    tc.CONSTRAINT_NAME=ccu.CONSTRAINT_NAME AND
    tc.TABLE_CATALOG=ccu.TABLE_CATALOG AND
    tc.TABLE_SCHEMA=ccu.TABLE_SCHEMA AND
    tc.TABLE_NAME=ccu.TABLE_NAME
  LEFT JOIN
  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON
    rc.CONSTRAINT_CATALOG=ccu.CONSTRAINT_CATALOG AND
    rc.CONSTRAINT_SCHEMA=ccu.CONSTRAINT_SCHEMA AND
    rc.CONSTRAINT_NAME=ccu.CONSTRAINT_NAME
  LEFT JOIN
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rccu ON
    rc.UNIQUE_CONSTRAINT_CATALOG=rccu.CONSTRAINT_CATALOG AND
    rc.UNIQUE_CONSTRAINT_SCHEMA=rccu.CONSTRAINT_SCHEMA AND
    rc.UNIQUE_CONSTRAINT_NAME=rccu.CONSTRAINT_NAME
  LEFT JOIN
  INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc ON
    tc.CONSTRAINT_CATALOG=cc.CONSTRAINT_CATALOG AND
    tc.CONSTRAINT_SCHEMA=cc.CONSTRAINT_SCHEMA AND
    tc.CONSTRAINT_NAME=cc.CONSTRAINT_NAME
WHERE
  tc.TABLE_CATALOG='CATALOG-NAME' AND -- see remark
  tc.TABLE_SCHEMA='SCHEMA-NAME' AND   -- see remark
  tc.TABLE_NAME='TABLE-NAME'
ORDER BY tc.CONSTRAINT_NAME

If you don't care about potential namespace conflicts, you may leave out the lines commented with "-- see remark".

See also: Warning about potential case sensitivity problems above.