Last updated: 2017-03-16
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: dbms_comparison@sql-workbench.net

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
Queries
Window functionsYesYes(*)DISTINCT is not supported inside a window functionYes(*)DISTINCT is not supported inside a window functionYesNoNo(*)Will be available in MariaDB 10.2Yes(*)Since 3.0NoNoNoNo
Common Table ExpressionsYesYesYesYesNoNo(*)Will be available in MariaDB 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 queryYesYesNoNoNoNoYesNoYesNoYes
Recursive QueriesYesYesYesYesNoNo(*)Will be available in MariaDB 10.2YesYesYesNoYes(*)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.4NoNoNoNoNoNoYesNoNo
PIVOT SupportYesNo(*)The crosstab function can be used for this.YesNoNoNoNoNoNoNoNo
GROUP BY .. ROLLUPYesYes(*)Since 9.5YesYesYesYesNoNoNoYesNo
Temporal queries(*)Temporal queries allow querying the database (or a single table) to return the data as it was in the pastYesNoNoYesNoNoNoNoNoNoNo
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 CPUsYes(Yes)(*)Only for certain types of joins and aggregatesYesYesNoNoNoNoNoNoNo
Aggregates for stringsYes(*)Limited to 32kYesNoYesYesYesYesYesYesNoYes
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'
YesYesNoYesYesYesYesYesYesNoNo
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
Regular ExpressionsOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Comparison based on RegEx(*)Conditions with regular expressions that can be used e.g. in a WHERE clauseYesYesNoNoYesYesYesYesYesNoNo
Substring(*)Extract the part of a string value based on a RegExYesYesNoNoNoYes(*)Since 10.0.5Yes(*)Since 3.0NoYesNoNo
Replace(*)Replace values in a string based on a RegExYesYesNoNoNoYes(*)Since 10.0.05NoYesYes(*)Since 2.3.4NoNo
ConstraintsOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Deferred constraints(*)Define constraints that are checked only at commit timeYesYesNoNoNoNoNoNoNoYes(*)Since 10.11Yes
Check constraintsYesYesYesYesNoNo(*)Will be in 10.2YesYesYesYesYes
Check constraints with sub-queryNoNoNoNoNoNoYesNoNoNoNo
Check constraints using custom functions(*)Create a check constraint based on a user-defined functionYesYesYesYesNoNoYesNoNoNoNo(*)Possible through C function, not through SQL functions
Exclusion constraints(*)Constraints that prevent e.g. overlapping date ranges (WITHOUT OVERLAP in ANSI SQL)NoYesNoNoNoNoNoNoNoNoNo
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
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'
NoNoNoNoNoNoNoYes
Descending Index(*)Define an index that is sorted descendingYesYesYesYesNo(*)Will be in 8.0NoYesYesYesNoYes
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 NoNoYesYesNoNoNoNoNoNoNo
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 startedYesYesYesYesNoNoYesYesYesYesYes
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 obtainedYesYesYesNoNo(*)Will be in 8.0NoNoNoNoNoNo
RETURNING clause as a result setNoYesYesNoNoNoYesNoNoNoNo
Parallel DMLYesNoNoNoNoNoNoNoNoNoNo
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.
YesYesNoNo
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.No(*)The boolean data type can only be used in procedural codeNo(*)MySQL's BOOLEAN is only a synonym for TINYINTNo(*)MariaDB's BOOLEAN is only a synonym for TINYINTYes(*)Since 3.0YesYesYesNo
IntervalYesYesNoNoNoNoNoNoYesNoNo
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
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 columnsYesNoYesYesYes(*)Since 5.7Yes(*)Since 5.2YesYesYesNoNo
Functions as column default(*)Use any function (including user defined functions) as the default for a columnNo(*)Only built-in functions can be used. No PL/SQL functionsYesYesNoNoNo(*)Will be available in 10.2NoYesYes(*)When Postgres or Oracle compatibility is enabledNo(Yes)(*)Only functions written in C can be used
SequencesYesYesYesYesNoNoYesYesYesYesNo
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)(*)Done through table inheritance and triggersYesYesYesYesNoNoNoNoNo
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
NoNoNoNoYesYesYesYesYesNoNo
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 disconnectedNoYesYesNoYesYesYesYesYesNoYes
Use a temporary table twice in a single queryYesYesYesYesNoNoYesYesYesNo(*)Derby has no temporary tablesYes
ProgrammingOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Stored procedures(*)Writing and managing stored procedures using SQL commandsYes(Yes)(*)Only functions, no proceduresYesYesYesYesYesNoYesNo(*)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 functionsNoNoNoNoNoYesNoNo
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
YesNoNoNoNoNoNoNoNoNo
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 triggersYesYesNoNoNoNoYesNoNoNoNo
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
YesYes(*)Since 9.4YesYesYesYesYesNoYesNoNo
Triggers on viewsYesYesYesYesNoNoYesNoYesNoYes
Views with derived tables(*)Create a view that uses a derived tableYesYesYesYesNoNoYesYesYesYesYes
JOINs and OperatorsOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
CROSS JOINYesYesYesYesYesYesYesYesYesYesYes
FULL OUTER JOINYesYesYesYesNoNoYesNoYesYesNo
LATERAL JOINYes(*)Since 12cYes(*)Since 9.3(Yes)(*)Called APPLY
Inner joins are not supported.
Lateral joins against a derived table are not supported
YesNoNoNoNoYesNoNo
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 ALLYesNoNoNo(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 ALLYesNoNoNo(Yes)(*)Does not support EXCEPT ALLYesYes(Yes)(*)Does not support EXCEPT ALL
ORDER BY ... NULLS LASTYesYesNoYesNoNoYesYesYesYesNo
IS DISTINCT FROMNoYesNo(Yes)(*)Not supported by DB2 LUWYes(*)Using the operator <=>Yes(*)Using the operator <=>YesNoYesNoNo
BETWEEN SYMMETRICNoYesNoNoNoNoNoNoYesNoNo
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 undocumentedYesNoNoNoNoNoNoYesNoNo(*)SQLite doesn't support real DATE or TIMESTAMP values
OtherOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
Catalogs ("databases")No(Yes)(*)Queries between different database (=catalogs) are not possibleYesNoYesYes(Yes)(*)Queries between different database (=catalogs) are not possibleYesYesNoYes
SchemasYesYesYesYesNoNoNoYesYesYesNo
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
XQueryYesNoYesYesNoNoNoNoNoNoNo
JSON(*)Support for a (validating) JSON data type and corresponding functionsYes(*)Introduced in version 12.1.0.2YesYes(*)Since SQL Server 2016(Yes)(*)Not accessible through SQL but DB2 offers a MongoDB compatible APIYes(*)Since 5.7No(*)Will be available in 10.2 (but not JSON data type, only JSON functions)NoNoNoNo(*)Derby 10.12 has added some basic support to convert to and from JSONYes(*)Through a loadable extension
Indexes on JSON documents(*)Create an index on a JSON column to support conditions on elements of the documentYesYes(*)Can index the complete document(Yes)(*)Only on scalar values of the documentYes(No)(*)A single attribute can be indexed by creating a computed column and indexing thatNoNoNoNoNo(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 SQLYes(*)Dynamic columns can be used for this. But they can't be indexedNoNo(*)The MVStore is a key/value store, but it's not usable through SQLNoNoNo
SecurityOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBDerbySQLite
User groups / RolesYesYesYesYesNo(*)Will be in 8.0NoYesYesYesYesNo
Row level security(*)Allow access to the data on row level based on rules for each (database) userYesYes(*)Since 9.5YesYesNoNoNoNoNoNoNo
Grant on column level(*)Grant access to only some of the columns of a tableYesYesYesYesYesYesYesNoYesNoNo