SQL Dialects Reference/Print version

From Wikibooks, open books for an open world
Jump to navigation Jump to search


SQL Dialects Reference

The current, editable version of this book is available in Wikibooks, the open-content textbooks collection, at
https://en.wikibooks.org/wiki/SQL_Dialects_Reference

Permission is granted to copy, distribute, and/or modify this document under the terms of the Creative Commons Attribution-ShareAlike 3.0 License.

Introduction

Preamble[edit | edit source]

SQL (Structured Query Language) is one of the oldest programming languages in existence, first versions of which date back to 1969. Unfortunately, despite SQL being standardized since 1986, a lot of different implementations exist. They deviate more or less from each other, making developing applications that would work with a range of different SQL servers particularly difficult.

This wikibook is a compact comparative reference for several SQL language dialects. It lists particular common tasks and problems resolved in terms of several popular SQL server implementations. When possible, it tries to emphasize an universal solution. When it's not possible, it tries to list best practices.

Two main goals for the book are compactness and completeness. Obvious information, e.g. that a particular function or query does the same thing on all implementations, will not be listed. However, when some implementation(s) have important (even minor) deviations that can be a major pitfall for developers, such information definitely should be listed.

Who should use this book[edit | edit source]

Target audience is:

  • Developers who know a single dialect of SQL and want to develop for other SQL implementation right away (it's a simple way to).
  • Developers who port a ready project from one SQL implementation to another or a bunch of SQL implementations.
  • Developers who want their applications to be portable on a set of SQL servers (it's particularly useful for developers of open source projects that want to ensure that their application can be used on a variety of platforms).
  • System administrators who have to support a wide range of SQL servers and don't want to memorize all possible queries and nuances for every server.
  • Project managers or lead developers that want to estimate what SQL server is best for their project, given a list of requirements.

Who shouldn't use this book[edit | edit source]

This book is not a general learning course, not a comprehensive manual - it's a quick and compact reference that assumes that the reader knows basic concepts of SQL and precisely what is needed. The book discusses fairly advanced topics and minor, but important differences in SQL implementations that beginners / learners generally don't have to worry about.

Technical notes[edit | edit source]

Always where it is possible, a book tries to reference a web-published documentation, so a reader can always check particular detail, so a page is usually full of external documentation links.

As the full names of implementations are pretty long and inconvenient to spell out thoroughly every time, we'll use common abbreviations (shown in bold) to distinguish SQL dialects. Also, we only provide links to corresponding Wikipedia articles here, not on every occasion.

Many pages in this book include comparison tables that can be pretty long. It is recommended to install a comparison table extension to view these tables comfortably.

SQL implementations covered in this book[edit | edit source]

Several SQL dialects are covered in this book. Note that in all cases of particular implementations, only the latest stable version is described.

  • Standard. There are several versions of SQL standard (SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2011). The book would list all popular practices to do a job in all implementations, particularly emphasizing SQL versions that a particular solution works for (i.e. if some feature became standard since version X, it would be stated that such solution works since version X).
  • DB2 (IBM DB2).
  • Firebird (Firebird 2.5). An open-source RDBMS forked from sources of InterBase that were released by Borland on July 25, 2000. Firebird differs from Interbase and tries to be close to SQL standard as much as possible
  • MonetDB An open-source column-store. (MonetDB. V11)
  • MySQL (MySQL 5.0)
  • MSSQL (Microsoft SQL Server 2008). A proprietary RDBMS produced by Microsoft, targeting enterprise market. Original codebase was derived from Sybase SQL Server, but was mostly rewritten. Documentation is available in MSDN in a well-structured form, quite easy to reference.
  • Oracle (Oracle Database 11g2)
  • PostgreSQL (PostgreSQL 13)
  • SQLite.
  • Virtuoso (OpenLink Virtuoso running on Virtuoso Universal Server).
  • Linter


Data structure definition/Data types/Numeric types

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Integer (1 byte) [1] N/A N/A tinyint
integer1
N/A tinyint (always unsigned) TINYINT TINYINT NUMBER(1) "char" (including quotation marks) INTEGER[2] TINYINT
? Integer (2 bytes) [1] SMALLINT SMALLINT smallint
integer2
SMALLINT smallint SMALLINT SMALLINT NUMBER(2) SMALLINT
INT2
INTEGER[2] SMALLINT
? Integer (3 bytes) [1] N/A N/A N/A N/A N/A MEDIUMINT N/A NUMBER(3) N/A INTEGER[2] N/A
? Integer (4 bytes) [1] INTEGER INTEGER integer
integer4
INT
INTEGER
int INT INT NUMBER(4) INT
INTEGER
INT4
INTEGER[2] INT
INTEGER
? Integer (8 bytes) [1] BIGINT BIGINT bigint
integer8
BIGINT bigint BIGINT BIGINT INTEGER BIGINT
INT8
INTEGER[2] BIGINT
? Float (single precision, 4 bytes) [3] REAL FLOAT float4
real
float(23)
FLOAT
REAL
FLOAT(n), n≤24
real
float(24)
FLOAT REAL BINARY_FLOAT REAL
FLOAT(24)
N/A REAL
? Float (double precision, 8 bytes) [3] DOUBLE
FLOAT
DOUBLE PRECISION float
float8
double precision
float(53)
DOUBLE
DOUBLE PRECISION
FLOAT(n), n>24
float
float(53)
DOUBLE FLOAT
DOUBLE
BINARY_DOUBLE DOUBLE PRECISION
FLOAT(53)
REAL[2] FLOAT
? Fixed precision (p) and scale (s) number [1] DECIMAL(p, s)
NUMERIC(p, s)
DECIMAL(p, s)
NUMERIC(p, s)
DECIMAL(p, s)
NUMERIC(p, s)
DECIMAL(p, s)
DEC(p,s)
NUMERIC(p, s)
NUMBER(p,s)
decimal(p, s)
numeric(p, s)
DECIMAL(p, s)
NUMERIC(p, s)
DECIMAL(p, s)
NUMERIC(p, s)
NUMBER(p, s)/NUMBER(p)/NUMBER
DECIMAL(p, s)/DECIMAL(p)/DECIMAL
NUMERIC(p, s)/NUMERIC(p)/NUMERIC
FLOAT(p)
DECIMAL(p, s)
NUMERIC(p, s)
N/A[2] DECIMAL(p, s)
NUMERIC(p, s)
? Fixed precision p and s limits no limits p ≤ 31, 0 ≤ s ≤ p p ≤ 18, 0 ≤ s ≤ p p ≤ 31, 0 ≤ s ≤ p p ≤ 30, 0 ≤ s ≤ p, s ≤ 10, p-s ≤ 20 p ≤ 38, 0 ≤ s ≤ p p ≤ 65, 0 ≤ 30 ≤ s ≤ p p ≤ 18, 0 ≤ s ≤ p p ≤ 38, -84 ≤ s ≤ 127 (for negative scale the least significant digit is on the left side of the decimal point) p ≤ 1000, 0 ≤ s ≤ p
? UNSIGNED support No No No No No No Yes No Yes No No No
? Set display width in integer No No No No No No Yes No No No No No
  1. a b c d e f SMALLINT, INTEGER, INT, BIGINT, DECIMAL and NUMERIC. See: Wikibook SQL
  2. a b c d e f g uses dynamic typing; allows any type name
  3. a b FLOAT, REAL, DOUBLE PRECISION. See: Wikibook SQL


Data structure definition/Data types/Character types

Character types[edit | edit source]

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Variable-length with limit VARCHAR(n) VARCHAR(n) VARCHAR(n) VARCHAR(n)
NVARCHAR(n)
VARCHAR(n)
NVARCHAR(n)
VARCHAR[(n or max)]
NVARCHAR[(n or max)]
VARCHAR(n) VARCHAR(n)
CHARACTER VARYING(n)
VARCHAR2(n)
VARCHAR(n) is slated for deprecation
character varying(n)
varchar(n)
TEXT[1] VARCHAR(n)
? Variable-length with limit, max size no limit ? 32765 ? ? 2 GB with (max) or 8000 with the fixed limit 65535 about 2 GB 4000 bytes (32767 bytes if MAX_STRING_SIZE = EXTENDED - Oracle 12G) about 1 GB no limit (enforced by global, default = 1M) ?
? Fixed-length, blank padded string CHAR(n) CHAR(n) CHAR(n) CHAR(n)
NCHAR(n)
CHAR(n)
NCHAR(n)
CHAR(n)
NCHAR(n)
CHAR(n) ? CHAR(n) character(n)
char(n)
N/A CHAR(n)
? Fixed-length, blank padded string, max size no limit ? 32767 ? ? 8000 255 ? 2000 about 1 GB N/A ?

Notes:

  1. uses dynamic typing; allows any type name


Data structure definition/Data types/Date and time types

Date and time types[edit | edit source]

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL
Vers. 5.x
MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Date only DATE DATE DATE DATE
ANSIDATE
INGRESDATE
DATE,
DATETIME
date DATE DATE DATE (includes TIME !) DATE TEXT[1] DATE
? Time only TIME TIME TIME TIME [WITHOUT TIME ZONE] DATE
DATETIME
time TIME TIME no TIME [WITHOUT TIME ZONE] TEXT[1] TIME
? Time with time zone TIME WITH TIME ZONE ? ? TIME WITH TIME ZONE ? N/A N/A TIME WITH TIME ZONE no TIME WITH TIME ZONE N/A N/A
? Date and time without time zone TIMESTAMP TIMESTAMP TIMESTAMP DATE
TIMESTAMP
ANSIDATE
INGRESDATE
DATE
DATETIME
datetime2
datetime
smalldatetime
TIMESTAMP TIMESTAMP
TIMESTAMP WITH TIME ZONE
DATE
TIMESTAMP
TIMESTAMP [WITHOUT TIME ZONE] TEXT
REAL
INTEGER[1]
DATETIME
TIMESTAMP
? Date and time with time zone TIMESTAMP WITH TIME ZONE ? ? ? ? datetimeoffset N/A TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE N/A N/A
? Time interval INTERVAL DAY TO SECOND(n)

INTERVAL YEAR TO MONTH
? ? INTERVAL DAY TO SECOND(n)

INTERVAL YEAR TO MONTH
? N/A Not available as data type, only as data values. INTERVAL INTERVAL DAY[(day_precision)] TO SECOND[(fractional_second_precision)]

INTERVAL YEAR [(year_precision)] TO MONTH
INTERVAL N/A ?


Data structure definition/Data types/Large object types

Large object types[edit | edit source]

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL
Vers. 5.x
MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Character LOBs CLOB(n) CLOB(n) BLOB SUB_TYPE TEXT CLOB,
TEXT,
LONG VARCHAR
BLOB varchar(max)
nvarchar(max)
text
ntext
TEXT STRING,
CLOB,
TEXT,
CHARACTER LARGE OBJECT
CLOB text TEXT LONG VARCHAR
? Character LOBs max size, bytes no limit ? about 4 GB ? ? about 2 GB 4 GB - 1 byte about 1 GB 1 000 000 000 ?
? Binary LOBs BLOB(n) BLOB(n) BLOB SUB_TYPE BINARY BLOB
LONG BYTE
BLOB varbinary(max)
image
BLOB(n)
BINARY LARGE OBJECT
BLOB(n) BLOB bytea
lo
BLOB LONG VARBINARY
? Binary LOBs max size, bytes no limit ? about 4 GB ? ? about 2 GB 4 GB - 1 byte ? 1 000 000 000 ?

Notes:

  1. a b c the built-in date/time functions can use these types for storing values


Data structure definition/Data types/Misc types

Misc types[edit | edit source]

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL
Vers. 5.x
MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Boolean boolean N/A N/A ? BOOLEAN BIT BOOLEAN
TINYINT(1)
BOOLEAN No BOOLEAN N/A BIT
? Boolean storage size N/A ? ? ? ? 8 columns in a single table = 1 byte 1 byte 1 byte N/A 1 byte N/A ?
? UUID N/A N/A ? ? ? uniqueidentifier ? UUID Not a data type, but a function SYS_GUID() will return a UUID as a RAW data type. UUID N/A IRI
? Row version N/A ? ? ? ? TIMESTAMP
ROWVERSION
? ? ? ? N/A ?
? Any data type N/A ? ? ? ? SQL_VARIANT ? ? ANYTYPE
ANYDATA
ANYDATASET
? (any) ?


Data structure definition/Delimited identifiers

Delimited identifiers[edit | edit source]

There are regular identifiers and delimited identifiers to denominate database objects like tables or columns. You must apply delimited identifiers if you want to use SQL keywords like 'FROM' as identifiers or special characters within its name. Delimited identifiers are labeled by the following techniques.

Dialect Sample Comment
Standard "identifier" The SQL standard calls these "delimited identifiers". The SQL standard requires that delimited identifiers are case-sensitive, although not all servers enforce this.
DB2 "identifier" Case sensitive per standard and also allows special characters.
Firebird "identifier" Case sensitive per standard.
Ingres "identifier" Case sensitivity for delimited identifiers is specified when a database is created. For compliance with ANSI/ISO Entry SQL-92, delimited identifiers must be case sensitive.
Linter "identifier" Case sensitive per standard.
MonetDB "identifier" Case sensitive per standard.
MSSQL [identifier]
"identifier"[1]
Case sensitivity is set by a per-database option.
MySQL `identifier`
"identifier"[2]
Oracle "identifier" Case sensitive per standard. Case insensitive when not double-quoted: They are converted to uppercase before the execution of any statement, e.g: 'CrEAtE table MyTable' is converted to 'CREATE TABLE MYTABLE'. This conforms to the standard.
PostgreSQL "identifier" Case sensitive per standard. Case insensitive when not double-quoted (converted to lower case - while it's upper case per standard).
SQLite [identifier]
"identifier"
'identifier'
identifier
Never case sensitive, even when quoted. Bracketed keywords are always understood as identifiers. Double-quoted keywords are understood as identifiers if previously seen as such, but are otherwise interpreted as string literals. Single-quoted keywords are interpreted as string literals where such are allowed, but otherwise as identifiers. Some keywords may be used as identifiers even when not quoted.
Virtuoso "identifier"  

Notes:

  1. If the quoted_identifier option is set on — otherwise, "identifier" would be parsed as a string in single quotes.
  2. If running in ANSI mode.


Data structure definition/Auto-increment column

A short hint: In most cases auto-increment columns are used as Primary Key columns. In the SQL standard the junction of the two concepts is not mandatory.

SQL Standard[edit | edit source]

The SQL standard defines two ways to generate auto-increment values. First, there are identity columns as an extension to exact numeric types. The syntax is: "GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY". Second, the use of sequences in combination with triggers is standardized.

CREATE TABLE t1 (col1 DECIMAL GENERATED ALWAYS AS IDENTITY);

DB2[edit | edit source]

Identity columns or sequences combined with triggers (comparison of both techniques).

CREATE TABLE t1 (col1 INT GENERATED ALWAYS AS IDENTITY);

--  or:

CREATE TABLE t1 (col1 INT);
CREATE SEQUENCE sequence_name;
CREATE TRIGGER insert_trigger
       NO CASCADE BEFORE INSERT ON t1
       REFERENCING NEW AS n
       FOR EACH ROW
  SET n.col1 = NEXTVAL FOR sequence_name;

Firebird[edit | edit source]

Is recommended to use sequences combined with triggers . From 3.0 there is Identity support.

SET TERM  ^;
CREATE TABLE t1(col1 INTEGER NOT NULL PRIMARY KEY)^
CREATE SEQUENCE sequence_name^
ALTER  SEQUENCE sequence_name RESTART WITH 0^

CREATE TRIGGER trigger_name FOR t1
BEFORE INSERT
AS
BEGIN
  NEW.col1 = NEXT VALUE FOR sequence_name;
END^

Linter[edit | edit source]

AUTOINC columns (maybe with RANGEs) or sequences combined with triggers.

CREATE TABLE t1 (col1 SMALLINT AUTOINC);
CREATE TABLE t2 (col1 INTEGER AUTOINC);
CREATE TABLE t3 (col1 BIGINT AUTOINC);

MonetDB[edit | edit source]

CREATE SEQUENCE sequence_name AS INT START WITH 1 INCREMENT BY 1;
CREATE TABLE t1 (
  col1 BIGINT PRIMARY KEY DEFAULT NEXT VALUE FOR sequence_name,
  col2 BIGINT AUTO_INCREMENT, 
  col3 BIGINT GENERATED ALWAYS AS IDENTITY (
           START WITH 100 INCREMENT BY 2
           NO MINVALUE MAXVALUE 1000
           CACHE 2 CYCLE)
);
CREATE TABLE ts (
  col1 SERIAL,  /* implies: INTEGER NOT NULL PRIMARY KEY DEFAULT NEXT VALUE FOR "sch"."seq_12345" */
  ...
);
CREATE TABLE tbs (
  col1 BIGSERIAL,  /* implies: BIGINT NOT NULL PRIMARY KEY DEFAULT NEXT VALUE FOR "sch"."seq_23456" */
  ...
);

MSSQL[edit | edit source]

CREATE TABLE t1 (col1 INT IDENTITY(1,1));

MySQL[edit | edit source]

CREATE TABLE t1 (col1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT);

OpenLink Virtuoso[edit | edit source]

IDENTITY (start with 1, increment by 1);
CREATE TABLE t1 (col1 INTEGER IDENTITY);

-- or:

CREATE TABLE t1 (col1 INTEGER IDENTITY (start with 1));

Oracle[edit | edit source]

CREATE TABLE t1 (col1 NUMBER PRIMARY KEY);
CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER trigger_name BEFORE INSERT ON t1 FOR EACH ROW
DECLARE
  max_id NUMBER;
  cur_seq NUMBER;
BEGIN
IF :NEW.col1 IS NULL THEN
  -- normal assignment of the next value in the sequence
  :NEW.col1 := sequence_name.NEXTVAL;
ELSE
  -- or allow the user to specify the value, so must advance the sequence to match
  SELECT GREATEST(COALESCE(MAX(col1), 0), :NEW.col1) INTO max_id FROM t1;
  WHILE cur_seq < max_id LOOP
    SELECT sequence_name.NEXTVAL INTO cur_seq FROM DUAL;
  END LOOP;
END IF;
END;

-- since Oracle 12.1:
CREATE TABLE t1 (col1 NUMBER GENERATED BY DEFAULT AS IDENTITY);

PostgreSQL[edit | edit source]

create table t1 (col1 serial primary key);

-- since postgres 10:
create table t1 (col1 integer generated by default as identity primary key);

SQLite[edit | edit source]

Both create an autoincrementing column; the AUTOINCREMENT keyword only prevents reusing deleted values.

CREATE TABLE t1 (col1 INTEGER PRIMARY KEY);
CREATE TABLE t1 (col1 INTEGER PRIMARY KEY AUTOINCREMENT);


Functions and expressions/Math functions/Trigonometric functions

Trigonometric functions[edit | edit source]

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL
Vers. 5.x
MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Arc sine N/A ASIN(x) ASIN(x) ASIN(X) ASIN(x) ASIN(x) ASIN(x) ASIN(x) ASIN(x) ASIN(x) N/A ASIN(x)
? Arc cosine N/A ACOS(x) ACOS(x) ACOS(x) ACOS(x) ACOS(x) ACOS(x) ACOS(x) ACOS(x) ACOS(x) N/A ACOS(x)
? Arc tangent of x N/A ATAN(x) ATAN(x) ATAN(x) ATAN(x) ATAN(x) ATAN(x) ATAN(x) ATAN(x) ATAN(x) N/A ATAN(x)
? Arc tangent of x and y N/A ATAN2(x, y) ATAN2(x,y) ATAN2(x, y) ATAN2(x, y) ATN2(x, y) ATAN2(x, y)
ATAN(x, y)
ATAN2(x,y) ATAN2(x, y)
ATAN2(x / y)
ATAN2(x,y) N/A ATAN2(x, y)
? Sine N/A SIN(x) SIN(x) SIN(x) SIN(x) SIN(x) SIN(x) SIN(x) SIN(x) SIN(x) N/A SIN(x)
? Cosine N/A COS(x) COS(x) COS(x) COS(x) COS(x) COS(x) COS(x) COS(x) COS(x) N/A COS(x)
? Tangent N/A TAN(x) TAN(x) TAN(x) TAN(x) TAN(x) TAN(x) TAN(x) TAN(x) TAN(x) N/A TAN(x)
? Cotangent N/A COT(x) COT(x) N/A N/A COT(x) COT(x) COT(x) N/A COT(x) N/A COT(x)
? Hyperbolic sine N/A SINH(x) SINH(x) N/A SINH(x) N/A N/A SINH(x) SINH(x) N/A N/A N/A
? Hyperbolic cosine N/A COSH(x) COSH(x) N/A COSH(x) N/A N/A COSH(x) COSH(x) N/A N/A N/A
? Hyperbolic tangent N/A TANH(x) TANH(x) N/A TANH(x) N/A N/A TANH(x) TANH(x) N/A N/A N/A
? Hyperbolic arctangent N/A ATANH(x) ATANH(x) N/A ? N/A N/A N/A N/A N/A N/A ?


Functions and expressions/Math functions/Numeric functions

Numeric functions[edit | edit source]

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL
Vers. 5.x
MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Absolute value of x ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x)
? Sign of number x N/A SIGN(x) SIGN(x) SING(x) SIGN(x) SIGN(x) SIGN(x) SIGN(x) SIGN(x) SIGN(x) N/A SIGN(x)
? Modulus (remainder) of MOD(x, y) MOD(x, y) MOD(x, y) MOD(x, y) MOD(x, y) x % y x % y
MOD(x, y)
x % y
MOD(x, y)
MOD(x, y) x % y
MOD(x, y)
x % y MOD(x, y)
? Smallest integer >= x CEILING(x)
CEIL(x)
CEILING(x)
CEIL(x)
CEILING(x)
CEIL(x)
CEIL(x)
CEILING(x)
CEIL(x) CEILING(x) CEILING(x)
CEIL(x)
CEILING(x)
CEIL(x)
CEIL(x) CEILING(x)
CEIL(x)
N/A CEILING(x)
? Largest integer <= x FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) N/A FLOOR(x)
? Round x (to precision of d digits) N/A ROUND(x, d) ROUND(x, d) ROUND(x, d) ROUND(x[, d]) ROUND(x[, d]) ROUND(x[, d]) ROUND(x, d) ROUND(x[, d]) ROUND(x[, d]) ROUND(x[, d]) ROUND(x)
? Truncate x to n decimal places N/A TRUNCATE(x, n)
TRUNC(x, n)
TRUNC(x[, n]) TRUNCATE(x, n)
TRUNC(x, n)
TRUNC(x[, d]) ROUND(x[, d], 1) TRUNCATE(x[, dn) sys.ms_trunc(x, n) TRUNC TRUNC(x[, y]) N/A N/A
? Square root of x () SQRT(x) SQRT(x) SQRT(x) SQRT(x) SQRT(x) SQRT(x) SQRT(x) SQRT(x) SQRT(x) SQRT(x) N/A SQRT(x)
? Exponent of x () EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) N/A EXP(x)
? Power () POWER(x, y) POWER(x, y) POWER(x, y) POWER(x, y)
x ** y
POWER(x, y) POWER(x, y) POW(x, y)
POWER(x, y)
POWER(x, y) POWER(x, y) POWER(x, y) N/A POWER(x,y)
? Natural logarithm of x LN(x) LN(x) LN(x) LOG(x)
LN(x)
LN(x) LOG(x) LN(x)
LOG(x)
LN(x)
LOG(x)
LN(x) LN(x) N/A LOG(x)
? Logarithm of x, base b N/A LOG(b, x) LOG(b, x) N/A LOG(b, x) LOG(x, b) LOG(b, x) LOG(b, x) LOG(b, x) LOG(b, x) N/A ?
? Logarithm, base 10 N/A LOG10(x) LOG10(x) N/A N/A LOG10(x) LOG10(x) LOG10(x) LOG(x) LOG(x) N/A LOG(x)
? Randomize, set seed to x N/A RAND(x) N/A SET RANDOM_SEED x RAND(x) RAND(x) RAND(x) RAND(x) random()[1] SETSEED(x) N/A RANDOMIZE([x])
? Generate floating-point random number between 0 and 1 N/A RAND() RAND() RANDOMF() RAND() RAND() RAND() CAST(RAND() as float) / 2147483648 dbms_random.value (returns number >= 0 and < 1)
dbms_random.value(37, 89) (returns a random number >= 37 and < 89.)
RANDOM() RANDOM() (between −263 and 263) RND()
? Highest number in a list N/A N/A MAXVALUE(list) ? GREATEST(list) N/A GREATEST(list) GREATEST(a, b) GREATEST(list) GREATEST(list) MAX(list) MAX(list)
? Lowest number in a list N/A N/A MINVALUE(list) ? LEAST(list) N/A LEAST(list) LEAST(a, b) LEAST(list) LEAST(list) MIN(list) MIN(list)

Notes[edit | edit source]

  1. The random() function in Oracle can be found in the built-in DBMS package dbms_random.


Functions and expressions/Math functions/Aggregate functions

Aggregate functions[edit | edit source]

Aggregate function operate on a group of values, returning single scalar value. The standard specifies that with the exception of VAR_POP, VAR_SAMP, STDDEV_POP and STDDEV_SAMP all aggregate function should be able to handle one of two additional quantifier before an argument: ALL (feature ID E091-06) and DISTINCT (feature ID E091-07). ALL is the default and can be omitted and DISTINCT means that only unique values would be passed in aggregate function. To make presentation more compact, these two quantifiers aren't discussed separately for every function, but specified as [DISTINCT|ALL] if function supports both of them.

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL
Vers. 5.x
MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Count all rows in a group COUNT(*) ... GROUP BY <grouping criterion> COUNT(*) COUNT(*) COUNT(*) ? COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*) COUNT(*) ?
? Count non-NULL values in x COUNT(x) COUNT([DISTINCT] x) COUNT([DISTINCT] x) COUNT([DISTINCT] x) COUNT([DISTINCT] x) COUNT([DISTINCT] x) COUNT([DISTINCT] x) COUNT([DISTINCT] x) COUNT([DISTINCT] x) COUNT([DISTINCT] x) COUNT([DISTINCT] x) ?
? Concatenate non-NULL values in x using y as separator N/A ? LIST([DISTINCT] x, y) ? ? N/A[1] GROUP_CONCAT([DISTINCT] x SEPARATOR y) listagg(x)
listagg(x, y)
sys.group_concat(x)
sys.group_concat(x, y)
since R11.2: LISTAGG(x, y)[2] STRING_AGG(x, y) since 9.0 [3] GROUP_CONCAT([DISTINCT] x [, y]) ?
? Sum of x SUM(x) SUM([DISTINCT] x) SUM(x) SUM(x) SUM([DISTINCT] x) SUM([DISTINCT] x) SUM(x) SUM([DISTINCT] x) SUM(x) SUM([DISTINCT] x) SUM([DISTINCT] x)
TOTAL([DISTINCT] x)
?
? Average of x AVG(x) AVG([DISTINCT] x) AVG(x) AVG(x) AVG([DISTINCT] x) AVG([DISTINCT] x) AVG([DISTINCT] x) AVG([DISTINCT] x) AVG(x) AVG([DISTINCT] x) AVG([DISTINCT] x) ?
? Minimum value in x MIN(x) MIN(x) MIN(x) MIN(x) MIN(x) MIN(x) MIN(x) MIN(x) MIN(x) MIN(x) MIN(x) ?
? Maximum value in x MAX(x) MAX(x) MAX(x) MAX(x) MAX(x) MAX(x) MAX(x) MAX(x) MAX(x) MAX(x) MAX(x) ?
? Standard deviation STDDEV_POP(x)
STDDEV_SAMP(x)
STDDEV([DISTINCT] x) STDDEV_POP(x)
STDDEV_SAMP(x)
STDDEV_POP(x) STDDEV([DISTINCT] x) STDEV(x)
STDEVP(x)
STD(x)
STDDEV(x)
STDDEV_POP(x)
sys.stddev_pop(x)
sys.stddev_samp(x)
STDDEV([DISTINCT|ALL] x) STDDEV(x)
STDDEV_POP(x)
STDDEV_SAMP(x)
N/A STDDEV(x, y)
? Variance VAR_POP(x)
VAR_SAMP(x)
VARIANCE([DISTINCT] x) VAR_POP(x)
VAR_SAMP(x)
? VARIANCE([DISTINCT] x) VAR(x)
VARP(x)
VARIANCE(x)
VAR_POP(x)
sys.var_pop(x)
sys.var_samp(x)
VARIANCE(x) VARIANCE(x)
VAR_POP(x)
VAR_SAMP(x)
N/A VAR(x)
? Population covariance of x and y COVAR_POP(x, y) COVARIANCE(x, y)
COVAR(x, y)
COVAR_POP(x, y) ? ? N/A N/A sys.covar_pop(x, y) COVAR_POP(x, y) COVAR_POP(x, y) N/A ?
? Sample covariance of x and y COVAR_SAMP(x, y) N/A COVAR_SAMP(x, y) ? ? N/A N/A sys.covar_samp(x, y) COVAR_SAMP(x, y) COVAR_SAMP(x, y) N/A ?
  1. Can be implemented using user-defined aggregator functions or several other approaches[1]
  2. Can be implemented using user-defined aggregator function, undocumented wmsys.wm_concat function and using several other approaches[2]
  3. Can be implemented for older versions using PostgreSQL arrays and user-defined aggregator functions[3]


Functions and expressions/Date and time functions

Date and time functions[edit | edit source]

See also: Date and time types

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL
Vers. 5.x
MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Current date CURRENT_DATE CURRENT DATE
CURRENT_DATE
CURRENT_DATE DATE('TODAY') SYSDATE, CURRENT_TIMESTAMP, UNIX_TIMESTAMP (incl time also) CAST(GETDATE() AS DATE)
CONVERT(DATE, GETDATE())
CURDATE()
CURRENT_DATE
CURRENT_DATE()
CURRENT_DATE TRUNC(CURRENT_DATE), TRUNC(SYSDATE) CURRENT_DATE CURRENT_DATE CURDATE
? Current time CURRENT_TIME CURRENT TIME
CURRENT_TIME
CURRENT_TIME TIME(DATE('NOW')) N/A N/A CURTIME, CURRENT_TIME CURRENT_TIME N/A CURRENT_TIME CURRENT_TIME CURTIME
? Current date and time CURRENT_TIMESTAMP CURRENT TIMESTAMP
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP, 'NOW' DATE('NOW') SYSDATE, CURRENT_TIMESTAMP, UNIX_TIMESTAMP GETDATE(), CURRENT_TIMESTAMP NOW(), CURRENT_TIMESTAMP NOW(), CURRENT_TIMESTAMP CURRENT_DATE, SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP NOW(), CURRENT_TIMESTAMP CURRENT_TIMESTAMP NOW
? Add interval to date date + arg date + arg arg1 + arg2 arg1 + arg2
arg1 = date, arg2 = time-interval;
date+'15 day'
arg1 + arg2
ADD_MONTHS
DATEADD DATE_ADD arg1 + interval in days or fractional days arg1 + interval in days or fractional days

ADD_MONTHS()

see more

arg1 + arg2 N/A DATEADD
? Subtract interval from date date - arg date - arg arg1 - arg2 arg1 - arg2
arg1 = date, arg2 = time-interval;
date-'3 month'
arg1 - arg2 DATEDIFF DATE_SUB arg1 - interval '1' DAY arg1 - arg2 (sysdate-interval '1' MONTH) arg1 - arg2 N/A DATEDIFF
? Date difference (date1 - date2) field date1 - date2 arg1 - arg2
DATEDIFF
date1 - date2;
interval( 'day', date1 - date2)
arg1 - arg2 DATEDIFF DATEDIFF CAST(arg1 - arg2 as bigint)/1000 (diff in seconds) arg1 - arg2 see more see more-2 AGE N/A DATEDIFF
? Last day of month ? date + 1 MONTH - DAY(date) DAYS LASTDAYMONTH LAST_DAY LAST_DAY N/A LAST_DAY ? LAST_DAY N/A N/A N/A
? Time zone conversion date AT TIME ZONE offset ? N/A ? N/A N/A CONVERT_TZ ? NEW_TIME TIMEZONE N/A TIMEZONE
? First weekday after date ? ? N/A ? NEXT_DAY N/A ? ? NEXT_DAY N/A N/A N/A
? Convert date to string CAST (x AS STRING) TO_CHAR(value, format)
VARCHAR_FORMAT(value, format)
CAST(value, datetype)
DATETOSTR
DATE_FORMAT(date, format)
char(date)
TO_CHAR(value, format) DATENAME DATE_FORMAT(value, format) CAST (x AS STRING) TO_CHAR TO_CHAR(value, format) STRFTIME(format, value) CAST
? Convert date to number N/A INT(date) EXTRACT ? TO_NUMBER(value)
DATESPLIT
DATEPART ? ? TO_NUMBER(TO_CHAR()) DATE_PART N/A CAST
? Convert string to date CAST (x AS DATE) DATE(value)
TIMESTAMP(value)
CAST DATE(string) TO_DATE(value, format)
TO_TIMESTAMP(value, format)
CAST ? CAST, TIMESTAMP 'yyyy-mm-dd HH:mm:ss' TO_DATE TO_DATE N/A STRINGDATE
? Extract year from DATE or DATETIME x EXTRACT(YEAR FROM x) ? EXTRACT(YEAR FROM x) YEAR(x)
DATE_PART(YEAR, x)[1]
? YEAR(x)
DATEPART(year, x)
YEAR(x)
EXTRACT(YEAR FROM x)
EXTRACT(YEAR FROM x) EXTRACT(YEAR FROM x) EXTRACT(YEAR FROM x) strftime('%Y', x) ?
? Extract month from DATE or DATETIME x EXTRACT(MONTH FROM x) ? EXTRACT(MONTH FROM x) MONTH(x)
DATE_PART(MONTH, x)[1]
? MONTH(x)
DATEPART(month, x)
MONTH(x)
EXTRACT(MONTH FROM x)
EXTRACT(MONTH FROM x) EXTRACT(MONTH FROM x) EXTRACT(MONTH FROM x) strftime('%m', x) ?
? Extract day of month from DATE or DATETIME x EXTRACT(DAY FROM x) ? EXTRACT(DAY FROM x) DAY(x)
DATE_PART(DAY, x)[1]
? DAY(x)
DATEPART(day, x)
DAYOFMONTH(x)
DAY(x)
EXTRACT(DAY FROM x)
EXTRACT(DAY FROM x) EXTRACT(DAY FROM x) EXTRACT(DAY FROM x) strftime('%d', x) ?
? Extract hour (0…23) from TIME or DATETIME x EXTRACT(HOUR FROM x) ? EXTRACT(HOUR FROM x) HOUR(x)
DATE_PART(HOUR, x)[1]
? DATEPART(hour, x) HOUR(x)
EXTRACT(HOUR FROM x)
EXTRACT(HOUR FROM x) EXTRACT(HOUR FROM x) EXTRACT(HOUR FROM x) strftime('%H', x) ?
  1. a b c d These functions are only applicable to ingresdate data type in Ingres; normal dates, represented by ansidate datatype should be converted to ingresdate first.


Functions and expressions/String functions

String functions[edit | edit source]

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

Function Since SQL Standard DB2 SQLite MonetDB MySQL PostgreSQL Firebird Virtuoso Oracle MSSQL Linter
Convert character x to ASCII N/A N/A ASCII(x) UNICODE(x) ASCII(x) ASCII(x) ASCII(x) ASCII_VAL(x) ASCII(x) ASCII(x) ASCII(x) N/A
Convert ASCII x to character N/A N/A CHR(x) CHAR(x) N/A CHAR(x) CHR(x) ASCII_CHAR(x) CHR(x) CHR(x) CHAR(x) CHR(x)
String concatenate 92 arg1 || arg2 arg1 || arg2
arg1 CONCAT arg2
arg1 || arg2 CONCAT (arg1 , arg2)
arg1 || arg2 ... || argN
CONCAT (multiple arguments) arg1 || arg2 arg1 || arg2 CONCAT(list) arg1 || arg2
CONCAT (only 2 arguments)[1]
CONCAT(list)
arg1 + arg2[2]
arg1||arg2
CONCAT(list)
arg1+arg2
Join items using a separator N/A N/A ? ? ? CONCAT_WS(separator, item1, item2, ...) CONCAT_WS(separator, item1, item2, ...) ? ? N/A CONCAT_WS(separator, item1, item2, ...) ?
Find first occurrence of substring search in str, starting from start 92 POSITION(search IN str) LOCATE(search, str[, start])
POSSTR(str, search)
INSTR(str, search) POSITION(search IN str) POSITION(search IN str)
INSTR(str, search)
LOCATE(search, str[, start])
POSITION(search IN str)
STRPOS(str, search)
POSITION(search IN str) SUBSTR INSTR(str, search[, start]) CHARINDEX(search, str[, start]) POSITION(search IN str)
INSTR(str, search, [start [,[n]])
Find first occurrence of pattern search in string str 2003 SUBSTRING_REGEX (search IN str) N/A N/A N/A N/A INSTR REGEXP_INSTR(str, search) PATINDEX(search, str) N/A
Convert x to lowercase 92 LOWER(x) LOWER(x)
LCASE(x)
LOWER(x) LOWER(x) LOWER(x)
LCASE(x)
LOWER(x) LOWER(x) LCASE(x) LOWER(x) LOWER(x) LOWER(x)
Convert x to uppercase 92 UPPER(x) UPPER(x)
UCASE(x)
UPPER(x) UPPER(x) UPPER(x)
UCASE(x)
UPPER(x) UPPER(x) UCASE(x)
UPPER(x)
UPPER(x) UPPER(x) UPPER(x)
Pad left side 2003 N/A LPAD(str, len [, fill]) LPAD LPAD LPAD N/A LPAD N/A LPAD
Pad right side 2003 N/A RPAD(str, len [, fill]) RPAD RPAD RPAD N/A RPAD N/A RPAD
Remove leading blank spaces from x 92 TRIM(LEADING [' '] FROM x) N/A LTRIM(x) LTRIM(str [, str]) LTRIM LTRIM TRIM(LEADING [' '] FROM x) LTRIM LTRIM LTRIM LTRIM
Remove trailing blank spaces from x 92 TRIM(TRAILING [' '] FROM x) N/A RTRIM(x) RTRIM(str [, str]) RTRIM RTRIM TRIM(TRAILING [' '] FROM x) RTRIM RTRIM RTRIM RTRIM
Remove leading and trailing blanks from x 92 TRIM(BOTH [' '] FROM x)
TRIM(x)
LTRIM(RTRIM(x))
or TRIM(x)
TRIM(x) TRIM(str [, str]) TRIM TRIM TRIM(BOTH [' '] FROM x)
TRIM(x)
TRIM TRIM LTRIM(RTRIM(x)) TRIM
Repeat str n times 2003 REPEAT(str, n) N/A REPEAT REPEAT REPEAT RPAD REPEAT RPAD REPLICATE REPEAT_STRING(str, n)
String of n spaces 2003 SPACE(n) N/A N/A SPACE(n) N/A RPAD SPACE(n) RPAD SPACE(n)
Convert number to string 2003 CHAR(num) CAST CAST CAST CAST TO_CHAR STR TO_CHAR
Substring from string str, starting from start, length of len 92 SUBSTRING(str FROM start [FOR len]) SUBSTR(str, len[, start]) SUBSTR(str, start [, len]) SUBSTRING SUBSTRING
SUBSTR
SUBSTRING(str FROM start [FOR len])
SUBSTR(str, start[, len])
SUBSTRING(str FROM start [FOR len]) SUBSTR SUBSTR(str,start[,len]) SUBSTRING(str, start, length) SUBSTRING(str,start[,len])
SUBSTRING(str FROM start [FOR len])
The same with SUBSTR instead of SUBSTRING
Replace characters REPLACE(string, from, to) REPLACE(str, from, to) REPLACE REPLACE REPLACE REPLACE(str, find, repl) REPACE REPLACE REPLACE REPLACE
Capitalize first letter of each word in string x N/A N/A INITCAP(x)[3] N/A N/A N/A INITCAP(x) N/A INITCAP(x) INITCAP(x) N/A INITCAP(x)
Translate string TRANSLATE(str, to, from) N/A N/A N/A TRANSLATE(str, from, to) N/A TRANSLATE(str, from, to) N/A TRANSLATE
Length of string x (in characters) 92 CHAR_LENGTH(x)
CHARACTER_LENGTH(x)
LENGTH(x) LENGTH(x) LENGTH(x) CHAR_LENGTH(x) CHAR_LENGTH(x)
CHARACTER_LENGTH(x)
CHAR_LENGTH(x)
CHARACTER_LENGTH(x)
LENGTH(x) LENGTH(x) LEN(x) LENGTH(x)
Length of string x (in bytes) 92 OCTET_LENGTH(x) LENGTH(x) LENGTH(CAST(x AS BLOB)) OCTET_LENGTH(x) LENGTH(x) OCTET_LENGTH(x) OCTET_LENGTH(x) LENGTH(x) LENGTHB(x) DATALENGTH(x) OCTET_LENGTH(x)
Greatest character string in list 2003 MAX GREATEST MAX MAX GREATEST N/A GREATEST
Least character string in list 2003 MIN LEAST MIN MIN LEAST N/A LEAST
Quote SQL in string x QUOTE(x) QUOTE(x) QUOTE(x) N/A QUOTE_LITERAL(x) q'quote_delimiter x quote_delimiter' QUOTENAME(x, '''')
Soundex index of string x SOUNDEX(x) SOUNDEX(x)[4] SOUNDEX(x) SOUNDEX(x)[5] N/A N/A N/A SOUNDEX(x)[6] SOUNDEX(x) N/A
Calculate MD5 hash from string x N/A N/A N/A MD5(x) MD5(x) N/A N/A DBMS_CRYPTO.HASH (UTL_RAW.CAST_TO_RAW(X), 2) HASHBYTES('MD5', x) N/A
Calculate SHA1 hash from string x N/A N/A N/A SHA1(x) N/A N/A N/A DBMS_CRYPTO.HASH (UTL_RAW.CAST_TO_RAW(X), 3) HASHBYTES('SHA1', x) N/A
Generate UUID N/A N/A N/A UUID() GEN_UUID() SYS_GUID() NEWID()
NEWSEQUENTIALID()
SYS_GUID()

Notes[edit | edit source]

  1. Oracle concatenation result does not get "eaten" by NULLs (unlike in ANSI SQL). Oracle documentation warns about potential future change in this behavior and recommends explicitly coalescing NULLs.
  2. MS SQL concat() result does not get "eaten" by NULLs (unlike in ANSI SQL). + result does not get "eaten" when SET CONCAT_NULL_YIELDS_NULL OFF;
  3. INITCAP is supported starting DB2 V9.7.
  4. Soundex function is omitted from SQLite by default. Only available if SQLite is built with -DSQLITE_SOUNDEX=1 compile-time option.
  5. MySQL uses original Soundex algorithm.
  6. Uses enhanced Soundex algorithm as defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth.


Functions and expressions/Misc expressions

Misc functions[edit | edit source]

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL
Vers. 5.x
MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Convert value val to data type type CAST(val AS type) ? CAST(val AS type) ? CAST(val AS type)
CASTexpression as type
TO_CHAR
TO_NUMBER
TO_DATE
CAST(val AS type)
CONVERT(type, val, style)
CONVERT(val, type)
CAST(val AS type)
CAST(val AS type)
CONVERT(val, type)
CAST(val AS type) CAST(val AS type)
val::type
CAST(val AS type) ?
? Replace NULL within a val with a fallback value, return val intact, if it's non-NULL COALESCE(val, fallback) ? ? ? NVL(val, fallback) COALESCE(val, fallback)
ISNULL(val, fallback)
IFNULL(val, fallback) COALESCE(val, fallback) COALESCE(val, fallback)
NVL(val, fallback)
COALESCE(val, fallback) IFNULL(val, fallback)
COALESCE(val, fallback)
?
? Return the first non-NULL value from a list of values (val1, val2, ...) COALESCE(val1, val2, ...) COALESCE(val1, val2) COALESCE(val1, val2, ...) ? COALESCE(val1, val2, ...) COALESCE(val1, val2, ...) COALESCE(val1, val2, ...) COALESCE(val1, val2, ...) COALESCE(val1, val2, ...) COALESCE(val1, val2, ...) COALESCE(val1, val2, ...) ?
? Convert particular values to NULL: return NULL if a = b otherwise keep a NULLIF(a, b) ? NULLIF(a, b) ? NULLIF(a, b) NULLIF(a, b) NULLIF(a, b) NULLIF(a, b) NULLIF(a, b) NULLIF(a, b) NULLIF(a, b) ?


Select queries/Subqueries

Subqueries in FROM clause[edit | edit source]

Standard Subquery (ANSI calls it <derived table>) alias is mandatory:[1]

SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions

DB2 ?
Firebird ?
Ingres ?
Linter ?
MonetDB
  • SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions
    • Subquery alias is mandatory
  • WITH common_table_expression AS (SELECT subquery expressions FROM subquery tables) SELECT expressions FROM common_table_expression WHERE conditions
MSSQL
  • SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions
    • Subquery alias is mandatory
  • WITH common_table_expression AS (SELECT subquery expressions FROM subquery tables) SELECT expressions FROM common_table_expression WHERE conditions
MySQL ?
Oracle SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) WHERE conditions
PostgreSQL
  • SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions
    • Subquery alias is mandatory
  • WITH common_table_expression AS (SELECT subquery expressions FROM subquery tables) SELECT expressions FROM common_table_expression WHERE conditions
SQLite
  • SELECT … FROM (SELECT …) [AS alias] WHERE …
  • WITH cte AS (SELECT …) SELECT … FROM cte WHERE …
Virtuoso ?


Select queries/Select without tables

Select without tables[edit | edit source]

Sometimes one needs to execute SQL scalar expressions without table context, i.e. make a query that would act as normal SELECT operator, evaluate given comma-separated expressions and return a table with single row and one or multiple columns (one for every individual expression). Obviously, expressions can't reference any columns from the tables, as there are none.

An example is determining the value of a mathematical function, using Oracle syntax:

SQL> select 4*atan(1) as "Arc tangent of 1 times 4" from dual;

Arc tangent of 1 times 4
------------------------
              3.14159265
Standard ?
DB2
  • VALUES
  • SELECT expressions FROM sysibm.sysdummy1
Firebird SELECT expressions FROM rdb$database
Ingres SELECT expressions
Linter SELECT expressions
MonetDB
  • SELECT expressions
  • VALUES ("expressions")
MSSQL
  • SELECT expressions
  • VALUES (expressions)
MySQL
  • SELECT expressions
  • SELECT expressions FROM dual
  • VALUES row_constructor_list
Oracle SELECT expressions FROM dual
PostgreSQL
SQLite
  • SELECT expressions
  • VALUES ("expressions")
Virtuoso ?


Select queries/Limiting results of select query

Limiting number of rows returned[edit | edit source]

Note that end_row = start_row + num_rows - 1

Standard SELECT columns FROM table FETCH FIRST num_rows ROWS ONLY

SELECT columns FROM table OFFSET start_row ROWS FETCH FIRST num_rows ROWS ONLY

DB2 SELECT columns FROM table FETCH FIRST num_rows ROWS ONLY
Firebird Versions > 2.0
SELECT columns FROM table ROWS start_row TO end_row

All versions

SELECT FIRST num_rows SKIP start_row columns FROM table
Ingres
Linter
SELECT columns FROM table FETCH FIRST num_rows
SELECT columns FROM table LIMIT start_row, num_rows
(rows are numbered from 0)
MonetDB SELECT columns FROM table LIMIT num_rows OFFSET start_row
MSSQL
  • SELECT columns FROM table OFFSET start_row ROWS FETCH { FIRST | NEXT } num_rows ROW[S] ONLY
  • Simple version (when start_row = 1):
SELECT TOP num_rows columns FROM table
MySQL Versions > 4.0.14
SELECT columns FROM table LIMIT num_rows OFFSET start_row

All versions

SELECT columns FROM table LIMIT start_row, num_rows
Oracle Simple query
-- Notice: Will not work, if start_row > 1, since the first row will return false, and the cursor will terminate.
SELECT columns FROM table WHERE rownum >= start_row AND rownum <= end_row

This works fine without specifying start_row:

SELECT columns FROM table WHERE rownum <= end_row

This also works, but suffix num column in resultset:

SELECT * FROM (
  SELECT temp.*, rownum num FROM table ORDER BY columns
  ) WHERE num >= start_row and num <= end_row

Since 12.1, similar to standards:

SELECT columns FROM table FETCH FIRST num_rows ROWS ONLY
SELECT columns FROM table OFFSET start_row ROWS FETCH FIRST num_rows ROWS ONLY

Full syntax: (Can have OFFSET, or FETCH, or both clauses)

SELECT column [, column2 …] FROM table [ORDER BY column2] 
[OFFSET start_row ROWS] 
[FETCH [FIRST|NEXT] [num_rows|percent PERCENT] ROWS [ONLY|WITH TIES]]
PostgreSQL SELECT columns FROM table LIMIT num_rows OFFSET start_row

or: The SQL standard syntax.

SQLite SELECT columns FROM table LIMIT num_rows OFFSET start_row
Virtuoso
SELECT TOP num_rows columns FROM table
SELECT TOP skip_rows,num_rows columns FROM table


Standard -
DB2 ?
Firebird ?
Ingres ?
Linter ?
MonetDB ?
MSSQL NOT IN()
MySQL NOT IN()
Oracle MINUS
PostgreSQL ?
SQLite ?
Virtuoso ?


Select queries/Hierarchical Queries

Hierarchical Queries[edit | edit source]

Hierarchical queries are a way to extract information from a table that is linked with itself.

Let's say we have the following table:


My example table: 

id     of type numeric 
father of type numeric, that references an id of other register of the same table 
data   rest of fields, etc

If we have the following values:


id     father        data 
50     null          The boss
51     50            The well positioned manager
52     50            Another well positioned manager 
53     51            The worker
54     52            Another worker
5      null          Other node 
10     5             The son of Other node

The values that "hang" from node 50 are the values 50, 51, 52, 53, 54 but not 5 nor 10.

  • DB2

or


  • Firebird / InterBase

  • Ingres, MySQL, MSSQL[2]


WITH RECURSIVE t AS (
    SELECT id, father FROM "table" WHERE id = 50 AND father IS NULL
UNION ALL
    SELECT t1.id, t1.father FROM t JOIN "table" t1 ON (t1.father = t.id)
)
SELECT * FROM t;
  • Oracle, Linter
SELECT * FROM table CONNECT BY id = PRIOR father START WITH id = 50
  1. https://jakewheat.github.io/sql-overview/sql-2011-foundation-grammar.html#table-primary
  2. MS SQL does not allow the RECURSIVE keyword


Write queries/Replace query

Replace query[edit | edit source]

Replace query inserts new row if no row with such primary key exists or updates existing row if it does. SQL:2003 standard introduced a MERGE statement to implement such functionality, while other implementations provide similar queries named "REPLACE" or so-called "Upsert" query (a portmanteau of UPDATE and INSERT).

Standard MERGE statement can be used to do a replace query:
MERGE INTO table_name1 USING table_name2 ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT columns VALUES (values)

Note that MERGE is much more powerful than just doing replace queries.

DB2 MERGE statement
MERGE INTO phonebook AS p
   USING ( VALUES ('john doe', '1234' ) ) AS v(name, extension)
   ON ( p.name = v.name )
   WHEN MATCHED
      UPDATE SET p.extension = v.extension
   WHEN NOT MATCHED
      INSERT VALUES ( v.name, v.extension )
Firebird MERGE statement
MERGE INTO phonebook B
USING (
  SELECT name
  FROM phonebook
  WHERE name = 'john doe') E
ON (B.name = E.name)
WHEN MATCHED THEN
  UPDATE SET B.extension = '1234'
WHEN NOT MATCHED THEN
  INSERT (name, extension)
  VALUES ('john doe', '1234);

Non-standard simplified form:

UPDATE OR INSERT INTO phonebook (name, extension)
VALUES ('john doe', '1234')
MATCHING (name)
Ingres ?
Linter ?
MonetDB ?
MSSQL MERGE statement (from version SQL Server 2008)
DECLARE @UnitMeasureCode nchar(3) = 'ABC'
DECLARE @Name varchar(25) = 'Test name'

MERGE INTO Production.UnitMeasure AS target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
    WHEN MATCHED THEN 
        UPDATE SET Name = source.Name
	WHEN NOT MATCHED THEN	
	    INSERT (UnitMeasureCode, Name)
	    VALUES (source.UnitMeasureCode, source.Name)
MySQL Allows 3 syntaxes: non-standard REPLACE query, (since 4.1) INSERT ... ON DUPLICATE KEY UPDATE, and a variant on IF EXISTS.
REPLACE [INTO] table [(columns)] VALUES (values)
INSERT INTO table (columns) VALUES (values) ON DUPLICATE KEY UPDATE column1=value1, column2=value2
IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' )
THEN UPDATE phonebook SET extension = '1234' WHERE name = 'john doe'
ELSE INSERT INTO phonebook VALUES( 'john doe','1234' )
END IF
Oracle MERGE statement
MERGE INTO phonebook B
USING (
  SELECT name_id
  FROM phonebook
  WHERE name = 'john doe') E
ON (B.name = E.name)
WHEN MATCHED THEN
  UPDATE SET B.extension = '1234'
WHEN NOT MATCHED THEN
  INSERT (B.name, B.extension)
  VALUES ('john doe', '1234);
  • Multi-statement form:
IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' )
UPDATE phonebook SET extension = '1234' WHERE name = 'john doe'
ELSE
INSERT INTO phonebook VALUES( 'john doe','1234' )
PostgreSQL Since version 9.5 INSERT INTO...ON CONFLICT... syntax inspired by MySQL:
INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
SQLite REPLACE statement:
REPLACE [INTO] table [(columns)] VALUES (values)

(always deletes the old row)

Virtuoso ?


Write queries/Insert results from select query into existing table

Insert results from select query into existing table[edit | edit source]

Problem: Insert results from a given select query (SELECT columns FROM table ...) into existing table (existing_table) of the same or compatible structure (as query result).

Standard INSERT INTO existing_table SELECT columns FROM table ...
DB2 INSERT INTO existing_table SELECT columns FROM table ...
Firebird INSERT INTO existing_table SELECT columns FROM table ...
Ingres ?
Linter INSERT INTO existing_table SELECT columns FROM table ...
MonetDB INSERT INTO existing_table SELECT columns FROM table ...
MSSQL INSERT INTO existing_table SELECT columns FROM table ...
MySQL INSERT INTO existing_table SELECT columns FROM table ...
Oracle INSERT INTO existing_table SELECT columns FROM table ...
PostgreSQL INSERT INTO existing_table SELECT columns FROM table ...
SQLite INSERT INTO existing_table SELECT columns FROM table ...
Virtuoso INSERT INTO existing_table SELECT columns FROM table ...


Write queries/Save results from select query as new table

Save results from select query as new table[edit | edit source]

Problem: save results from an arbitrary SELECT query (SELECT columns FROM table ...) as a new permanent table new_table.

Standard ?
DB2 ?
Firebird ?
Ingres ?
Linter CREATE TABLE new_table AS SELECT columns FROM table ...
MonetDB CREATE TABLE new_table AS SELECT columns FROM table ... WITH DATA
MSSQL SELECT columns INTO new_table FROM table ...
MySQL CREATE TABLE new_table [AS] SELECT columns FROM table ...
Oracle CREATE TABLE new_table [AS] SELECT columns FROM table ...
PostgreSQL
  • SELECT columns INTO new_table FROM table ...
  • CREATE TABLE new_table [AS] SELECT columns FROM table ...
SQLite CREATE TABLE new_table [AS] SELECT columns FROM table ...
Virtuoso CREATE TABLE new_table [AS] SELECT columns FROM table ...


Write queries/Update returning

Update returning[edit | edit source]

This variant of UPDATE statement allows to return the values from the row(s) affected by the update.

Standard No support in SQL:2003
DB2 UPDATE ... RETURNING ... INTO ... since 9.7.

Only returns the updated values, see the documentation.

Firebird UPDATE ... RETURNING ... since 2.1.

Can return both old and new values using old. or new. prefixes but for at most one row, see the documentation.

Ingres ?
Linter ?
MonetDB ?
MSSQL UPDATE ... OUTPUT ... since MS SQL Server 2005.

Can return both old and new values using inserted. or deleted. prefixes, see the documentation.

MySQL No support in 5.7.
Oracle UPDATE ... RETURNING ... INTO ...

Supports retrieving values of multiple rows with BULK COLLECT INTO, see the documentation.

PostgreSQL UPDATE ... RETURNING ... since 8.2.

Only returns the updated values, see the documentation.

SQLite No support in 3.8.
Virtuoso ?


Transactions

Transactions[edit | edit source]

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL
Vers. 5.x
MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Start START TRANSACTION
[transaction characteristics]
Implicit SET TRANSACTION ? ? BEGIN TRAN[SACTION] BEGIN [WORK]
START [TRANSACTION]
START TRANSACTION
BEGIN TRANSACTION
Implicit BEGIN [WORK|TRANSACTION]
START TRANSACTION
BEGIN [TRANSACTION] Implicit
? Commit COMMIT [WORK] COMMIT [WORK] COMMIT [WORK] [EXEC SQL] COMMIT [WORK] ? COMMIT [WORK]
COMMIT TRAN[SACTION] [transaction_name]
COMMIT [WORK] COMMIT [WORK] [AND [NO] CHAIN] COMMIT [WORK] COMMIT [WORK|TRANSACTION] COMMIT [TRANSACTION]
END [TRANSACTION]
COMMIT WORK
? Rollback whole transaction ROLLBACK [WORK] ROLLBACK [WORK] ROLLBACK [WORK] [EXEC SQL] ROLLBACK [WORK] ? ROLLBACK [WORK]
ROLLBACK TRAN[SACTION]
ROLLBACK [WORK] ROLLBACK [WORK] [AND [NO] CHAIN] ROLLBACK [WORK] ROLLBACK [WORK|TRANSACTION] ROLLBACK [TRANSACTION] ROLLBACK WORK
? Define a savepoint x, while inside a transaction SAVEPOINT x SAVEPOINT x ON ROLLBACK RETAIN CURSORS SAVEPOINT x SAVEPOINT x ? SAVE TRAN[SACTION] x SAVEPOINT x SAVEPOINT x SAVEPOINT x SAVEPOINT x SAVEPOINT x N/A
? Rollback to given savepoint x ROLLBACK [WORK] TO SAVEPOINT x ROLLBACK [WORK] TO SAVEPOINT x ROLLBACK [WORK] TO [SAVEPOINT] x [EXEC SQL] ROLLBACK [WORK] TO x ? ROLLBACK TRAN[SACTION] x ROLLBACK [WORK] TO [SAVEPOINT] x ROLLBACK [WORK] [AND [NO] CHAIN] TO SAVEPOINT x ROLLBACK [WORK] TO [SAVEPOINT] x ROLLBACK [WORK|TRANSACTION] TO [SAVEPOINT] x ROLLBACK [TRANSACTION] TO [SAVEPOINT] x N/A
? Release (forget) savepoint x RELEASE SAVEPOINT x RELEASE [TO] SAVEPOINT x RELEASE SAVEPOINT x N/A ? N/A RELEASE SAVEPOINT x RELEASE SAVEPOINT x N/A RELEASE [SAVEPOINT] x RELEASE [SAVEPOINT] x N/A
? Prepare transaction named id for two-phase commit ? ? N/A ? ? ? ? N/A ? PREPARE TRANSACTION id N/A ?
? Commit prepared transaction named id ? ? N/A ? ? ? ? N/A ? COMMIT PREPARED id N/A ?
? Rollback prepared transaction named id ? ? N/A ? ? ? ? N/A ? ROLLBACK PREPARED id N/A ?
Start Commit Rollback Prepare Execute prepared
Linter Implicit
  • a COMMIT statement is executed
  • any DDL statement is executed
  • any statement is executed in AUTOCOMMIT mode
  • a ROLLBACK statement is executed
  • a user process is terminated abnormally or disconnects without COMMIT/ROLLBACK
? ?


Procedural language/Stored procedures

Stored procedures[edit | edit source]

Database Create syntax Calling
DB2
CREATE PROCEDURE procedure_name(...)
   BEGIN
   /* SQL code */
   END
CALL procedure_name(...)
Firebird
SET TERM $$ ;

CREATE PROCEDURE nameprocedure
  (input_parameter_name datatype, ... ) 
RETURNS 
  (output_parameter_name datatype, ... )
AS 
DECLARE VARIABLE variable_name datatype;
BEGIN
  /* SQL code */
END$$

SET TERM ; $$
SELECT ... FROM function_name(...)
EXECUTE function_name(...)
MonetDB
CREATE [ OR REPLACE ] PROCEDURE procedure_name(...)
BEGIN
   /* SQL code */
END


CREATE [ OR REPLACE ] PROCEDURE procedure_name(...)
EXTERNAL NAME  MAL_procedure_name
CALL procedure_name(...)
MySQL
DELIMITER $$

CREATE PROCEDURE nameprocedure
  (input_parameter_name datatype, ... )
BEGIN
  /* SQL code */
END$$

DELIMITER ;
CALL nameprocedure(...)
Linter
CREATE [OR REPLACE] PROCEDURE procedure_name([IN/OUT/INOUT] parameter_name datatype, ...) [RESULT datatype] [FOR DEBUG]
   DECLARE 
   /* variables declaration */
   CODE
   /* stored procedure code 
      (including SQL code)*/
   EXCEPTIONS
   /* exceptions declarations */
   END
CALL procedure_name(...)

EXECUTE procedure_name(...)

EXECUTE procedure_name(...) AS OWNER

SELECT procedure_name(...)
FROM ...
WHERE
procedure_name(...) = ...
OpenLink Virtuoso
Oracle
CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
    [EXCEPTION
        exception_section]
END [procedure_name];
CALL [PACKAGE_NAME.]procedure_name(...);

BEGIN
  [PACKAGE_NAME.]procedure_name(...);
END;
  • EXEC[ute] is another (client) option, which is implemented by some Oracle clients: [1]
  • CALL syntax can't pass BOOLEAN and other PL/SQL-only types: [1]
PostgreSQL
CREATE FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS return_type
AS $$
DECLARE
  variable_name datatype;
BEGIN
  /* SQL code */
END;
$$ LANGUAGE plpgsql;
SELECT function_name(...)
SQL Server
CREATE PROCEDURE nameprocedure
  (input_parameter_name datatype, ... )
AS
  /* SQL code */
GO
EXEC nameprocedure(...)
SQLite

N/A

N/A


Procedural language/User-defined functions

User-defined functions (UDF)[edit | edit source]

Database Create syntax Calling
DB2
CREATE FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS return_type
BEGIN
   /* SQL code */
END
VALUES function_name(...)

or

SELECT function_name(...) FROM ... WHERE function_name(...) = ...
MonetDB
CREATE [ OR REPLACE ] FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS return_type   /* return type can also be a table structure */
BEGIN
   /* SQL code */
END


CREATE [ OR REPLACE ] FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS return_type   /* return type can also be a table structure */
LANGUAGE { C | CPP | R | PYTHON3 | PYTHON3_MAP }
{
   function_body_in_language_syntax
}


CREATE [ OR REPLACE ] FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS return_type   /* return type can also be a table structure */
EXTERNAL NAME  MAL_function_name
SELECT function_name(...)

/* when the return type of the function is a table, following is allowed */
SELECT ... FROM function_name(...)
MySQL
DELIMITER $$

CREATE FUNCTION function_name
  (input_parameter_name datatype, ... )
RETURNS datatype
BEGIN
RETURN
  /* SQL code */
END$$

DELIMITER ;
SELECT function_name(...)
PostgreSQL
CREATE FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS datatype
AS $$
DECLARE
  variable_name datatype;
BEGIN
  /* SQL code */
END;
$$ LANGUAGE plpgsql;
SELECT function_name(...)
Firebird

UDFs are written in external tools and compiled into executable form.
They are dynamically loaded at runtime.
In order for server to pick them up, they need to be registered, like this:

DECLARE EXTERNAL FUNCTION function_name [datatype, ...]
RETURNS datatype
ENTRY_POINT 'entryname'
MODULE_NAME 'modulename';
SELECT function_name(...)
OpenLink Virtuoso
Oracle
CREATE OR REPLACE my_function(p_contract IN VARCHAR2, p_org_id IN VARCHAR2)
RETURN DATE
AS
  l_ret_eff_date DATE := SYSDATE;
BEGIN
  RETURN l_ret_eff_date;
END;
/
SELECT my_function('PARM1', 'ORG1')
FROM dual;
SQLite

N/A

N/A

SQL Server


SQL XML

The ISO publication (ISO/IEC 9075-14) is part of the SQL standard. Formerly it was named SQLX or SQL/XML. It defines the data type XML and functions acting on this data type as well as functions creating and handling XML objects (elements, attributes, ...) within this data type. Oracle denotes the data type XMLType.


Function Description DB2 SQLite MonetDB MySQL PostgreSQL Firebird OpenLink Virtuoso Oracle MSSQL
Version Software Version(s) Supported V9 N/A V11+ Planned 8.3+ N/A 3.5-4.x 9-10x Proprietary XML Extensions
XMLElement() Create an XML Element XMLElement() N/A xmlelement() Planned xmlelement() N/A XMLElement() XMLElement() .query()[2]
XMLForest() Create an XML Fragment from passed-in components. XMLForest() N/A xmlforest() xmlforest() Planned (patch exists) N/A XMLForest() XMLForest() FOR XML clause[3]
XMLColAttVal() Create an XML fragment and then expands the resulting XML so that each XML fragment has the name "column" with the attribute "name" N/A N/A Planned Planned N/A XMLColAttVal() XMLColAttVal() FOR XML clause[3]
ExtractValue() Takes as arguments an XML instance and an XPath expression and returns a scalar value of the resultant node. N/A N/A Planned Planned N/A ExtractValue() ExtractValue() .value()[4]
XMLTransform() Takes as arguments an XML instance and an XSL style sheet, which is itself a form of XML instance. It applies the style sheet to the instance and returns an XML. N/A N/A Planned Planned N/A XMLTransform() XMLTransform() N/A[5]
XMLSequence() Takes input and returns either a varray of the top-level nodes in the XML, or an XMLSequence type an XML document for each row of the cursor. N/A xmlsequence() Planned Planned N/A XMLSequence() XMLSequence() .nodes()[6]
XMLConcat() Takes as input a series of XML instances, concatenates the series of elements for each row, and returns the concatenated series. XMLConcat N/A N/A Planned Planned (patch exists) N/A XMLConcat() XMLConcat() N/A[7]
UpdateXML() Takes as arguments an XML instance and an XPath-value pair, and returns an XML instance with the updated value. N/A N/A Planned Planned N/A UpdateXML() UpdateXML() .modify()[8]

Notes[edit | edit source]

  1. a b Oracle SQL Stored Procedures Call vs. Execute discussion on CALL vs EXEC[ute] command in SQL*Plus and other clients.
  2. The MSSQL 2005 and higher xml data type .query() method performs this function.
  3. a b The MSSQL 2000 and higher FOR XML clause of the SQL SELECT statement performs a similar function.
  4. The MSSQL 2005 and higher xml data type .value() method performs this function.
  5. In MSSQL 2005 and higher the xml data type and SQLCLR can be used to create functions/procedures that simulate this functionality.
  6. The MSSQL 2005 and higher xml data type .nodes() method performs this function.
  7. In MSSQL 2005 and higher the + string concatenation operator can be used in conjunction with explicit conversions of xml data type instances to character data types to simulate this function.
  8. The MSSQL 2005 and higher xml data type .modify() method performs this function using XML DML.


Administration

While this topic is not directly about SQL language, it is sufficiently close to gather information about frequently undertaken administrative tasks together.

Dumping database[edit | edit source]

Dumping the database involves creation of a file that stores all the data (rows) and the schema for this data from the desired databases/tables. Most usually it's done as a series of SQL statements, combining CREATE DATABASE / CREATE TABLE statements to recreate the schema and INSERT statements to refill the rows — thus, usually the resulting file can be directly imported using command-line SQL client by simple execution of queries. However, sometimes other output formats are desired and some databases (at least MSSQL) lack the ability to serialize the database as SQL statements from command-line dumper client.

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Basic invocation ? ? ? copydb dbname options ? bcp database out file options mysqldump options database msqldump -d database ? pg_dump options database sqlite3 dbfile .dump ?
? Authentication to target server ? ? ? -uusername -P ? -U username -P password -S host -uusername -ppassword -hhost -Pport -uusername ? -Uusername -Wpassword N/A ?
? Save dump into file ? ? ? ? ? in basic invocation -rfile / --result-file=file redirect msqldump output ? -ffile / --file=file .output file
.once file
?
? Dump only schema ? ? ? ? ? N/A -d / --no-data msqldump -d "database" -D ? -s / --schema-only .schema ?
? Add DROP schema at start ? ? ? -add_drop ? N/A N/A ? -c N/A ?
? Prevent dumping of access privileges ? ? ? ? ? N/A N/A N/A ? -x / --no-privileges / --no-acl N/A ?
? Dump only data ? ? ? ? ? default -n -t / --no-create-db --no-create-info -t tablename ? -s / --data-only N/A ?
? Dump data as tab-separated ? ? ? ? ? default --tab=path / -Tpath ? ? ? .mode tabs ?
? Dump data as INSERT statements ? ? ? ? ? N/A default default ? -d .mode insert ?
? Dump everything as XML file ? ? ? ? ? N/A --xml / -X N/A ? N/A N/A ?
? Ordering of data ? ? ? ? ? -h "ORDER (column, ...)" --order-by-primary ? ? ? ? ?

Restoring database[edit | edit source]

Command-line SQL query[edit | edit source]

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Basic invocation ? ? ? sql options database ? isql options [-d database] mysql options [database] mclient -d database ? psql options [database] sqlite3 filename options ?
? Authentication to target server ? ? ? -uusername -Ppassword -Ggroup -Rrole ? -U username -P password -S instance -H host -uusername -ppassword -hhost -Pport ? ? -Uusername -Wpassword -hhost -pport N/A ?
? Execute query from command line and exit ? ? ? N/A ? -Q "query" -e query -sstatement ? -c query query ?
? Execute query from command line and continue with interactive prompt ? ? ? N/A ? -q "query" N/A -i ? N/A N/A ?
? Input file[1] ? ? ? N/A ? -i file N/A file ? -f file -init file ?
? Output to file instead of stdout[2] ? ? ? N/A ? -o file N/A ? ? -o file N/A ?
? Copy output also to file ? ? ? N/A ? N/A --tee=file ? ? -L file N/A ?

References[edit | edit source]

  1. Can be also done using shell < redirection
  2. Can be also done using shell > redirection


References

Standards[edit | edit source]

  • SQL-86
  • SQL-89
  • SQL-92 — full text is available
  • SQL:1999 — can be purchased from ISO
  • SQL:2003 — can be purchased from ISO
  • working draft of SQL:2008, downloadable from Whitemarsh Information Systems Corporation.
  • SQL:2008 — can be purchased from ISO
  • SQL:2011 — can be purchased from ISO

Official product documentations[edit | edit source]

Single implementation references and cheat sheets[edit | edit source]

Comparisons[edit | edit source]

  • Comparison of different SQL implementations
  • Comparison of Oracle, MySQL and PostgreSQL DBMS by Computing Division of Fermi National Accelerator Laboratory
  • SQL Function Reference: Oracle vs. SQL Server
  • Kline, Kevin E. (2001). "SQL Functions". SQL in a Nutshell. Cambridge, MS: O'Reilly. ISBN 1-56592-744-3. {{cite book}}: Unknown parameter |chapterurl= ignored (|chapter-url= suggested) (help); Unknown parameter |coauthors= ignored (|author= suggested) (help); Unknown parameter |month= ignored (help)
  • SQL Functions — MySQL vs Oracle comparison by Eric Brian
  • Standard SQL - an article by Peter Gulutzan that compares SQL standards support in DB2, MSSQL and Oracle

Migration[edit | edit source]