Comparison of Different SQL Implementations

Peter Kitson

ISBN : -

Order a printed copy of this book from Amazon --UNAVAILABLE--


Cover Design - Comparison of Different SQL Implementations
  

For your free electronic copy of this book please verify the numbers below. 

(We need to do this to make sure you're a person and not a malicious script)

Numbers

 



Sample Chapter From Comparison of Different SQL Implementations
     Copyright © Troels Arvin



The INSERT statement

Inserting several rows at a time

Standard An optional SQL feature is row value constructors (feature ID F641). One handy use of row value constructors is when inserting several rows at a time, such as:

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\');
PostgreSQL Not supported.
DB2 Supported.
MSSQL Not supported.
MySQL Supported.
Oracle Not supported.

Data types

The BOOLEAN type

Standard The BOOLEAN type is optional (has feature ID T031), which is a bit surprising for such a basic type. However, it seems that endless discussions of how NULL is to be interpreted for a boolean value is holding BOOLEAN from becoming a core type.

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).
PostgreSQL Follows the standard.

Accepts NULL as a boolean literal; doesn\'t accept UNKNOWN as a boolean literal.

Documentation
DB2 Doesn\'t support the BOOLEAN type.
 
Judging from various JDBC-documentation, it seems that IBM recommends a CHAR(1) field constrained to values \'0\' and \'1\' (and perhaps NULL) as the way to store boolean values.
MSSQL Doesn\'t support the BOOLEAN type.

Possible alternative type: the BIT type which may have 0 or 1 (or NULL) as value. If you insert an integer value other than these into a field of type BIT, then the inserted value will silently be converted to 1.

Rudy Limeback has some notes about oddities with the MSSQL BIT type.

Documentation
MySQL Offers a non-conforming BOOLEAN type. MySQL\'s BOOLEAN is one of many aliases to its TINYINT(1) type.

(Never use TINYINT(1) as the column type if you use JDBC with MySQL and expect to get non-boolean values from it.)

MySQL accepts the literals TRUE and FALSE as aliases to 1 and 0, respectively. However, you may also assign a value of — e.g. — 9 to a column of type BOOLEAN (which is non-conforming).

If you use JDBC with MySQL, then BOOLEAN is the preferred type for booleans: MySQL\'s JDBC-driver implicitly converts between Java\'s boolean and MySQL\'s pseudo-BOOLEAN type.

Documentation
Oracle Doesn\'t support the BOOLEAN type.
 
Judging from various JDBC documentation, it seems that Oracle recommends NUMBER(1) as the way to store boolean values; it\'s probably wise to constrain such columns to values 0 and 1 (and perhaps NULL).

Warning to JDBC users:
According to the JDBC standard, getBoolean() must convert a SQL-\'value\' of NULL to the false Java value. To check if the database-value was really NULL, use wasNull().

The CHAR type

For the following section, I have used this test-SQL to try to illuminate differences (unfortunately, even standard SQL as simple as this has to be adjusted for some products):

Test steps:
CREATE TABLE chartest (
  charval1 CHAR(10) NOT NULL,
  charval2 CHAR(10) NOT NULL,
  varcharval VARCHAR(30) NOT NULL
);
INSERT INTO chartest VALUES (\'aaa\',\'aaa\',\'aaa\');
INSERT INTO chartest
  VALUES (\'aaaaaa      \',\'aaa\',\'aaa\'); -- should truncate to \'aaaaaa    \'
INSERT INTO chartest
  VALUES (\'aaaaaaaaaaaa\',\'aaa\',\'aaa\'); -- should raise error
SELECT * FROM chartest; -- should show two rows
DELETE FROM chartest WHERE charval1=\'aaaaaa\';
SELECT * FROM chartest; -- should show one row
SELECT * FROM chartest WHERE charval1=varcharval;
SELECT charval1 || \'X\' AS res FROM chartest;
SELECT CHAR_LENGTH(charval1 || charval2) AS res FROM chartest;
SELECT CHAR_LENGTH(charval1) + CHAR_LENGTH(charval2)
  AS res
  FROM chartest;

Expected results, after CREATE and INSERTs:

SELECT * FROM chartest; -- should show two rows
CHARVAL1   CHARVAL2   VARCHARVAL
========== ========== ==============================
aaa        aaa        aaa
aaaaaa     aaa        aaa
 
 
DELETE FROM chartest WHERE charval1=\'aaaaaa\';


SELECT * FROM chartest; -- should show one row
CHARVAL1   CHARVAL2   VARCHARVAL
========== ========== ==============================
aaa        aaa        aaa


SELECT * FROM chartest WHERE charval1=varcharval;
CHARVAL1   CHARVAL2   VARCHARVAL
========== ========== ==============================
aaa        aaa        aaa


SELECT charval1 || \'X\' FROM chartest AS res;
    res
===========
aaa       X


SELECT CHAR_LENGTH(charval1 || charval2) AS res FROM chartest;
    res
===========
         20


SELECT character_length(charval1) + character_length(charval2)
AS res
FROM chartest;
    res
============
          20