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 5CD2-02-Foundation-2006-01.pdf.

No books cover SQL:2008 yet. Regarding the previous standard, SQL:2003, the only book 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:2008 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.

The DBMS may support an extension of this (feature T173) which allows for more table properties to be copied:
CREATE TABLE t2 ( LIKE t1 INCLUDING IDENTITY INCLUDING DEFAULTS INCLUDING GENERATED )

If INCLUDING DEFAULTS is not specified, column defaults will not be part of t2; likewise with IDENTITY and GENERATED properties.

Triggers, CHECK constraints, and other 'non-trivial' table features are not copied to the new table.

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

The DBMS may additionally allow ORDER BY outside cursor definitions (optional feature IDs F850, F851, F852, F855).^{(Since SQL:2008)}

The standard doesn't specify how NULLs should be ordered in comparison with non-NULL values, except that any two NULLs are to be considered equally ordered, and that NULLs should sort either above or below all non-NULL values. However, the DBMS may optionally (as part of feature ID T611, "Elementary OLAP operations") allow the user to specify whether NULLs should sort first or last:
... ORDER BY ... NULLS FIRST
or
... ORDER BY ... NULLS LAST

• Using FETCH FIRST:^{(since SQL:2008)}

  Non-core feature IDs F856, F857, F858, and F859 describe using
  SELECT ... FROM ... WHERE ... ORDER BY ... FETCH FIRST n ROWS ONLY

  You may write ROW instead of ROWS.

• Using a Window function:^{(since SQL:2003)}

  Non-core Feature ID T611 specifies window functions, of which one is ROW_NUMBER() OVER:
   
  SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

• Using a cursor:

  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:

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

• The fast variant:

  One of the major additions in SQL:2003 was 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 is 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.

• Using OFFSET and FETCH FIRST:^{(since SQL:2008)}

  SELECT...
FROM ...
WHERE ...
ORDER BY ...
OFFSET skip ROWS
FETCH FIRST n ROWS ONLY

  You may write ROW instead of ROWS.

• Using a window function:^{(since SQL:2003)}

  Non-core Feature ID T611 specifies window functions, one of which is ROW_NUMBER() OVER:
   
  SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownum,
    columns
  FROM tablename
) AS foo
WHERE rownum > skip AND rownum <= (n+skip)

• Using a cursor:

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

  • DECLARE 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 may be read 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.

Returns NUMERIC. Returns NULL if the input is NULL.
Alias: CHAR_LENGTH.
The argument may be of type CHAR or VARCHAR.
Part of the Core SQL requirements (feature ID E021-04).
Related function: OCTET_LENGTH.

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. If no length parameter is indicated, length becomes infinite

   (The standard 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 sets start-position to 1 and reduces the value of length by 1+abs(start-position).
     I.e., if start-position is -3 and length is 6, then the length value becomes 2.
      
     Another way to put it is that when start-position is negative, a bunch of arbitrary/blank characters are prepended to the input-value. bunch=1-start-position.

   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 one 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 DBMS 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 the standard-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 DBMS 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 DBMS cannot guarantee that this will work.

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

Unfortunately, the standard doesn't specify

1. whether TRUNCATE TABLE should be allowed in a transaction involving other statements, or not
2. whether TRUNCATE TABLE should imply an immediate COMMIT, or not

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.