Last updated: 2016-12-20
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 ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
Queries
Window functionsYesYes(*)DISTINCT is not supported inside a window functionYes(*)DISTINCT is not supported inside a window functionNo(*)Available in MariaDB 10.2.0YesYes(*)Since 3.0NoNoNoNo
Common Table ExpressionsYesYesYesNo(*)Available in MariaDB 10.2.1YesYesNoYesNoYes(*)Since 3.8.3
CTE in a sub-query(*)Use a common table expression in a sub-query, not only as a top level queryYesYesNoNoNoYesNoYesNoYes
Recursive QueriesYesYesYesNoYesYesYesYesNoYes(*)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. NoYesNoNoYesYesYes
Filtered aggregates(*)Only include rows in an aggregate based on a condition:
avg(salary) filter (where dept_id = 1)
NoYes(*)Since 9.4NoNoNoNoNoYesNoNo
PIVOT SupportYesNo(*)The crosstab function can be used for this.YesNoNoNoNoNoNoNo
GROUP BY .. ROLLUPYesYes(*)Since 9.5YesYesYesNoNoNoYesNo
Temporal queries(*)Temporal queries allow querying the database (or a single table) to return the data as it was in the pastYesNoNoNoYesNoNoNoNoNo
SELECT without a FROM clauseNoYesYes(Yes)(*)No WHERE clause is allowed e.g.
select 42 where not exists (...);
NoNoYesYes(*)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 aggregatesYesNoYesNoNoNoNoNo
Aggregates for stringsYes(*)Limited to 32kYesNoYesYesYesYesYesNoYes
Tuple comparisonYesYesNoYesYesNo(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.5NoNoYesNoYesYesNoYes(*)Since 3.15
UPDATE with a joinNoYesYesYesNoNoNoNoNoNo
ANSI date literals(*)Specify date or timestamps using ANSI literals, e.g.
DATE '2014-01-31' or timestamp '2014-04-25 19:18:17'
YesYesNoYesYesYesYesYesNoNo
Query variables(*)Variables that can be used inside a single query without the need to use procedural codeNoNoYesYesNoNoYesNoNoNo
Regular ExpressionsOraclePostgresSQL ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
Comparison based on RegEx(*)Conditions with regular expressions that can be used e.g. in a WHERE clauseYesYesNoYesNoYesYesYesNoNo
Substring(*)Extract the part of a string value based on a RegExYesYesNoNoNoYes(*)Since 3.0NoYesNoNo
Replace(*)Replace values in a string based on a RegExYesYesNoNo(*)MariaDB 10.1 supports regexp_replaceNoNoYesYes(*)Since 2.3.4NoNo
ConstraintsOraclePostgresSQL ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
Deferred constraints(*)Define constraints that are checked only at commit timeYesYesNoNoNoNoNoNoYes(*)Since 10.11Yes
Check constraintsYesYesYesNoYesYesYesYesYesYes
Check constraints with sub-queryNoNoNoNoNoYesNoNoNoNo
Check constraints using custom functions(*)Create a check constraint based on a user-defined functionYesYesYesNoYesYesNoNoNoNo(*)Possible through C function, not through SQL functions
Exclusion constraints(*)Constraints that prevent e.g. overlapping date ranges (WITHOUT OVERLAP in ANSI SQL)NoYesNoNoNoNoNoNoNoNo
Statement based constraint evaluationYesYesYesNoYesNoYesYesYesYes
ON DELETE CASCADE(*)For foreign keysYesYes(Yes)(*)Not for self-referencing FK constraints (to the same table)YesYesYesYesYesYesYes
ON UPDATE CASCADE(*)For foreign keysNoYes(Yes)(*)Not for self-referencing FK constraints (to the same table)YesNoYesYesYesNoYes
IndexingOraclePostgresSQL ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
Partial index(*)Define an index on a subset of a tableYes(*)Through a function based indexYesYesNoNoNoNoNoNoYes
Descending Index(*)Define an index that is sorted descendingYesYesYesNoYesYesYesYesNoYes
Index on expression(*)Create an index based on an expression/functionYesYes(No)(*)Can be simulated using an index on a computed column(No)(*)Can be simulating by indexing a computed column(Yes)(*)Not for DB2 LUW(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)YesYesNoNoYesNoNoNoNoNo(*)Functions written in C can be indexed
Index include columns(*)Define an index on some columns and include other (non-indexed) columns NoNoYesNoYesNoNoNoNoNo
Clustered index(*)An index that contains the table data (index and table storage are the same)Yes(*)Called Index Organized TableNoYesYesYesNoNoNoNoYes
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 indexesYesNoYes(*)The behaviour depends on the storage engine being used.NoNoYesYesNoYes
DMLOraclePostgresSQL ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
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 statementNoNoNoNoNoNoNo
Multi-row INSERTs(*)Insert more than one row with a single INSERT statementNoYesYesYesYesNoYesYesYesYes
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 cascadeYesNoNoNoNoNoNoNoNo
Read consistency during DML operations(*)During a DML operation reading a column value should return the value that was valid before the statement startedYesYesYesNoYesYesYesYesYesYes
Use target table in sub-queries(*)Use the targe table of an UPDATE, DELETE or INSERT statement in a sub-selectYesYesYesNoYesYesYesYesYesNo(*)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 conflictYesYes(*)Using INSERT .. ON DUPLICATEYesYesYesYesYes(*)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 obtainedYesYesYesNoNoNoNoNoNoNo
RETURNING clause as a result setNoYesYesNoNoYesNoNoNoNo
Parallel DMLYesNoNoNoNoNoNoNoNoNo
Data Types(*)Data types that can be used for the column of a tableOraclePostgresSQL ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
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 tableNoYesNoNoYesNoNo
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 deprecatedNoNoYesYesYesNoNo
Distinct types(*)User defined types which cannot be compared. E.g. prevent comparing a product_id to a customer_idNoNoNoNoYesNoNoNoNoNo
ArraysNoYesNoNoNoYesYesYesNoNo
Enums(*)De-normalize lookup values by specifying a fixed set of allowed values (a special case of a check constraint)NoYesNoYesNoNoNoNoNoNo
IP addressNoYesNoNoNoNoNoNoNoNo
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(*)MySQL's BOOLEAN is only a synonym for TINYINTNo(*)The boolean data type can only be used in procedural codeYes(*)Since 3.0YesYesYesNo
IntervalYesYesNoNoNoNoNoYesNoNo
TIME(*)A data type that only stores a timeNoYesYesYesYesYesYesYesNoNo
DATE(*)A data type that only stores a date (without a time)No(*)Oracle's DATE type stores date and timeYesYesYesYesYesYesYesYesNo
TIMESTAMP(*)A data type that stores a date and timeYesYesYes(*)The data type is named datetime or datetime2.
timestamp is something different.
Yes(*)TIMESTAMP has a very limited range: from 1970 up to 2038.YesYesYesYesYesNo
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. YesNoNoNoNoNoNoNoNo
DDLOraclePostgresSQL ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
Transactional DDL(*)The ability to rollback any DDL statementNoYesYesNoYesYesNoNoNoYes
Computed columns(*)Define a column in a table that is always calculated based on other columnsYesNoYesYes(*)Since 5.7YesYesYesYesNoNo
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 functionsYesYesNoNoNoYesYes(*)When Postgres or Oracle compatibility is enabledNo(Yes)(*)Only functions written in C can be used
SequencesYesYesYesNoYesYesYesYesYesNo
Auto increment columns(*)Columns that are populated automatically with unique values without the usage of triggersYes(*)Since 12cYesYesYesYesYesYesYesYesYes
SynonymsYesNoYesNoYesNoNoYes(*)Since 2.3.4YesNo
Non-blocking index creation(*)Create an index without blocking DML on the tableYesYesYesNoYesNoNoNoNoNo
PartitioningYes(Yes)(*)Done through table inheritance and triggersYesYesYesNoNoNoNoNo
Cascading DROP(*)Drop a table including incoming foreign keysYesYesNoNo(*)MySQL accepts the CASCADE keyword but silently ignores it.YesNoYesYesNoNo
DDL Triggers(*)Define triggers that are fired when a DDL statement is executedYesYesYesNoNoYes(*)Since 3.0NoNoNoNo
TRUNCATE Trigger(*)Define triggers that are fired when a TRUNCATE statement is executed(No)(*)Possible through a system triggerYesNoNoNoNoNoNoNoNo
Custom name for PK constraint(*)Specify the name of the PK constraintYesYesYesNoYesYesYesYesYesYes
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 typeYesYesYesYesYesYesYesYes
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
NoNoNoYesNoYesYesYesNoNo
Materialized views(*)Persist the result of a query as a tableYesYesYes(*)Called indexed viewsNoYes(*)Called materialized query tables (MQT)NoNoNoNoNo
MVIEW with query rewrite(*)If applicable can the optimizer detect that a query against the base tables can be done using the materialized viewYesNoYesNoNoNoNoNoNoNo
Automatically updated MVIEWS(*)MVIEW is automatically updated when the underlying tables are changedYesNoYesNoYesNoNoNoNoNo
Temporary TablesOraclePostgresSQL ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
Permanent global temporary tables(*)Tmporary tables that are created once and that need to be dropped manualy (their definition is retained across server restarts)YesNoNoNoYesYesNoYesNoNo
Global temporary tables(*)Temporary tables that are always visible, but the data is session specificNoNoYesNoNoNoYesNoNoNo
Session local temporary tables(*)Temporary tables that are automatically dropped at the end of the transaction or when the session is disconnectedNoYesYesYesNoYesYesYesNoYes
Use a temporary table twice in a single queryYesYesYesNoYesYesYesYesNo(*)Derby has no temporary tablesYes
ProgrammingOraclePostgresSQL ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
Stored procedures(*)Writing and managing stored procedures using SQL commandsYes(Yes)(*)Only functions, no proceduresYesYesYesYesNoYesNo(*)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 tableYesYesYesNoYesYesNoYesNoNo(*)Can be written in C
Custom aggregates(*)Create aggregate functions using SQLYesYesNo(*)Can be done with CLR functionsNoNoNoNoYesNoNo
Function overloading(*)Create different versions of the same function that are distinguished by their argument listYes(*)Only inside packagesYesNoNoYesNoNoYesNoNo
User defined operators(*)Create new (comparison) operators for user defined data typesNo(*)CREATE OPERATOR only creates functions.
Creating operators like =, <, > is not possible
YesNoNoNoNoNoNoNoNo
Statement level triggers(*)Triggers that fire once for each statementYesYesYesNoYesNoNo(*)Triggers can only be written in JavaYesYesNo
Row level triggers(*)Triggers that fire once for each rowYesYesNoYesYesYesNo(*)Triggers can only be written in JavaYesYesYes
RETURNING clause in a programming language(*)Use a RETURNING clause from within a programming languageYesYesYesNoNoYesNoNoNoNo
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 purposeYesYesYesNo(*)Triggers can only be written in JavaYesYesYes
Dynamic SQL in functions(*)The ability to use dynamic SQL in stored functionsYesYesNo(*)Possible in CLR functionsNoYesYesNoNoNoNo
Dynamic SQL in triggers(*)The ability to use dynamic SQL in triggersYesYesNoNoNoYesNoNoNoNo
Delete triggers fired by cascading deletes(*)When rows are deleted due to a ON DELETE CASCADE foreign key, are delete triggers fired?YesYesYesNoYesYesNoYesYesYes(*)Only with PRAGMA recursive_triggers=ON
Built-in schedulerYesNoYesYesYesNoNoNoNoNo
ViewsOraclePostgresSQL ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
Updateable ViewsYesYesYesYesYesYesNoYesNoNo
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.4YesYesYesYesNoYesNoNo
Triggers on viewsYesYesYesNoYesYesNoYesNoYes
Views with derived tables(*)Create a view that uses a derived tableYesYesYesNoYesYesYesYesYesYes
JOINs and OperatorsOraclePostgresSQL ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
CROSS JOINYesYesYesYesYesYesYesYesYesYes
FULL OUTER JOINYesYesYesNoYesYesNoYesYesNo
LATERAL JOINYes(*)Since 12cYes(*)Since 9.3(Yes)(*)Called APPLY
Inner joins are not supported.
Lateral joins against a derived table are not supported
NoYesNoNoYesNoNo
JOIN ... USING (...)(*)A shortcut notation for the JOIN operator when both columns have the same name.YesYesNoYesNoYesNoYesYesYes
JOINs using tuple comparison(*)Use tuples in JOIN conditionsYesYesNoYesYesNoYesYesNoNo
INTERSECT(Yes)(*)Does not support INTERSECT ALLYes(Yes)(*)Does not support INTERSECT ALLNoYesNo(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 ALLNoYesNo(Yes)(*)Does not support EXCEPT ALLYesYes(Yes)(*)Does not support EXCEPT ALL
ORDER BY ... NULLS LASTYesYesNoNoYesYesYesYesYesNo
IS DISTINCT FROMNoYesNoYes(*)Using the operator <=>NoYesNoYesNoNo
BETWEEN SYMMETRICNoYesNoNoNoNoNoYesNoNo
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 undocumentedYesNoNoNoNoNoYesNoNo(*)SQLite doesn't support real DATE or TIMESTAMP values
OtherOraclePostgresSQL ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
Catalogs ("databases")No(Yes)(*)Queries between different database (=catalogs) are not possibleYesYesNo(Yes)(*)Queries between different database (=catalogs) are not possibleYesYesNoYes
SchemasYesYesYesNoYesNoYesYesYesNo
INFORMATION_SCHEMA(*)Support for the INFORMATION_SCHEMA defined in the SQL standardNoYesYesYesNoNoYesYesNoNo
NoSQL FeaturesOraclePostgresSQL ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
XML Support(*)Support for a validating XML data typeYesYesYesYesYesNoNoNoNoNo
XPath(*)Support for a XPath expressions on XML dataYesYesYesYesYesNoNoNoNoNo
XQueryYesNoYesNoYesNoNoNoNoNo
JSON(*)Support for a (validating) JSON data type and corresponding functionsYes(*)Introduced in version 12.1.0.2YesYes(*)Since SQL Server 2016Yes(*)Since 5.7(Yes)(*)Not accessible through SQL but DB2 offers a MongoDB compatible APINoNoNoNo(*)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 document(No)(*)A single attribute can be indexed by creating a computed column and indexing thatYesNoNoNoNo(Yes)(*)Only scalar values, not the entire document
Key/Value storageNoYesNoNo(*)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 ServerMySQLIBM DB2FirebirdH2HSQLDBDerbySQLite
User groups / RolesYesYesYesNoYesYesYesYesYesNo
Row level security(*)Allow access to the data on row level based on rules for each (database) userYesYes(*)Since 9.5YesNoYesNoNoNoNoNo
Grant on column level(*)Grant access to only some of the columns of a tableYesYesYesYesYesYesNoYesNoNo