Comparison of SQL syntax[Encyclopedia]

Comparison of SQL syntax

From Wikipedia, the free encyclopedia

Jump to: navigation, search

This article gives a brief summary of various SQL syntax differences used in popular SQL engines:

• DB2
• Firebird / InterBase
• MySQL
• MSSQL
• Oracle
• PostgreSQL
• SQLite
• OpenLink Virtuoso

<script type="text/javascript"> // </script>

[edit] Data structure definition

[edit] Delimited identifiers

A method to somehow escape identifiers (table, column names, etc), so they do not have to obey regular identifier rules, such as not colliding with keywords, having some distinct characters, etc. 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/InterBase: "identifier", case sensitive per standard
• MSSQL and Sybase ASE: [identifier] (recommended) or "identifier" (if the quoted_identifier option is set on — otherwise, "identifier" would be parsed as a string in single quotes). Case sensitivity is set by a per-database option.
• MySQL: `identifier` (always) or "identifier" (if running in ANSI mode) or without escaping
• Oracle: "identifier" (note that Oracle's delimited identifiers are case-sensitive, and all regular identifiers are usually in upper case)
• PostgreSQL: "identifier" or without escaping
• SQLite: identifier without escaping
• OpenLink Virtuoso: "identifier"

[edit] Auto-increment column

• DB2: Identity columns or sequences combined with triggers (comparison of both techniques)

CREATE TABLE table_name ( column_name INT GENERATED ALWAYS AS IDENTITY )

or

CREATE SEQUENCE sequenze_name;

CREATE TABLE table_name ( column_name INT );

CREATE TRIGGER insert_trigger
   NO CASCADE BEFORE INSERT ON table_name
   REFERENCING NEW AS n
   FOR EACH ROW
   SET n.column_name = NEXTVAL FOR sequence_name;

• Firebird / Interbase:

CREATE table (column INTEGER NOT NULL PRIMARY KEY);;

CREATE GENERATOR table_gen;;
SET GENERATOR table_gen TO 0;;

CREATE TRIGGER t_column_gen FOR table_gen
BEFORE INSERT
AS
BEGIN
  NEW.column = GEN_ID(table_gen, 1);
END;; 

• MySQL: CREATE TABLE table (column INT NOT NULL PRIMARY KEY AUTO_INCREMENT)
• MSSQL: CREATE TABLE table (column INT IDENTITY(1,1))
• Oracle:

CREATE TABLE table (column NUMBER PRIMARY KEY);
CREATE SEQUENCE sequence START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER trigger BEFORE INSERT ON table REFERENCING NEW AS NEW FOR EACH ROW
BEGIN
  SELECT sequence.nextval INTO :NEW.ID FROM dual;
END;

• PostgreSQL: CREATE TABLE table (column SERIAL PRIMARY KEY)
• OpenLink Virtuoso

IDENTITY (start with 1, increment by 1);
CREATE TABLE table (column INTEGER IDENTITY);

or

CREATE TABLE table (column INTEGER IDENTITY (start with 1));

• SQLite: CREATE TABLE table (column INTEGER NOT NULL PRIMARY KEY [AUTOINCREMENT])

[edit] Functions and expressions

[edit] Math functions

[edit] Trigonometric functions

ANSI/ISO SQL Level	Feature-ID	Function	DB2	SQLite	MySQL	PostgreSQL	Firebird[1]	OpenLink Virtuoso	Oracle	MSSQL
		Arc sine	ASIN(x)	N/A	ASIN(x)	ASIN(x)	ASIN(x)	ASIN(x)	ASIN(x)	ASIN(x)
		Arc cosine	ACOS(x)	N/A	ACOS(x)	ACOS(x)	ACOS(x)	ACOS(x)	ACOS(x)	ACOS(x)
		Arc tangent of x	ATAN(x)	N/A	ATAN(x)	ATAN(x)	ATAN(x)	ATAN(x)	ATAN(x)	ATAN(x)
		Arc tangent of x and y	ATAN2(x, y)	N/A	ATAN2(x, y) ATAN(x, y)	ATAN2(x,y)	ATAN2(x,y)	ATAN2(x, y)	ATAN2(x, y)	ATN2(x, y)
		Sine	SIN(x)	N/A	SIN(x)	SIN(x)	SIN(x)	SIN(x)	SIN(x)	SIN(x)
		Cosine	COS(x)	N/A	COS(x)	COS(x)	COS(x)	COS(x)	COS(x)	COS(x)
		Tangent	TAN(x)	N/A	TAN(x)	TAN(x)	TAN(x)	TAN(x)	TAN(x)	TAN(x)
		Cotangent	COT(x)	N/A	COT(x)	COT(x)	COT(x)	COT(x)	N/A	COT(x)
		Hyperbolic sine	SINH(x)	N/A	N/A	N/A	SINH(x)	N/A	SINH(x)	N/A
		Hyperbolic cosine	COSH(x)	N/A	N/A	N/A	COSH(x)	N/A	COSH(x)	N/A
		Hyperbolic tangent	TANH(x)	N/A	N/A	N/A	TANH(x)	N/A	TANH(x)	N/A

Notes:

1. ↑ Firebird users need to register functions to make them available. The registration is done once for each database.

[edit] Numeric functions

ANSI/ISO SQL Level	Feature-ID	Function	DB2	SQLite	MySQL	PostgreSQL	Firebird	OpenLink Virtuoso	Oracle	MSSQL
SQL:2003/200n	T441	Absolute value of x	ABS(x)	ABS(x)	ABS(x)	ABS(x)	ABS(x)	ABS(x)	ABS(x)	ABS(x)
		Sign of number	SIGN(x)	N/A	SIGN(x)	SIGN(x)	SIGN(x)[1]	SIGN(x)	SIGN(x)	SIGN(x)
SQL:2003/200n	T441	Modulus (remainder) of x / y	MOD(x, y)	x % y	x % y MOD(x, y)	x % y MOD(x, y)	MOD(x,y)	MOD(x,y)	MOD(x, y)	x % y
SQL:2003/200n	T621	Smallest integer >= x	CEILING(x) CEIL(x)	N/A	CEILING(x) CEIL(x)	CEILING(x) CEIL(x)	CEILING(x)	CEILING(x)	CEIL(x)	CEILING(x)
SQL:2003/200n	T621	Largest integer <= x	FLOOR(x)	N/A	FLOOR(x)	FLOOR(x)	FLOOR(x)	FLOOR(x)	FLOOR(x)	FLOOR(x)
		Round x (to precision of d digits)	ROUND(x, d)	ROUND(x[, d])	ROUND(x[, d])	ROUND(x[, d])	ROUND(x,d)[1]	ROUND(x)	ROUND(x)	ROUND(x[, d])
		Truncate x to n decimal places	TRUNCATE(x, n) TRUNC(x, n)	N/A	TRUNCATE(x[, dn)	TRUNC(x[, y])	N/A	N/A	TRUNC	N/A
SQL:2003/200n	T621	Square root	SQRT(x)	N/A	SQRT(x)	SQRT(x)	SQRT(x)	SQRT(x)	SQRT(x)	SQRT(x)
SQL:2003/200n	T621	Exponent of x (ex)	EXP(x)	N/A	EXP(x)	EXP(x)	?	EXP(x)	EXP(x)	EXP(x)
SQL:2003/200n	T621	Power (xy)	POWER(x, y)	N/A	POWER(x, y) POW(x, y)	POWER(x, y)	POWER(x, y)[1]	POWER(x,y)	POWER(x, y)	POWER(x, y)
SQL:2003/200n	T621	Natural logarithm of x	LN(x)	N/A	LN(x)	LN(x)	LN(x)	LOG(x)	LN(x)	LOG(x)
		Logarithm, any base	LOG(b, x)	N/A	LOG(b, x)	LOG(b, x)	LOG(b, x)	LOG(x)	LOG(x)	N/A
		Logarithm, base 10	LOG10(x)	N/A	LOG10(x)	LOG(x)	LOG10(x)	LOG(x)	LOG(x)	LOG10(x)
		Randomize, set seed to x	RAND(x)	N/A	RAND(x)	SETSEED(x)	?	RANDOMIZE([x])	random()[2]	RAND(x)
		Generate random number	RAND()	RANDOM()	RAND()	RANDOM()	RAND()	RND()	N/A	RAND()
		Highest number in list	N/A	MAX(list)	GREATEST(list)	MAX() GREATEST(list)	MAX()	MAX(list)	GREATEST(list)	N/A
		Lowest number in list	N/A	MIN(list)	LEAST(list)	MIN() LEAST(list)	MIN(list)	MIN(list)	LEAST(list)	N/A
SQL-92	F261-04	Convert number if NULL	COALESCE		COALESCE	COALESCE	COALESCE	ISNULL	COALESCE	COALESCE

Notes:

1. ↑ Firebird users need to install rFunc library for ROUND and POWER functions.
2. ↑ The random() function in Oracle can be found in the built-in DBMS package dbms_random.

1.  

[edit] Aggregate functions

ANSI/ISO SQL Level	Feature-ID	Function	DB2	SQLite	MySQL	PostgreSQL	Firebird	OpenLink Virtuoso	Oracle	MSSQL
SQL:2003/200n (core)	E091-02	Count non-NULL values in x	COUNT([DISTINCT] x)	?	COUNT([DISTINCT] x)	COUNT(x)	COUNT([DISTINCT] x)	?	?	COUNT([DISTINCT] x)
SQL:2003/200n (core)	E091-05	Sum of x	SUM([DISTINCT] x)	?	SUM(x)	SUM(x)	SUM(x)	?	?	SUM([DISTINCT] x)
SQL:2003/200n (core)	E091-01	Average of x	AVG([DISTINCT] x)	?	AVG([DISTINCT] x)	AVG(x)	AVG(x)	?	?	AVG([DISTINCT] x)
SQL:2003/200n		Minimum value in x	MIN(x)	MIN(x)	MIN(x)	MIN(x)	MIN(x)	?	?	MIN(x)
SQL:2003/200n		Maximum value in x	MAX(x)	MAX(x)	MAX(x)	MAX(x)	MAX(x)	?	?	MAX(x)
SQL:2003/200n		Range (max - min) of x	MAX(x) - MIN(x)	?	N/A	?	?	?	?	N/A
SQL:2003/200n		Standard deviation	STDDEV([DISTINCT] x)	N/A	STD(x) STDDEV(x) STDDEV_POP(x)	STDDEV(x)	N/A	STDDEV(x, y)	STDDEV([DISTINCT|ALL] x)	STDEV(x) STDEVP(x)
SQL:2003/200n		Variance	VARIANCE([DISTINCT] x)	N/A	VARIANCE(x) VAR_POP(x)	VARIANCE(x)	N/A	VAR(x)	VARIANCE(x)	VAR(x) VARP(x)

[edit] Date and time functions

ANSI/ISO SQL Level	Feature-ID	Function	DB2	SQLite	MySQL	PostgreSQL	Firebird	OpenLink Virtuoso	Oracle	MSSQL
		Date addition	date + arg	N/A	DATE_ADD	arg1 + arg2	arg1 + arg2	DATEADD	arg1 + arg2	DATEADD
		Date subtraction	date - arg	N/A	DATE_SUB	arg1 - arg2	arg1 - arg2	DATEDIFF	arg1 - arg2	DATEDIFF
		Date difference	date1 - date2	N/A	DATEDIFF	AGE	arg1 - arg2	DATEDIFF	arg1 - arg2	DATEDIFF
		Last day of month	date + 1 MONTH - DAY(date) DAYS	N/A	LAST_DAY	N/A	LASTDAYMONTH[1]	N/A	LAST_DAY	N/A
		Time zone conversion		N/A	CONVERT_TZ	TIMEZONE	N/A	TIMEZONE	NEW_TIME	N/A
		First weekday after date		N/A		N/A	N/A	N/A	NEXT_DAY	N/A
SQL:2003/200n (core)	F051-04	Convert date to string	TO_CHAR(value, format) VARCHAR_FORMAT(value, format)	STRFTIME(format, value)	DATE_FORMAT(value, format)	TO_CHAR(value, format)	CAST(value, datetype) DATETOSTR[1]	CAST	TO_CHAR	DATENAME
SQL:2003/200n (core)	F051-04	Convert date to number	INT(date)	N/A		DATE_PART	EXTRACT	CAST	TO_NUMBER(TO_CHAR())	DATEPART
SQL:2003/200n (core)	F051-04	Convert string to date	DATE(value) TIMESTAMP(value)	N/A		TO_DATE	CAST	STRINGDATE	TO_DATE	CAST
SQL:2003/200n (core)	F051-06	Get current date	CURRENT_DATE	CURRENT_DATE	CURDATE, CURRENT_DATE	CURRENT_DATE	CURRENT_DATE	CURDATE	SYSDATE	N/A
SQL:2003/200n (core_	F051-01	Get current time	CURRENT_TIME	CURRENT_TIME	CURTIME, CURRENT_TIME	CURRENT_TIME	CURRENT_TIME	CURTIME	N/A	N/A
SQL:2003/200n (core)	F051-01	Get current date and time	CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	NOW(), CURRENT_TIMESTAMP	NOW(), CURRENT_TIMESTAMP	CURRENT_TIMESTAMP, 'NOW'	NOW	SYSTIMESTAMP	GETDATE()

Notes:

1. ↑ Firebird users might need to install rFunc library for some functions.

[edit] String functions

ANSI/ISO SQL Level	Feature-ID	Function	DB2	SQLite	MySQL	PostgreSQL	Firebird	OpenLink Virtuoso	Oracle	MSSQL
SQL:2003/200n		Convert character x to ASCII	ASCII(x)	N/A	ASCII(x)	ASCII(x)	ASCII_CHAR(x)[1]	ASCII(x)	ASCII(x)	ASCII(x)
SQL:2003/200n		Convert ASCII x to character	CHR(x)	N/A	CHAR(x)	CHR(x)	ASCII_VAL(x)[1]	CHR(x)	CHR(x)	CHAR(x)
SQL:2003/200n (core)	E021-07	String concatenate	string1 || string2 string1 CONCAT string2	arg1 || arg2	CONCAT (multiple arguments)	arg1 || arg2	arg1 || arg2	CONCAT(list)	arg1 || arg2 CONCAT (only 2 arguments)	arg1 + arg2
SQL:2003/200n (core)	E021-11	Find first occurrence of substring in string	LOCATE(search, string[, start]) POSSTR(string, search)	N/A	INSTR LOCATE POSITION	POSITION STRPOS	N/A	SUBSTR	INSTR	CHARINDEX
SQL:2003/200n		Find first occurrence of pattern in string		N/A			STRPOS[1]	INSTR	INSTR	PATINDEX
SQL:2003/200n (core)	E021-08	Convert x to lowercase	LOWER(x) LCASE(x)	LOWER(x)	LOWER(x) LCASE(x)	LOWER(x)	LOWER(x)[1]	LCASE(x)	LOWER(x)	LOWER(x)
SQL:2003/200n (core)	E021-08	Convert x to uppercase	UPPER(x) UCASE(x)	UPPER(x)	UPPER(x) UCASE(x)	UPPER(x)	UPPER(x)[1]	UCASE(x)/UPPER(x)	UPPER(x)	UPPER(x)
SQL:2003/200n		Pad left side		N/A	LPAD	LPAD	LPAD[1]	N/A	LPAD	N/A
SQL:2003/200n		Pad right side		N/A	RPAD	RPAD	RPAD[1]	N/A	RPAD	N/A
		Remove leading blank spaces	LTRIM(x)	N/A	LTRIM	LTRIM	LTRIM[1]	LTRIM	LTRIM	LTRIM
		Remove trailing blank spaces	RTRIM(x)	N/A	RTRIM	RTRIM	RTRIM[1]	RTRIM	RTRIM	RTRIM
SQL:2003/200n (core)	E021-09	Remove leading and trailing blank	LTRIM(RTRIM(x))	N/A	TRIM	TRIM	N/A	TRIM	TRIM	N/A
SQL:2003/200n		Repeat string multiple times	REPEAT(str, num)	N/A	REPEAT	REPEAT	STRREPEAT[1]	REPEAT	RPAD	REPLICATE
SQL:2003/200n		String of repeated spaces	SPACE(num)	N/A	SPACE	N/A	RPAD[1]	SPACE	RPAD	SPACE
SQL:2003/200n		Convert number to string	CHAR(num)				CAST		TO_CHAR	STR
SQL:2003/200n (core)	E021-06	Substring	SUBSTR(str, length[, start])	SUBSTR	SUBSTRING SUBSTR	SUBSTRING SUBSTR	SUBSTRING SUBSTR SUBSTRLEN	SUBSTR	SUBSTR	SUBSTRING
		Replace characters	REPLACE(string, from, to)	N/A	REPLACE	REPLACE	N/A	REPACE	REPLACE	STUFF
		Capitalize first letter of each word in string		N/A	N/A	INITCAP	N/A	INITCAP	INITCAP	N/A
		Translate character string	TRANSLATE(string, to, from)	N/A	N/A	TRANSLATE	N/A		TRANSLATE	N/A
SQL:2003/200n (core)	E021-04	Length of string x (in characters)	LENGTH(x)	LENGTH(x)	CHAR_LENGTH(x)	CHAR_LENGTH(x) CHARACTER_LENGTH(x)	STRLEN(x)[1]	LENGTH(x)	LENGTH(x)	LEN(x)
SQL:2003/200n (core)	E021-05	Length of string x (in bytes)	LENGTH(x)	LENGTH(x)	LENGTH(x)	OCTET_LENGTH(x)	N/A	LENGTH(x)		DATALENGTH(x)
SQL:2003/200n (core)	E091-03	Greatest character string in list		MAX			MAX	MAX	GREATEST	N/A
SQL:2003/200n (core)	E091-04	Least character string in list		MIN			MIN	MIN	LEAST	N/A
SQL-92		If x is NULL then return def else return x	COALESCE(x, def)		COALESCE(x, def)	COALESCE(x, def)	COALESCE(x, def)		COALESCE(x, def)	COALESCE(x, def)
		Quote SQL in string x		QUOTE(x)	QUOTE(x)		N/A	QUOTE_LITERAL(x)		QUOTENAME(x,'''')
		Soundex index of string x	SOUNDEX(x)	SOUNDEX(x)	SOUNDEX(x)	N/A	N/A	N/A	SOUNDEX(x)	SOUNDEX(x)
		Calculate MD5 hash from string		N/A	MD5	MD5	N/A	N/A	N/A	HASHBYTES('MD5',x)
		Calculate SHA1 hash from string		N/A	SHA1	N/A	N/A	{{n/a}	N/A	HASHBYTES('SHA1',x)

Notes:

1. ↑ Firebird users need to register most of these functions to make them available. The registration is done once for each database. Additional rFunc library is needed for some functions.

[edit] SQL/XML functions

SQLX better known as SQL/XML is an ISO standard (ISO/IEC 9075-1) whose extensions that are part of the SQL 2003 standard.

Function	Description	DB2	SQLite	MySQL	PostgreSQL	Firebird	OpenLink Virtuoso	Oracle	MSSQL
Version	Software Version(s) Supported	V9	N/A	Planned	N/A	N/A	3.5-4.x	9-10x	N/A
XMLElement()	Create an XML Element	XMLElement()	N/A	Planned	N/A	N/A	XMLElement()	XMLElement()	N/A
XMLForest()	Create an XML Fragment from passed-in components.	XMLForest()	N/A	Planned	N/A	N/A	XMLForest()	XMLForest()	N/A
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	Planned	N/A	N/A	XMLColAttVal()	XMLColAttVal()	N/A
ExtractValue()	Takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node.		N/A	Planned	N/A	N/A	ExtractValue()	ExtractValue()	N/A
XMLTransform()	Takes as arguments an XMLType instance and an XSL style sheet, which is itself a form of XMLType instance. It applies the style sheet to the instance and returns an XMLType.		N/A	Planned	N/A	N/A	XMLTransform()	XMLTransform()	N/A
XMLSequence()	Takes input and returns either a varray of the top-level nodes in the XMLType, or an XMLSequence type an XML document for each row of the cursor.		N/A	Planned	N/A	N/A	XMLSequence()	XMLSequence()	N/A
XMLConcat()	Takes as input a series of XMLType instances, concatenates the series of elements for each row, and returns the concatenated series.	XMLConcat	N/A	Planned	N/A	N/A	XMLConcat()	XMLConcat()	N/A
UpdateXML()	Takes as arguments an XMLType instance and an XPath-value pair, and returns an XMLType instance with the updated value.		N/A	Planned	N/A	N/A	UpdateXML()	UpdateXML()	N/A

[edit] Select queries

[edit] Select without tables

• DB2

VALUES

or

SELECT expressions FROM sysibm.sysdummy1

• Firebird, InterBase

SELECT expressions FROM rdb$database

• MySQL, MSSQL, PostgreSQL, SQLite

SELECT expressions

• Oracle

SELECT expressions FROM dual

[edit] Limiting results of select query

Note that end_row = start_row + num_rows - 1

DB2		SELECT colums FROM table FETCH FIRST num_rows ONLY
Firebird		SELECT FIRST num_rows SKIP start_row columns FROM table
MSSQL	Simple version (when start_row = 1)	SELECT TOP num_rows columns FROM table
	Complex version (full-featured, requires ordering)	SELECT * FROM ( SELECT TOP num_rows columns FROM ( SELECT TOP num_rows + start_row columns FROM table ORDER BY some_key ASC ) AS newtable ORDER BY some_key DESC ) AS newtable2 ORDER BY some_key ASC
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	SELECT columns FROM table WHERE rownum >= start_row AND rownum <= end_row
	Complex query (when ORDER BY is used)	SELECT * FROM ( SELECT temporaryalias.*, rownum num FROM ( SELECT columns FROM table ORDER BY columns ) temporaryalias WHERE rownum <= end_row ) WHERE num >= start_row
PostgreSQL		SELECT columns FROM table LIMIT num_rows OFFSET start_row
SQLite		SELECT columns FROM table LIMIT num_rows OFFSET start_row
OpenLink Virtuoso		SELECT columns FROM table LIMIT num_rows

[edit] Write queries

[edit] Replace query

Replace query inserts new row if no row with such primary key exists or updates existing row if it does.
Note: this is not part of SQL standards, it is only a MySQL extension.

• DB2: MERGE statement

MERCE 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 )

• MySQL: Allows 3 syntaxes: non-standard REPLACE query, (since 4.1) INSERT ... ON DUPLICATE KEY UPDATE, and a varient 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

• rest of the world writes

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' )

[edit] Insert results from select query into existing table

• DB2, Firebird, MSSQL, MySQL, Oracle, PostgreSQL, SQLite, OpenLink Virtuoso:

INSERT INTO existing_table SELECT columns FROM table

[edit] Save results from select query as table

• PostgreSQL, MSSQL, Sybase T-SQL:

SELECT columns INTO new_table FROM table

• MySQL, PostgreSQL, OpenLink Virtuoso, Oracle, SQLite:

CREATE TABLE new_table [AS] SELECT columns FROM table

[edit] Transactions

	Start	Commit	Rollback	Prepare	Execute prepared
DB2	Implicit	COMMIT [ WORK ]	ROLLBACK [ WORK ]	?	?
SQLite	BEGIN [TRANSACTION]	COMMIT [TRANSACTION]	ROLLBACK [TRANSACTION]	?	?
MySQL	BEGIN [WORK], START [TRANSACTION]			?	?
PostgreSQL				PREPARE TRANSACTION transaction_id	EXECUTE TRANSACTION transaction_id
Firebird	Implicit, SET TRANSACTION			?	?
OpenLink Virtuoso	Implicit	COMMIT WORK [TRANSACTION]	ROLLBACK WORK [TRANSACTION]	?	?
Oracle	Implicit	a COMMIT statement is executed any DDL statement is executed a user disconnects normally	a ROLLBACK statement is executed a user process is terminated abnormally	?	?
MSSQL	BEGIN TRAN[SACTION]	COMMIT [TRAN[SACTION]]	ROLLBACK [TRAN[SACTION]]	?	?

[edit] SQL Procedure language

[edit] Stored procedures

Database	Create syntax	Calling
DB2	CREATE PROCEDURE procedure_name(...) BEGIN /* SQL code */ END	CALL procedure_name(...)
SQLite	N/A	N/A
MySQL	DELIMITER $ CREATE PROCEDURE nameprocedure (input_parameter_name datatype, ... ) BEGIN /* SQL code */ END$ DELIMITER ;	CALL nameprocedure(...)
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(...)
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(...)
OpenLink Virtuoso
Oracle
MSSQL	CREATE PROCEDURE nameprocedure (input_parameter_name datatype, ... ) AS /* SQL code */ GO	EXEC nameprocedure(...)

[edit] 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(...) = ...
SQLite	N/A	N/A
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
MSSQL