Last updated: 2021-02-13
Items with (*) have a comment (or explanation) that is shown as a tooltip when hovering the mouse over them

Please send feedback or error reports to:

This comparison focuses on SQL features that can be used in SQL statements or self-contained SQL scripts that don't require additional software (e.g. a compiler) to be usable. Features for database administration or deployment are also not the focus of this comparison.

To get a more in-depth comparison about some of the SQL features compared here, please visit Modern SQL

FeatureOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Window functionsYesYes(*)DISTINCT is not supported inside a window functionYes(*)DISTINCT is not supported inside a window functionYesYes(*)Since 8.0Yes(*)Since 10.2Yes(*)Since 3.0NoNoNoYes(*)Since 3.25
Common Table ExpressionsYesYesYesYesYes(*)Since 8.0Yes(*)Since 10.2YesNoYesNoYes(*)Since 3.8.3
CTE in a sub-query(*)Use a common table expression in a sub-query, not only as a top level queryYesYesNoNoYes(*)Since 8.0NoYesNoYesNoYes
Recursive QueriesYesYesYesYesYes(*)Since 8.0YesYes(Yes)(*)All columns are treated as varchar.
A CTE name can not be re-used in the same transaction
Can not be used in views
Can not be used with sub-queries
Can not be used with INSERT statements.
YesNoYes(*)Since 3.8.3
Row constructor(*)Use of the VALUES row-constructor wherever a table reference can be used.
Sometimes also called "table value constructor".
NoYesYes(*)Can only be used in a FROM, not e.g. in a common table expression directly. YesNoNoNoNoYesYesYes
Filtered aggregates(*)Only include rows in an aggregate based on a condition:
avg(salary) filter (where dept_id = 1)
NoYes(*)Since 9.4NoNoNoNoNoNoYesNoYes(*)Since 3.25
PIVOT SupportYesNo(*)The crosstab function can be used for this.YesNoNoNoNoNoNoNoNo
GROUP BY .. ROLLUPYesYes(*)Since 9.5YesYesYesYesNoNoYes(*)Since 2.5.1YesNo
GROUP BY .. GROUPING SETS(*)Create multiple independent groups with a single GROUP BY queryYesYes(*)Since 9.5YesYesNoNoNoNoYes(*)Since 2.5.1NoNo
Temporal queries(*)Temporal queries allow querying the database (or a single table) to return the data as it was in the pastYesNoYes(*)Since SQL Server 2016YesNoYes(*)Since 10.3NoNoYes(*)Since 2.5.0NoNo
SELECT without a FROM clauseNoYesYesNo(Yes)(*)No WHERE clause is allowed e.g.
select 42 where not exists (...);
(Yes)(*)No WHERE clause is allowed e.g.
select 42 where not exists (...);
NoYesYes(*)When Postgres compatibility is enabledNoYes
Parallel queries(*)The ability to distribute a single query over serveral CPUsYesYes(*)Full parallel query support since Postgres 11YesYesNoNoNoNoNoNoNo
Aggregates for stringsYes(*)Limited to 32kYesYes(*)DISTINCT is not supported.
Can not be used as a window function.
Tuple comparison(Yes)(*)Not supported for >, < <> or between operatorsYesNoYesYes(*)Not supported with the BETWEEN operatorYes(*)Not supported with the BETWEEN operatorNo(Yes)(*)Not supported for the IN operatorYesNo(Yes)(*)Not possible with constant values for an IN clause
e.g.(a,b) IN ( (1,2), (3,4) )
Tuple updatesYesYes(*)Since 9.5NoYesNoNoNoYesYesNoYes(*)Since 3.15
UPDATE with a joinNoYesYesNoYesYesNoNoNoNoNo
ANSI date literals(*)Specify date or timestamps using ANSI literals, e.g.
DATE '2014-01-31' or timestamp '2014-04-25 19:18:17'
Query variables(*)Variables that can be used inside a single query without the need to use procedural codeNoNoYesNoYesYesNoYesNoNoNo
UNNEST(*)Convert an array into a set of rowsNoYesNoYesNoNoNoNoYesNoNo
Split string to rows(*)Split a string delimited by a specific character into multiple rows (usable like a table)NoYesYes(*)Since 2016NoNoNoNoNoNoNoNo
Regular ExpressionsOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Comparison based on RegEx(*)Conditions with regular expressions that can be used e.g. in a WHERE clauseYesYesNoYes(*)Since 11.1YesYesYesYesYesNoNo
Substring(*)Extract the part of a string value based on a RegExYesYesNoYes(*)Since 11.1Yes(*)Since 8.0Yes(*)Since 10.0.5Yes(*)Since 3.0NoYesNoNo
Replace(*)Replace values in a string based on a RegExYesYesNoYes(*)Since 11.1Yes(*)Since 8.0Yes(*)Since 10.0.05NoYesYes(*)Since 2.3.4NoNo
ConstraintsOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Deferred foreign key constraints(*)Define constraints that are checked only at commit timeYesYesNoNoNoNoNoNoNoYes(*)Since 10.11Yes
Check constraintsYesYesYesYesYes(*)Since 8.0.16Yes(*)Since 10.2YesYesYesYesYes
Check constraints with sub-queryNoNoNoNoNoNoYesYesNoNoNo
Check constraints using custom functions(*)Create a check constraint based on a user-defined functionNoYesYesYesNoNoYesNoNoNoNo(*)Possible through C function, not through SQL functions
Exclusion constraints(*)Constraints that prevent e.g. overlapping date ranges (WITHOUT OVERLAP in ANSI SQL)NoYesNoYes(*)Since 10.0NoNoNoNoNoNoNo
Statement based constraint evaluationYesYesYesYesNoNoNoYesYesYesYes
ON DELETE CASCADE(*)For foreign keysYesYes(Yes)(*)Not for self-referencing FK constraints (to the same table)YesYesYesYesYesYesYesYes
ON UPDATE CASCADE(*)For foreign keysNoYes(Yes)(*)Not for self-referencing FK constraints (to the same table)NoYesYesYesYesYesNoYes
Foreign keys using MATCH FULL(*)Define multi-column foreign keys that handle NULL valuesNoYesNoNoNo(*)The definition is accepted, but ignoredNo(*)The definition is accepted, but ignoredNoNoYesNoNo(*)The definition is accepted, but ignored
IndexingOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Partial index(*)Define an index on a subset of a tableYes(*)Through a function based indexYes(Yes)(*)WHERE conditions involving functions are not supported
e.g. where upper(name) <> 'ARTHUR'
Descending Index(*)Define an index that is sorted descendingYesYesYesYesYes(*)Since 8.0No(Yes)(*)It's not possible to mix ASC and DESC for multi-column indexesYesYesNoYes
Index on expression(*)Create an index based on an expression/functionYesYes(No)(*)Can be simulated using an index on a computed column(Yes)(*)Not for DB2 LUW(No)(*)Can be simulating by indexing a computed column(No)(*)Can be simulating by indexing a computed column(Yes)(*)Limited to a single expression. Can not be combined with additional columnsNoNoNoYes(*)Since 3.9
Index using a custom function(*)Create an expression index using a custom function (written in a "SQL" procedural language)YesYesNoYesNoNoNoNoNoNoNo(*)Functions written in C can be indexed
Index include columns(*)Define an index on some columns and include other (non-indexed) columns NoYes(*)Since 11YesYesNoNoNoNoNoNoNo
Multi-column statistics(*)Create extended statistics storing dependencies between values in the columns of a single tableYesYesYesYesNoNoNoNoNoNoNo
Clustered index(*)An index that contains the table data (index and table storage are the same)Yes(*)Called Index Organized TableNoYesYesYesYesNoNoNoNoYes
Duplicate NULL values in unique index(*)The SQL standard requires that a unique index allows multiple NULL values.No(*)Works for single-column indexes only, not for multi-column indexesYesNoNoYes(*)The behaviour depends on the storage engine being used.Yes(*)The behaviour depends on the storage engine being used.NoYesYesNoYes
DMLOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Writeable CTEs(*)Use DML statements inside a CTENoYes(*)The result of a CTE can not be updatedYes(*)The result of a CTE can be updated, but a CTE cannot use a DML statementNoNoNoNoNoNoNoNo
Multi-row INSERTs(*)Insert more than one row with a single INSERT statementNoYesYesYesYesYesNoYesYesYesYes
TRUNCATE table with FK(*)Truncate tables that are referenced by other tablesYes(*)Oracle 12.1 introduced the cascade option for truncate which requires the FK to be defined as on delete cascadeYesNoNoNoNoNoNoNoNoNo
Read consistency during DML operations(*)During a DML operation reading a column value should return the value that was valid before the statement startedYesYesYesYesNoYes(*)Since 10.3
Requires non-default sql-mode
Use target table in sub-queries(*)Use the targe table of an UPDATE, DELETE or INSERT statement in a sub-selectYesYesYesYesNoNoYesYesYesYesNo(*)The query is accepted but updates the data incorrectly
MERGE support(*)Update rows if they exist, insert if not (sometimes also named "UPSERT")YesYes(*)Using insert ... on conflictYesYesYes(*)Using INSERT .. ON DUPLICATEYes(*)Using INSERT .. ON DUPLICATEYesYesYesYes(*)Since 10.11No
SELECT .. FOR UPDATE NOWAIT(*)Select one (or more) rows and lock them for a future update. Fail with an error if the lock cannot be obtainedYesYesNo(*)FOR UPDATE can only be used with cursors, not plain SELECT statementsNoYes(*)Since 8.0NoNoNoNoNoNo
RETURNING clause as a result setNoYesYesNoNoNoYesNoYes(*)Since 2.5.1NoNo
Parallel DML(*)Use multiple threads/workers for a single DML statementYesNoNoNoNoNoNoNoNoNoNo
Data Types(*)Data types that can be used for the column of a tableOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
User defined datatypes(*)Crate UDTs using SQL and use those UDTs as a column's data typeYesYesNo(*)SQL Server does have user defined datatypes but they can not be used for columns in a tableYesNoNoNoNoYesNoNo
Domains(*)A special kind of user defined data type that can also include check constraints, usually based on a base data typeNoYes(Yes)(*)This can be done using rules (together with user defined types), but they are deprecatedNoNoNoYesYesYesNoNo
Distinct types(*)User defined types which cannot be compared. E.g. prevent comparing a product_id to a customer_idNoNoNoYesNoNoNoNoNoNoNo
ArraysNoYesNoNoNoNo(Yes)(*)There is no support for arrays in SQL or JDBC.
They can only be used in stored procedures.
Enums(*)De-normalize lookup values by specifying a fixed set of allowed values (a special case of a check constraint)NoYesNoNoYesYesNoNoNoNoNo
IP addressNoYesNoNoNoNoNoNoNoNoNo
BOOLEAN(*)Standard boolean data type as defined by the SQL standard (usable as a column data type)No(*)Only PL/SQL supports booleanYesNo(*)The BIT is a number type that is limited to 0 and 1.Yes(*)Since 11.1No(*)MySQL's BOOLEAN is only a synonym for TINYINTNo(*)MariaDB's BOOLEAN is only a synonym for TINYINTYes(*)Since 3.0YesYesYesNo
TIME(*)A data type that only stores a timeNoYesYesYesYesYesYesYesYesNoNo
DATE(*)A data type that only stores a date (without a time)No(*)Oracle's DATE type stores date and timeYesYesYesYesYesYesYesYesYesNo
TIMESTAMP(*)A data type that stores a date and timeYesYesYes(*)The data type is named datetime or datetime2.
timestamp is something different.
YesYes(*)TIMESTAMP has a very limited range: from 1970 up to 2038.Yes(*)TIMESTAMP has a very limited range: from 1970 up to 2038.YesYesYesYesNo
TIME ZONE Support(*)Support for time zones (with TIMESTAMP values)YesYesYes(*)The data type is called datetimeoffsetNoNoNoNoYesYesNoNo
Range types(*)A data type that represents a range of values, e.g.:
all values from 1 through 100
The dates from 2014-01-01 to 2014-01-08
(No)(*)The PERIOD FOR introduced in 12c is something similar. YesNoNoNoNoNoNoNoNoNo
DDLOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Transactional DDL(*)The ability to rollback any DDL statementNoYesYesYesNoNoYesNoNoNoYes
Computed columns(*)Define a column in a table that is always calculated based on other columnsYesYes(*)Since 12YesYesYes(*)Since 5.7Yes(*)Since 5.2YesYesYesNoNo
Functions as column default(*)Use any function (including user defined functions) as the default for a column(Yes)(*)Only built-in functions can be used. No PL/SQL functionsYesYesNoNoYes(*)Since 10.2Yes(*)Since 3.0YesYes(*)When Postgres or Oracle compatibility is enabledNo(Yes)(*)Only functions written in C can be used
SequencesYesYesYesYesNoYes(*)Since 10.3YesYesYesYesNo
Auto increment columns(*)Columns that are populated automatically with unique values without the usage of triggersYes(*)Since 12cYesYesYesYesYesYesYesYesYesYes
SynonymsYesNoYesYesNoNoNoNoYes(*)Since 2.3.4YesNo
Non-blocking index creation(*)Create an index without blocking DML on the tableYesYesYesYesNoNoNoNoNoNoNo
PartitioningYes(Yes)(*)It's not possible to create globally unique indexes on partitioned tables without including the partition keyYesYes(Yes)(*)It's not possible to create globally unique indexes on partitioned tables without including the partition key(Yes)(*)It's not possible to create globally unique indexes on partitioned tables without including the partition keyNoNoNoNoNo
Cascading DROP(*)Drop a table including incoming foreign keysYesYesNoYesNo(*)MySQL accepts the CASCADE keyword but silently ignores it.No(*)MySQL accepts the CASCADE keyword but silently ignores it.NoYesYesNoNo
DDL Triggers(*)Define triggers that are fired when a DDL statement is executedYesYesYesNoNoNoYes(*)Since 3.0NoNoNoNo
TRUNCATE Trigger(*)Define triggers that are fired when a TRUNCATE statement is executed(No)(*)Possible through a system triggerYesNoNoNoNoNoNoNoNoNo
Custom name for PK constraint(*)Specify the name of the PK constraintYesYesYesYesNoNoYesYesYesYesYes
ALTER a table used in a view(*)The ability to ALTER the definition of a table used in a view without dropping the viewYesNo(*)You can add columns to a table but not drop columns or change their data typeYesYesYesYesYesYesYesYesYes
Add table column at specific position(*)Add a new column to a table at any position rather then only appending it to the end.
This is used to influence the column order for select * queries
Materialized views(*)Persist the result of a query as a tableYesYesYes(*)Called indexed viewsYes(*)Called materialized query tables (MQT)NoNoNoNoNoNoNo
MVIEW with query rewrite(*)If applicable can the optimizer detect that a query against the base tables can be done using the materialized viewYesNoYesNoNoNoNoNoNoNoNo
Automatically updated MVIEWS(*)MVIEW is automatically updated when the underlying tables are changedYesNoYesYesNoNoNoNoNoNoNo
Temporary TablesOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Permanent global temporary tables(*)Tmporary tables that are created once and that need to be dropped manualy (their definition is retained across server restarts)YesNoNoYesNoNoYesNoYesNoNo
Global temporary tables(*)Temporary tables that are always visible, but the data is session specificNoNoYesNoNoNoNoYesNoNoNo
Session local temporary tables(*)Temporary tables that are automatically dropped at the end of the transaction or when the session is disconnectedNoYesYesNoYesYesNoYesYesNoYes
Use a temporary table twice in a single queryYesYesYesYesNoYes(*)Since 10.2.1YesYesYesNo(*)Derby has no temporary tablesYes
ProgrammingOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Stored procedures(*)Writing and managing stored procedures using SQL commandsYesYes(*)Procedures only since Postgres 11YesYesYesYesYesNoYesNo(*)Procedures and functions can only be written in JavaNo(*)Can be written in C
Table functions(*)Functions that return result sets and can be used like a tableYesYesYesYesNoNoYesNoYesNoNo(*)Can be written in C
Custom aggregates(*)Create aggregate functions using SQLYesYesNo(*)Can be done with CLR functionsNo(*)Can be done with host languages (e.g. Java)NoNoNoNoYesNoNo
Function overloading(*)Create different versions of the same function that are distinguished by their argument listYes(*)Only inside packagesYesNoYesNoNoNoNoYesNoNo
User defined operators(*)Create new (comparison) operators for user defined data typesNo(*)CREATE OPERATOR only creates functions.
Creating operators like =, <, > is not possible
Statement level triggers(*)Triggers that fire once for each statementYesYesYesYesNoNoNoNo(*)Triggers can only be written in JavaYesYesNo
Row level triggers(*)Triggers that fire once for each rowYesYesNoYesYesYesYesNo(*)Triggers can only be written in JavaYesYesYes
RETURNING clause in a programming language(*)Use a RETURNING clause from within a programming languageYesYesYesNoNoNoYesNoNoNoNo
Before triggers(*)Triggers that are fired before the changes of a DML statement are persistedYesYes(No)(*)An INSTEAD OF trigger can be used for a similar purposeYesYesYesYesNo(*)Triggers can only be written in JavaYesYesYes
Dynamic SQL in functions(*)The ability to use dynamic SQL in stored functionsYesYesNo(*)Possible in CLR functionsYesNoNoYesNoNoNoNo
Dynamic SQL in triggers(*)The ability to use dynamic SQL in triggersYesYesYesNoNoNoYesNoNoNoNo
Delete triggers fired by cascading deletes(*)When rows are deleted due to a ON DELETE CASCADE foreign key, are delete triggers fired?YesYesYesYesNoNoYesNoYesYesYes(*)Only with PRAGMA recursive_triggers=ON
Built-in schedulerYesNoYesYesYesYesNoNoNoNoNo
ViewsOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Updateable ViewsYesYesYesYesYesYesYesNoYesNoNo
WITH CHECK OPTION(*)Create updateable views where only rows can be updated/deleted/inserted
that match the WHERE clause of the view
Triggers on viewsYesYesYesYesNoNoYesNoYesNoYes
Views with derived tables(*)Create a view that uses a derived tableYesYesYesYesNoNoYesYesYesYesYes
JOINs and OperatorsOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
CROSS JOINYesYesYesYesYesYesYesYesYesYesYes
LATERAL JOINYes(*)Since 12cYes(Yes)(*)Called APPLY
Inner joins are not supported.
Lateral joins against a derived table are not supported
JOIN ... USING (...)(*)A shortcut notation for the JOIN operator when both columns have the same name.YesYesNoNoYesYesYesNoYesYesYes
JOINs using tuple comparison(*)Use tuples in JOIN conditionsYesYesNoYesYesYesNoYesYesNoNo
INTERSECT(Yes)(*)Does not support INTERSECT ALLYes(Yes)(*)Does not support INTERSECT ALLYesNoYes(*)Since 10.3No(Yes)(*)Does not support INTERSECT ALLYesYes(Yes)(*)Does not support INTERSECT ALL
EXCEPT(Yes)(*)Called MINUS in Oracle, but does not support the ALL optionYes(Yes)(*)Does not support EXCEPT ALLYesNoYes(*)Since 10.3No(Yes)(*)Does not support EXCEPT ALLYesYes(Yes)(*)Does not support EXCEPT ALL
ORDER BY ... NULLS LASTYesYesNoYesNoNoYesYesYesYesYes(*)Since 3.30
IS DISTINCT FROMNoYesNo(Yes)(*)Not supported by DB2 LUWYes(*)Using the operator <=>Yes(*)Using the operator <=>YesNoYesNoNo
OVERLAPS(*)Checks for overlapping intervals, e.g.:
(date '2014-01-01', date '2014-09-01') overlaps (date '2014-04-01', date '2014-05-01')
(Yes)(*)Oracle supports the OVERLAPS operator, but this is undocumentedYesNoYes(*)Since 11.1NoNoNoNoYesNoNo(*)SQLite doesn't support real DATE or TIMESTAMP values
Partitioned outer join(*)A JOIN operator that can be used to fill gaps in sparse data, mainly time series.
(This is unrelated to "partion wise joins" between two partitioned tables)
YesNo(*)Since For a single time series this can be simulated using generate_series()NoNoNoNoNoNoNoNoNo
OtherOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Catalogs ("databases")(Yes)(*)Pluggable databases are available since 12.1, cross-database queries are not supported(Yes)(*)Queries between different database (=catalogs) are not possibleYesNoYesYes(Yes)(*)Queries between different database (=catalogs) are not possibleYesYesNoYes
INFORMATION_SCHEMA(*)Support for the INFORMATION_SCHEMA defined in the SQL standardNoYesYesNoYesYesNoYesYesNoNo
NoSQL FeaturesOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
XML Support(*)Support for a validating XML data typeYesYesYesYesYesYesNoNoNoNoNo
XPath(*)Support for a XPath expressions on XML dataYesYesYesYesYesYesNoNoNoNoNo
JSON(*)Support for a (validating) JSON data type and corresponding functionsYes(*)Introduced in version*)Since SQL Server 2016Yes(*)Since 11.5Yes(*)Since 5.7Yes(*)No JSON data type, only JSON functionsNoNoNoNo(*)Derby 10.12 has added some basic support to convert to and from JSONYes(*)Through a loadable extension
SQL/JSON Path(*)Support for SQL/JSON and JSON Path queriesYes(*)Since 18Yes(*)Uses non-standard function names, but supports the full JSON path syntaxYes(*)Since 2016NoNoNoNoNoNoNoNo
Indexes on JSON documents(*)Create an index on a JSON column (and the complete JSON value) to support arbitrarty queries for elements inside the JSON valueYes(*)Since 12.2Yes(No)(*)It's possible to create computed columns that exctracts a single value and index that computed column.Yes(No)(*)It's possible to create computed columns that exctracts a single value and index that computed column.(No)(*)It's possible to create computed columns that exctracts a single value and index that computed column.NoNoNoNo(Yes)(*)Only scalar values, not the entire document
Key/Value storageNoYesNoNoNo(*)A key/value store is available through the "Memcached API" but it's not usable in SQLNoNoNo(*)The MVStore is a key/value store, but it's not usable through SQLNoNoNo
SecurityOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
User groups / RolesYesYesYesYesYes(*)Since 8.0Yes(*)Since 10.0.5YesYesYesYesNo
Row level security(*)Allow access to the data on row level based on rules for each (database) userYesYes(*)Since 9.5YesYesNoNoNoNoYes(*)Since 2.5.0NoNo
Grant on column level(*)Grant access to only some of the columns of a table(Yes)(*)Not possible for SELECT grantYesYesYesYesYesYesNoYesNoNo