Comparison of SQL syntax

Encyclopedia : C : CO : COM : Comparison of SQL syntax

---

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

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

Data structure definition

Escaping 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. In some dialect, it is also called "delimited identifiers".

• SQLite: identifier without escaping
• MySQL: `identifier` (always) or "identifier" (if running in ANSI mode) or without escaping
• PostgreSQL: "identifier" or without escaping
• Firebird: identifier or without escaping
• OpenLink Virtuoso: "identifier"
• Oracle: "identifier" (note that Oracle's delimited identifiers are case-sensitive, and all regular identifiers are usually in upper case)
• MSSQL: [identifier] (recommended) or "identifier" (if the quoted_identifier option is set on — otherwise, "identifier" would be parsed as a string in single quotes).

Auto-increment column

• SQLite: CREATE TABLE table (column INTEGER NOT NULL PRIMARY KEY [AUTOINCREMENT])
• MySQL: CREATE TABLE table (column INT NOT NULL PRIMARY KEY AUTO_INCREMENT)
• PostgreSQL: CREATE TABLE table (column SERIAL PRIMARY KEY)
• 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.attach_id = GEN_ID(table_gen, 1);
END;; 

• 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));

• 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;

• MSSQL: CREATE TABLE table (column INT IDENTITY(1,1))

Functions and expressions

Math functions

Trigonometric functions

Function	SQLite	MySQL	PostgreSQL	Firebird	OpenLink Virtuoso	Oracle	MSSQL
Arc sine	style="background: #ececec" | N/A	ASIN(x)	ASIN(x)	style="background: #ececec" | N/A	ASIN(x)	ASIN(x)	ASIN(x)
Arc cosine	style="background: #ececec" | N/A	ACOS(x)	ACOS(x)	style="background: #ececec" | N/A	ACOS(x)	ACOS(x)	ACOS(x)
Arc tangent of x	style="background: #ececec" | N/A	ATAN(x)	ATAN(x)	style="background: #ececec" | N/A	ATAN(x)	ATAN(x)	ATAN(x)
Arc tangent of x and y	style="background: #ececec" | N/A	ATAN2(x, y) ATAN(x, y)	ATAN2(x,y)	style="background: #ececec" | N/A	ATAN2(x, y)	ATAN2(x, y)	ATN2(x, y)
Sine	style="background: #ececec" | N/A	SIN(x)	SIN(x)	style="background: #ececec" | N/A	SIN(x)	SIN(x)	SIN(x)
Cosine	style="background: #ececec" | N/A	COS(x)	COS(x)	style="background: #ececec" | N/A	COS(x)	COS(x)	COS(x)
Tangent	style="background: #ececec" | N/A	TAN(x)	TAN(x)	style="background: #ececec" | N/A	TAN(x)	TAN(x)	TAN(x)
Cotangent	style="background: #ececec" | N/A	COT(x)	COT(x)	style="background: #ececec" | N/A	COT(x)	style="background: #ececec" | N/A	COT(x)
Hyperbolic sine	style="background: #ececec" | N/A	style="background: #ececec" | N/A	style="background: #ececec" | N/A	style="background: #ececec" | N/A	style="background: #ececec" | N/A	SINH(x)	style="background: #ececec" | N/A
Hyperbolic cosine	style="background: #ececec" | N/A	style="background: #ececec" | N/A	style="background: #ececec" | N/A	style="background: #ececec" | N/A	style="background: #ececec" | N/A	COSH(x)	style="background: #ececec" | N/A
Hyperbolic tangent	style="background: #ececec" | N/A	style="background: #ececec" | N/A	style="background: #ececec" | N/A	style="background: #ececec" | N/A	style="background: #ececec" | N/A	TANH(x)	style="background: #ececec" | N/A

Numeric functions

Function	SQLite	MySQL	PostgreSQL	Firebird	OpenLink Virtuoso	Oracle	MSSQL
Absolute value of x	ABS(x)	ABS(x)	ABS(x)	ABS(x)	ABS(x)	ABS(x)	ABS(x)
Sign of number	style="background: #ececec" | N/A	SIGN(x)	SIGN(x)	style="background: #ececec" | N/A	SIGN(x)	SIGN(x)	SIGN(x)
Modulus (remainder) of [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
Smallest integer >= x	style="background: #ececec" | N/A	CEILING(x) CEIL(x)	CEILING(x) CEIL(x)	CEILING(x)	CEILING(x)	CEIL(x)	CEILING(x)
Largest integer <= x	style="background: #ececec" | 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])	style="background: #ececec" | N/A	ROUND(x)	ROUND(x)	ROUND(x[, d])
Truncate x to n decimal places	style="background: #ececec" | N/A	TRUNCATE(x [,d])	TRUNC(x[, y])	style="background: #ececec" | N/A	style="background: #ececec" | N/A	TRUNC	style="background: #ececec" | N/A
Square root	style="background: #ececec" | N/A	SQRT(x)	SQRT(x)	SQRT(x)	SQRT(x)	SQRT(x)	SQRT(x)
Exponent of x ([e^x])	style="background: #ececec" | N/A	EXP(x)	EXP(x)		EXP(x)	EXP(x)	EXP(x)
Power ([x^y])	style="background: #ececec" | N/A	POWER(x, y) POW(x, y)	POWER(x, y)	style="background: #ececec" | N/A	POWER(x,y)	POWER(x, y)	POWER(x, y)
Natural logarithm of x	style="background: #ececec" | N/A	LN(x)	LN(x)	LN(x)	LOG(x)	LN(x)	LOG(x)
Logarithm, any base	style="background: #ececec" | N/A	LOG(b,x)	LOG(b,x)	LOG(b,x)	LOG(x)	LOG(x)	style="background: #ececec" | N/A
Logarithm, base 10	style="background: #ececec" | N/A	LOG10(x)	LOG(x)	LOG10(x)	LOG(x)	LOG(x)	LOG10(x)
Randomize, set seed to x	style="background: #ececec" | N/A	RAND([x])	?	?	RANDOMIZE([x])	style="background: #ececec" | N/A	?
Generate random number	RANDOM()	RAND()	RANDOM()	RAND()	RND()	style="background: #ececec" | N/A	RAND()
Highest number in list	MAX(list)	GREATEST(list)	MAX() GREATEST(list)	MAX()	MAX(list)	GREATEST(list)	style="background: #ececec" | N/A
Lowest number in list	MIN(list)	LEAST(list)	MIN() LEAST(list)	MIN(list)	MIN(list)	LEAST(list)	style="background: #ececec" | N/A
Convert number if NULL					ISNULL	NVL	ISNULL

Aggregate functions

Function	SQLite	MySQL	PostgreSQL	Firebird	OpenLink Virtuoso	Oracle	MSSQL
Count non-NULL values in x	?	COUNT([DISTINCT] x)	?	?	?	?	?
Sum of x	?	SUM(x)	?	?	?	?	?
Average of x	?	AVG([DISTINCT] x)	?	?	?	?	?
Minimum value in x	?	MIN(x)	?	?	?	?	?
Maximum value in x	?	MAX(x)	?	?	?	?	?
Range (max - min) of x	?	style="background: #ececec" | N/A	?	?	?	?	?
Standard deviation	style="background: #ececec" | N/A	STD(x) STDDEV(x) STDDEV_POP(x)	STDDEV(x)	style="background: #ececec" | N/A	STDDEV(x, y)	STDDEV([DISTINCT>ALL] x)	STDEV(x)
Variance	style="background: #ececec" | N/A	VARIANCE(x) VAR_POP(x)	VARIANCE(x)	style="background: #ececec" | N/A	VAR(x)	VARIANCE(x)	VAR(x)

Date and time functions

Function	SQLite	MySQL	PostgreSQL	Firebird	OpenLink Virtuoso	Oracle	MSSQL
Date addition	style="background: #ececec" | N/A	DATE_ADD	arg1 + arg2	style="background: #ececec" | N/A	DATEADD	arg1 + arg2	DATEADD
Date subtraction	style="background: #ececec" | N/A	DATE_SUB	arg1 - arg2	style="background: #ececec" | N/A	DATEDIFF	arg1 - arg2	DATEDIFF
Date difference	style="background: #ececec" | N/A	DATEDIFF	AGE	style="background: #ececec" | N/A	DATEDIFF	arg1 - arg2	DATEDIFF
Last day of month	style="background: #ececec" | N/A	LAST_DAY	style="background: #ececec" | N/A	style="background: #ececec" | N/A	style="background: #ececec" | N/A	LAST_DAY	style="background: #ececec" | N/A
Time zone conversion	style="background: #ececec" | N/A	CONVERT_TZ	TIMEZONE	style="background: #ececec" | N/A	TIMEZONE	NEW_TIME	style="background: #ececec" | N/A
First weekday after date	style="background: #ececec" | N/A		style="background: #ececec" | N/A	style="background: #ececec" | N/A	style="background: #ececec" | N/A	NEXT_DAY	style="background: #ececec" | N/A
Convert date to string	STRFTIME(format, value)	DATE_FORMAT(value, format)	TO_CHAR(value, format)	CAST(value, datetype)	CAST	TO_CHAR	DATENAME
Convert date to number	style="background: #ececec" | N/A		DATE_PART	style="background: #ececec" | N/A	CAST	TO_NUMBER(TO_CHAR())	DATEPART
Convert string to date	style="background: #ececec" | N/A		TO_DATE	style="background: #ececec" | N/A	STRINGDATE	TO_DATE	CAST
Get current date	CURRENT_DATE	CURDATE, CURRENT_DATE	CURRENT_DATE	CURRENT_DATE	CURDATE	SYSDATE	style="background: #ececec" | N/A
Get current time	CURRENT_TIME	CURTIME, CURRENT_TIME	CURRENT_TIME	CURRENT_TIME	CURTIME	style="background: #ececec" | N/A	style="background: #ececec" | N/A
Get current date and time	CURRENT_TIMESTAMP	NOW(), CURRENT_TIMESTAMP	NOW(), CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	NOW	SYSTIMESTAMP	GETDATE()

String functions

Function	SQLite	MySQL	PostgreSQL	Firebird	OpenLink Virtuoso	Oracle	MSSQL
Convert character x to ASCII	style="background: #ececec" | N/A	ASCII(x)	ASCII(x)	style="background: #ececec" | N/A	ASCII(x)	ASCII(x)	ASCII(x)
Convert ASCII x to character	style="background: #ececec" | N/A	CHAR(x)	CHR(x)	style="background: #ececec" | N/A	CHR(x)	CHR(x)	CHAR(x)
String concatenate	arg1	arg2	CONCAT (multiple arguments)	arg1	arg2	style="background: #ececec" | N/A	CONCAT(list)	arg1	arg2 CONCAT (only 2 arguments)	arg1 + arg2
Find first occurrence of substring in string	style="background: #ececec" | N/A	INSTR LOCATE POSITION	POSITION STRPOS	style="background: #ececec" | N/A	SUBSTR	INSTR	CHARINDEX
Find first occurrence of pattern in string	style="background: #ececec" | N/A			style="background: #ececec" | N/A	INSTR	INSTR	PATINDEX
Convert x to lowercase	LOWER(x)	LOWER(x) LCASE(x)	LOWER(x)	style="background: #ececec" | N/A	LCASE(x)	LOWER(x)	LOWER(x)
Convert x to uppercase	UPPER(x)	UPPER(x) UCASE(x)	UPPER(x)	style="background: #ececec" | N/A	UCASE(x)/UPPER(x)	UPPER(x)	UPPER(x)
Pad left side	style="background: #ececec" | N/A	LPAD	LPAD	style="background: #ececec" | N/A	style="background: #ececec" | N/A	LPAD	style="background: #ececec" | N/A
Pad right side	style="background: #ececec" | N/A	RPAD	RPAD	style="background: #ececec" | N/A	style="background: #ececec" | N/A	RPAD	style="background: #ececec" | N/A
Remove leading blank spaces	style="background: #ececec" | N/A	LTRIM	LTRIM	style="background: #ececec" | N/A	LTRIM	LTRIM	LTRIM
Remove trailing blank spaces	style="background: #ececec" | N/A	RTRIM	RTRIM	style="background: #ececec" | N/A	RTRIM	RTRIM	RTRIM
Remove leading and trailing blank	style="background: #ececec" | N/A	TRIM	TRIM	style="background: #ececec" | N/A	TRIM	TRIM	style="background: #ececec" | N/A
Repeat string multiple times	style="background: #ececec" | N/A	REPEAT	REPEAT	style="background: #ececec" | N/A	REPEAT	RPAD	REPLICATE
String of repeated spaces	style="background: #ececec" | N/A	SPACE	style="background: #ececec" | N/A	style="background: #ececec" | N/A	SPACE	RPAD	SPACE
Convert number to string				style="background: #ececec" | N/A		TO_CHAR	STR
Substring	SUBSTR	SUBSTRING SUBSTR	SUBSTRING SUBSTR	style="background: #ececec" | N/A	SUBSTR	SUBSTR	SUBSTRING
Replace characters	style="background: #ececec" | N/A	REPLACE	REPLACE	style="background: #ececec" | N/A	REPACE	REPLACE	STUFF
Capitalize first letter of each word in string	style="background: #ececec" | N/A	style="background: #ececec" | N/A	INITCAP	style="background: #ececec" | N/A	INITCAP	INITCAP	style="background: #ececec" | N/A
Translate character string	style="background: #ececec" | N/A	style="background: #ececec" | N/A	TRANSLATE	style="background: #ececec" | N/A		TRANSLATE	style="background: #ececec" | N/A
Length of string x (in characters)	LENGTH(x)	CHAR_LENGTH(x)	CHAR_LENGTH(x) CHARACTER_LENGTH(x)	style="background: #ececec" | N/A	LENGTH(x)	LENGTH(x)	LEN(x)
Length of string x (in bytes)	LENGTH(x)	LENGTH(x)	OCTET_LENGTH(x)	style="background: #ececec" | N/A	LENGTH(x)		DATALENGTH(x)
Greatest character string in list	MAX			style="background: #ececec" | N/A	MAX	GREATEST	style="background: #ececec" | N/A
Least character string in list	MIN			style="background: #ececec" | N/A	MIN	LEAST	style="background: #ececec" | N/A
If x is NULL then return def else return x		IFNULL(x, def)		style="background: #ececec" | N/A		NVL(x, def)	ISNULL(x, def)
Quote SQL in string x	QUOTE(x)	QUOTE(x)		style="background: #ececec" | N/A	QUOTE_LITERAL(x)		QUOTENAME(x,)
Soundex index of string x	SOUNDEX(x)	SOUNDEX(x)	style="background: #ececec" | N/A	style="background: #ececec" | N/A	style="background: #ececec" | N/A	SOUNDEX(x)	SOUNDEX(x)
Calculate MD5 hash from string	style="background: #ececec" | N/A	MD5	MD5	style="background: #ececec" | N/A	style="background: #ececec" | N/A	style="background: #ececec" | N/A	HASHBYTES('MD5',x)
Calculate SHA1 hash from string	style="background: #ececec" | N/A	SHA1	style="background: #ececec" | N/A	style="background: #ececec" | N/A		style="background: #ececec" | N/A	HASHBYTES('SHA1',x)

Select queries

Select without tables

• SQLite, MySQL, PostgreSQL, MSSQL

SELECT expressions

• Oracle

SELECT expressions FROM dual

Limiting results of select query

Note that end_row = start_row + num_rows - 1

SQLite		SELECT columns FROM table LIMIT num_rows OFFSET start_row
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
PostgreSQL		SELECT columns FROM table LIMIT num_rows OFFSET start_row
Firebird		SELECT FIRST num_rows SKIP start_row columns FROM table
OpenLink Virtuoso		SELECT columns FROM table LIMIT 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
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

Write queries

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.

• MySQL: Allows 2 syntaxes: non-standard REPLACE query and (since 4.1) INSERT ... ON DUPLICATE KEY UPDATE.

REPLACE [INTO] table [(columns)] VALUES (values)

INSERT INTO table (columns) VALUES (values) ON DUPLICATE KEY UPDATE column1=value1, column2=value2

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

Insert results from select query into existing table

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

INSERT INTO existing_table SELECT columns FROM table

Save results from select query as table

• PostgreSQL, MSSQL:

SELECT columns INTO new_table FROM table

• MySQL, PostgreSQL, OpenLink Virtuoso, Oracle:

CREATE TABLE new_table [AS] SELECT columns FROM table

Transactions

• SQLite, MySQL, PostgreSQL

BEGIN [TRANSACTION]
COMMIT [TRANSACTION]
ROLLBACK [TRANSACTION]

• MySQL, PostgreSQL

START [TRANSACTION]
COMMIT [TRANSACTION]
ROLLBACK [TRANSACTION]

• Firebird

Beginning of a transaction is implicit.
COMMIT [TRANSACTION]
ROLLBACK [TRANSACTION]

• MSSQL

BEGIN TRAN[SACTION]
COMMIT [TRAN[SACTION]]
ROLLBACK [TRAN[SACTION]]

• Oracle

Beginning of a transaction is implicit, whenever an executable SQL statement is encountered.

A transaction can be committed when any of the following occurs:

a COMMIT statement is executed

any DDL statement is executed

a user disconnects normally

A transaction will be rolled back when any of the following occurs:

a ROLLBACK statement is executed

a user process is terminated abnormally

References

Official product documentations

• [MySQL 5.0 reference manual]
• [PostgreSQL 8.1 reference manual]
• [SQLite documentation]
• [Firebird / Interbase 6 language reference] (ZIP archive, 8.9MB)
• [OpenLink Virtuoso Universal Server Documentation]
• [Oracle Database SQL Reference]
• [Microsoft SQL Server Documentation]

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]
• [SQL Functions] — MySQL vs Oracle comparison by Eric Brian
• [SQLWays] database migration software documentation, provides many comparisons of SQL dialects differences.

 

---

From Wikipedia, the Free Encyclopedia. Original article here. Support Wikipedia by contributing or donating.
All text is available under the terms of the GNU Free Documentation License See Wikipedia Copyrights for details.

Search Titles

0

1

2

3

4

5

6

7

8

9

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

?