SQL Dialects Reference/Print version

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

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

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

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

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

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

SQL version	Feature	Standard SQL:2011	DB2	Firebird	Ingres	Linter	MSSQL	MySQL	MonetDB	Oracle Vers. 11.x	PostgreSQL	SQLite	Virtuoso
?	Integer (1 byte)		N/A	N/A	tinyint integer1	N/A	tinyint (always unsigned)	TINYINT	TINYINT	NUMBER(1)	"char" (including quotation marks)	INTEGER	TINYINT
?	Integer (2 bytes)		SMALLINT	SMALLINT	smallint integer2	SMALLINT	smallint	SMALLINT	SMALLINT	NUMBER(2)	SMALLINT INT2	INTEGER	SMALLINT
?	Integer (3 bytes)		N/A	N/A	N/A	N/A	N/A	MEDIUMINT	N/A	NUMBER(3)	N/A	INTEGER	N/A
?	Integer (4 bytes)		INTEGER	INTEGER	integer integer4	INT INTEGER	int	INT	INT	NUMBER(4)	INT INTEGER INT4	INTEGER	INT INTEGER
?	Integer (8 bytes)		BIGINT	BIGINT	bigint integer8	BIGINT	bigint	BIGINT	BIGINT	INTEGER	BIGINT INT8	INTEGER	BIGINT
?	Float (single precision, 4 bytes)		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)		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	FLOAT
?	Fixed precision (p) and scale (s) number		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	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

Data structure definition/Data types/Character types

Character types

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	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:

Data structure definition/Data types/Date and time types

Date and time types

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	DATE
?	Time only	TIME	TIME	TIME	TIME [WITHOUT TIME ZONE]	DATE DATETIME	time	TIME	TIME	no	TIME [WITHOUT TIME ZONE]	TEXT	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	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

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	?	?	$2^{31}-1$	$2^{16}$	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	?	?	$2^{31}-1$	$2^{16}$	about 2 GB	4 GB - 1 byte	?	1 000 000 000	?

Notes:

Data structure definition/Data types/Misc types

Misc types

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

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"	Case sensitivity is set by a per-database option.
MySQL	`identifier` "identifier"
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:

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

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

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

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

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

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

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

MySQL

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

OpenLink Virtuoso

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

-- or:

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

Oracle

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

create table t1 (col1 serial primary key);

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

SQLite

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

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

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 $x/y$	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)	SQRT(x)	N/A	SQRT(x)
?	Exponent of x ($e^{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 ($x^{y}$)	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()	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)

Functions and expressions/Math functions/Aggregate functions

Aggregate functions

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.

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	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)	STRING_AGG(x, y) since 9.0	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	?

Functions and expressions/Date and time functions

Date and time functions

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)	?	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)	?	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)	?	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)	?	DATEPART(hour, x)	HOUR(x) EXTRACT(HOUR FROM x)	EXTRACT(HOUR FROM x)	EXTRACT(HOUR FROM x)	EXTRACT(HOUR FROM x)	strftime('%H', x)	?

Functions and expressions/String functions

String functions

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)	CONCAT(list) arg1 + arg2	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)	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)	SOUNDEX(x)	SOUNDEX(x)	N/A	N/A	N/A	SOUNDEX(x)	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()

Functions and expressions/Misc expressions

Misc functions

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

Standard	Subquery (ANSI calls it <derived table>) alias is mandatory: 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

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	SELECT expressions VALUES (expressions)
SQLite	SELECT expressions VALUES ("expressions")
Virtuoso	?

Select queries/Limiting results of select query

Limiting number of rows returned

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	SELECT FIRST num_rows columns FROM table SELECT columns FROM table OFFSET start_row FETCH {FIRST|NEXT} num_rows ROWS ONLY
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 = 0): 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

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

• PostgreSQL
• SQLite

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

Write queries/Replace query

Replace query

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

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

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

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

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

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: CALL syntax can't pass BOOLEAN and other PL/SQL-only types:
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)

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()
XMLForest()	Create an XML Fragment from passed-in components.	XMLForest()	N/A	xmlforest()	xmlforest()	Planned (patch exists)	N/A	XMLForest()	XMLForest()	FOR XML clause
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
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()
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
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()
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
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()

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

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.

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	?	?	?	?	?

Command-line SQL query

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	?	sqlcmd options	mysql options [database]	mclient -d database	?	psql options [database]	sqlite3 filename options	?
?	Authentication to target server	?	?	?	-uusername -Ppassword -Ggroup -Rrole	?	-S host -d database -U username -P password	-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	?	?	?	N/A	?	-i file	N/A	file	?	-f file	-init file	?
?	Output to file instead of stdout	?	?	?	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

Standards

• 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

• DB2 Version 9 Information Center
• MonetDB documentation
• MySQL 5.0 reference manual
• PostgreSQL 8.2 reference manual
• SQLite documentation
• Firebird / Interbase 6 language reference (ZIP archive, 8.9MB)
• OpenLink Virtuoso Universal Server Documentation
• Oracle Database 10g2 SQL Reference
• Oracle Database 12g1 SQL Reference
• Microsoft SQL Server Documentation
• Linter SQL Server Documentation (English, old version);Linter SQL Server Documentation (Russian)

Single implementation references and cheat sheets

• Oracle PL/SQL Cheatsheet
• Oracle XML Cheatsheet

Comparisons

• 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

• Converting MySQL to PostgreSQL