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

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 DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
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.0Yes(*)Since 2.0NoYes(*)Since 3.25
Common Table ExpressionsYesYesYesYesYes(*)Since 8.0Yes(*)Since 10.2YesYes(*)Since 2.0YesYes(*)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.0NoYesNoYesYes
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.
YesYes(*)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. YesNoNoNoNoYesYes
Filtered aggregates(*)Only include rows in an aggregate based on a condition:
avg(salary) filter (where dept_id = 1)
NoYes(*)Since 9.4NoNoNoNoYes(*)Since 4.0NoYesYes(*)Since 3.25
PIVOT SupportYesNo(*)The crosstab function can be used for this.YesNoNoNoNoNoNoNo
GROUP BY .. ROLLUPYesYes(*)Since 9.5YesYesYesYesNoNoYes(*)Since 2.5.1No
GROUP BY .. GROUPING SETS(*)Create multiple independent groups with a single GROUP BY queryYesYes(*)Since 9.5YesYesNoNoNoNoYes(*)Since 2.5.1No
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.0No
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 enabledYes
Parallel queries(*)The ability to distribute a single query over serveral CPUsYesYes(*)Full parallel query support since Postgres 11YesYesNoNoNoNoNoNo
Aggregates for stringsYes(*)Limited to 32kYesYes(*)DISTINCT is not supported.
Can not be used as a window function.
Window string aggregates(*)Use string aggregation as a window function(Yes)(*)ORDER BY is not allowed in the window definitionYesNoYesNoNo(Yes)(*)ORDER BY is not allowed in the window definitionYesNoYes
Tuple comparison(Yes)(*)Not supported for >, < <> or between operatorsYesNoYesYes(*)Not supported with the BETWEEN operatorYes(*)Not supported with the BETWEEN operatorNoYesYes(Yes)(*)Not possible with constant values for an IN clause
e.g.(a,b) IN ( (1,2), (3,4) )
Tuple updatesYesYes(*)Since 9.5NoYesNoNoNoYesYesYes(*)Since 3.15
UPDATE with a joinNoYesYesNoYesYesNoNoNoNo
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 codeNoNoYesNoYesYesNoYesNoNo
UNNEST(*)Convert an array into a set of rowsNoYesNoYesNoNoNoNoYesNo
Split string to rows(*)Split a string delimited by a specific character into multiple rows (usable like a table)NoYesYes(*)Since 2016NoNoNoNoNoNoNo
Regular ExpressionsOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
Comparison based on RegEx(*)Conditions with regular expressions that can be used e.g. in a WHERE clauseYesYesNoYes(*)Since 11.1YesYesYesYesYesNo
Substring(*)Extract the part of a string value based on a RegExYesYesNoYes(*)Since 11.1Yes(*)Since 8.0Yes(*)Since 10.0.5Yes(*)Since 3.0NoYesNo
Replace(*)Replace values in a string based on a RegExYesYesNoYes(*)Since 11.1Yes(*)Since 8.0Yes(*)Since 10.0.05NoYesYes(*)Since 2.3.4No
ConstraintsOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
Deferred foreign key constraints(*)Define constraints that are checked only at commit timeYesYesNoNoNoNoNoNoNoYes
Check constraintsYesYesYesYesYes(*)Since 8.0.16Yes(*)Since 10.2YesYesYesYes
Check constraints with sub-queryNoNoNoNoNoNoYesYesNoNo
Check constraints using custom functions(*)Create a check constraint based on a user-defined functionNoYesYesYesNoNoYesNoNoNo(*)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.0NoYes(*)Only with date/timestamp rangesNoNoNoNo
Statement based constraint evaluationYesYesYesYesNoNoNoYesYesYes
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)NoYesYesYesYesYesYes
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 ignoredNoNoYesNo(*)The definition is accepted, but ignored
IndexingOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
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 indexesYesYesYes
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 LUWYes(*)Since 8.0.13(No)(*)Can be simulating by indexing a computed column(Yes)(*)Limited to a single expression. Can not be combined with additional columnsNoNoYes(*)Since 3.9
Index using a custom function(*)Create an expression index using a custom function (written in a "SQL" procedural language)YesYesNoYesNoNoNoNoNoNo(*)Functions written in C can be indexed
Index include columns(*)Define an index on some columns and include other (non-indexed) columns NoYes(*)Since 11YesYesNoNoNoNoNoNo
Multi-column statistics(*)Create extended statistics storing dependencies between values in the columns of a single tableYesYesYesYesNoNoNoNoNoNo
Clustered index(*)An index that contains the table data (index and table storage are the same)Yes(*)Called Index Organized TableNoYesYesYesYesNoNoNoYes
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 indexesYes(*)Since Since Postgres 15 this can be defined when creating the indexNoNoYes(*)The behaviour depends on the storage engine being used.Yes(*)The behaviour depends on the storage engine being used.NoYesYesYes
DMLOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
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 statementNoYesYesYesYesYesNoYesYesYes
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 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-selectYesYesYesYesNoNoYesYesYesNo(*)The query is accepted but updates the data incorrectly
MERGE(*)Update rows if they exist, insert if notYesYes(*)Since 15YesYesNoNoYesYesYesNo
UPSERT(*)Do an UPDATE or INSERT which is safe for concurrent executionYes(*)MERGE can be used for a similar functionalityYes(*)Using insert ... on conflict(No)(*)MERGE can be used for a similar functionality(No)(*)MERGE can be used for a similar functionalityYes(*)Using INSERT .. ON DUPLICATEYes(*)Using INSERT .. ON DUPLICATEYesYes(No)(*)MERGE can be used for a similar functionalityYes
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.0NoNoNoNoNo
RETURNING clause as a result setNoYesYesNoNoNoYesNoYes(*)Since 2.5.1No
Parallel DML(*)Use multiple threads/workers for a single DML statementYesNoNoNoNoNoNoNoNoNo
Data Types(*)Data types that can be used for the column of a tableOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
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 tableYesNoNoNoNoYesNo
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 deprecatedNoNoNoYesYesYesNo
Distinct types(*)User defined types which cannot be compared. E.g. prevent comparing a product_id to a customer_idNoNoNoYesNoNoNoNoNoNo
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)NoYesNoNoYesYesNoNoNoNo
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.Yes(*)Since 11.1No(*)MySQL's BOOLEAN is only a synonym for TINYINTNo(*)MariaDB's BOOLEAN is only a synonym for TINYINTYes(*)Since 3.0YesYesNo
TIME(*)A data type that only stores a timeNoYesYesYesYesYesYesYesYesNo
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.
YesYes(*)TIMESTAMP has a very limited range: from 1970 up to 2038.Yes(*)TIMESTAMP has a very limited range: from 1970 up to 2038.YesYesYesNo
TIME ZONE Support(*)Support for time zones (with TIMESTAMP values)YesYesYes(*)The data type is called datetimeoffsetNoNoNoYes(*)Since 4.0YesYesNo
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
UUID(*)A dedicated data type for UUID storageNoYesYesNoNoYes(*)Since 10.7.0NoYesYesNo
DDLOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
Transactional DDL(*)The ability to rollback any DDL statementNoYesYesYesNoNoYesNoNoYes
Computed columns(*)Define a column in a table that is always calculated based on other columnsYesYes(*)Since 12YesYesYes(*)Since 5.7Yes(*)Since 5.2YesYesYesNo
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 enabled(Yes)(*)Only functions written in C can be used
SequencesYesYesYesYesNoYes(*)Since 10.3YesYesYesNo
Auto increment columns(*)Columns that are populated automatically with unique values without the usage of triggersYes(*)Since 12cYesYesYesYesYesYesYesYesYes
IDENTITY columns(*)Standard compliant IDENTITY columns(Yes)(*)Does not support OVERRIDING SYSTEM VALUEYes(*)Since 10No(*)SQL Server's identity columns are not compatible with the SQL standard.(Yes)(*)Does not support OVERRIDING SYSTEM VALUENoNoYes(*)Since 3.0YesYesNo
SynonymsYesNoYesYesNoNoNoNoYes(*)Since 2.3.4No
Non-blocking index creation(*)Create an index without blocking DML on the tableYesYesYesYesNoNoNoNoNoNo
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 keyNoNoNoNo
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.NoYesYesNo
DDL Triggers(*)Define triggers that are fired when a DDL statement is executedYesYesYesNoNoNoYes(*)Since 3.0NoNoNo
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 constraintYesYesYesYesNoNoYesYesYesYes
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
Materialized views(*)Persist the result of a query as a tableYesYesYes(*)Called indexed viewsYes(*)Called materialized query tables (MQT)NoNoNoNoNoNo
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 changedYesNoYesYesNoNoNoNoNoNo
Temporary TablesOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
Permanent global temporary tables(*)Tmporary tables that are created once and that need to be dropped manualy (their definition is retained across server restarts)YesNoNoYesNoNoYesNoYesNo
Global temporary tables(*)Temporary tables that are always visible, but the data is session specificNoNoYesNoNoNoNoYesNoNo
Session local temporary tables(*)Temporary tables that are automatically dropped at the end of the transaction or when the session is disconnectedYes(*)Since 19YesYesNoYesYesNoYesYesYes
Use a temporary table twice in a single queryYesYesYesYesNoYes(*)Since 10.2.1YesYesYesYes
ProgrammingOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
Stored procedures(*)Writing and managing stored procedures using SQL commandsYesYes(*)Procedures only since Postgres 11YesYesYesYesYesNoYesNo(*)Can be written in C
Table functions(*)Functions that return result sets and can be used like a tableYesYesYesYesNoNoYesNoYesNo(*)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)NoNoNoNoYesNo
Function overloading(*)Create different versions of the same function that are distinguished by their argument listYes(*)Only inside packagesYesNoYesNoNoNoNoYesNo
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 JavaYesNo
Row level triggers(*)Triggers that fire once for each rowYesYesNoYesYesYesYesNo(*)Triggers can only be written in JavaYesYes
RETURNING clause in a programming language(*)Use a RETURNING clause from within a programming languageYesYesYesNoNoNoYesNoNoNo
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 JavaYesYes
Dynamic SQL in functions(*)The ability to use dynamic SQL in stored functionsYesYesNo(*)Possible in CLR functionsYesNoNoYesNoNoNo
Dynamic SQL in triggers(*)The ability to use dynamic SQL in triggersYesYesYesNoNoNoYesNoNoNo
Delete triggers fired by cascading deletes(*)When rows are deleted due to a ON DELETE CASCADE foreign key, are delete triggers fired?YesYesYesYesNoNoYesNoYesYes(*)Only with PRAGMA recursive_triggers=ON
Built-in schedulerYesNoYesYesYesYesNoNoNoNo
ViewsOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
Updateable ViewsYesYesYesYesYesYesYesNoYesNo
WITH CHECK OPTION(*)Create updateable views where only rows can be updated/deleted/inserted
that match the WHERE clause of the view
Triggers on viewsYesYesYesYesNoNoYesNoYesYes
Views with derived tables(*)Create a view that uses a derived tableYesYesYesYesNoNoYesYesYesYes
JOINs and OperatorsOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
CROSS JOINYesYesYesYesYesYesYesYesYesYes
LATERAL JOINYes(*)Since 12cYes(Yes)(*)Called APPLY
Inner joins are not supported.
Lateral joins against a derived table are not supported
YesYes(*)Since 8.0.14NoYes(*)Since 4.0NoYesNo
JOIN ... USING (...)(*)A shortcut notation for the JOIN operator when both columns have the same name.YesYesNoNoYesYesYesNoYesYes
JOINs using tuple comparison(*)Use tuples in JOIN conditionsYesYesNoYesYesYesNoYesYesNo
INTERSECT(Yes)(*)Does not support INTERSECT ALLYes(Yes)(*)Does not support INTERSECT ALLYesNoYes(*)Since 10.3No(Yes)(*)Does not support INTERSECT ALLYes(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 ALLYes(Yes)(*)Does not support EXCEPT ALL
ORDER BY ... NULLS LASTYesYesNoYesNoNoYesYesYesYes(*)Since 3.30
IS DISTINCT FROMNoYesNo(Yes)(*)Not supported by DB2 LUWYes(*)Using the operator <=>Yes(*)Using the operator <=>YesNoYesNo
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.1NoNoNoNoYesNo(*)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(*)For a single time series this can be simulated using generate_series()NoNoNoNoNoNoNoNo
OtherOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
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 possibleYesYesYes
INFORMATION_SCHEMA(*)Support for the INFORMATION_SCHEMA defined in the SQL standardNoYesYesNoYesYesNoYesYesNo
NoSQL FeaturesOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
XML Support(*)Support for a validating XML data typeYesYesYesYesYesYesNoNoNoNo
XPath(*)Support for a XPath expressions on XML dataYesYesYesYesYesYesNoNoNoNo
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 functionsNoNoNoYes(*)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 2016NoNoNoNoNoNoNo
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.NoNoNo(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 SQLNoNo
SecurityOraclePostgresSQL ServerIBM DB2MySQLMariaDBFirebirdH2HSQLDBSQLite
User groups / RolesYesYesYesYesYes(*)Since 8.0Yes(*)Since 10.0.5YesYesYesNo
Row level security(*)Allow access to the data on row level based on rules for each (database) userYesYes(*)Since 9.5YesYesNoNoNoNoYes(*)Since 2.5.0No
Grant on column level(*)Grant access to only some of the columns of a table(Yes)(*)Not possible for SELECT grantYesYesYesYesYesYesNoYesNo