RDBMS -Feature Comparision

January 12, 2015 · by · in MongoDB

Oracle vs Postgres vs SQLServer vs MySQL vs IBM-DB2 vs Firebird vs H2 HSQLDB vs Derby vs SQLite

This comparison focuses on 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.

Feature Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
Queries
Window functions Yes Yes Yes No Yes No(*) No No No No
Common Table Expressions Yes Yes Yes No Yes Yes No Yes No Yes(*)
Recursive Queries Yes Yes Yes No Yes Yes Yes Yes No Yes(*)
Row constructor(*) No Yes Yes(*) Yes Yes No No Yes No No
Filtered aggregates(*) No Yes(*) No No No No No Yes No No
PIVOT Support Yes No(*) Yes No No No No No No No
GROUP BY .. ROLLUP Yes No Yes Yes Yes No No No Yes No
Temporal queries(*) Yes No No No Yes No No No No No
SELECT without table No Yes Yes No(*) No No Yes No No Yes
Parallel queries(*) Yes No(*) Yes No Yes No No No No No
Aggregates for strings Yes(*) Yes No Yes No No Yes Yes No Yes
Tuple comparison Yes Yes No Yes Yes No (Yes)(*) Yes No No
Tuple updates Yes Yes No No Yes No Yes Yes No No
UPDATE with a join No Yes Yes Yes No No No No No No
ANSI date literals(*) Yes Yes No Yes Yes Yes Yes Yes No No
Query variables(*) No No Yes Yes No No No No No No
Regular Expressions Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
Comparison based on RegEx(*) Yes Yes No Yes No Yes Yes Yes No No
Substring(*) Yes Yes No No No No No Yes No No
Replace(*) Yes Yes No No(*) No No Yes No No No
Constraints Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
Deferred constraints(*) Yes Yes No No No No No No Yes(*) Yes
Check constraints Yes Yes Yes No Yes Yes Yes Yes Yes Yes
Check constraints with sub-query No No No No No Yes No No No No
Check constraints using custom functions(*) Yes Yes Yes No Yes Yes No No No No
Exclusion constraints(*) No Yes No No No No No No No No
Statement based constraint evaluation Yes Yes Yes No Yes No Yes Yes Yes Yes
ON DELETE CASCADE(*) Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
ON UPDATE CASCADE(*) No Yes Yes Yes Yes Yes Yes Yes No Yes
Indexing Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
Partial index(*) Yes(*) Yes Yes No No No No No No Yes
Descending Index(*) Yes Yes Yes No Yes Yes Yes Yes No Yes
Index on expression(*) Yes Yes (No)(*) No Yes Yes No No No No
Index using a custom function(*) Yes Yes No No Yes No No No No No
Index include columns(*) No No Yes No Yes No No No No No
Clustered index(*) Yes(*) No Yes Yes Yes No No No No No
Duplicate NULL values in unique index(*) No Yes No Yes(*) No No Yes Yes No Yes
DML Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
Writeable CTEs(*) No Yes(*) Yes(*) No No No No No No No
Multi-row INSERTs(*) No Yes Yes Yes Yes No Yes Yes Yes Yes
TRUNCATE table with FK(*) Yes(*) Yes No No No No No No No No
Read consistency during DML operations(*) Yes Yes Yes No Yes Yes Yes Yes Yes Yes
MERGE support(*) Yes (No)(*) Yes Yes(*) Yes Yes Yes Yes Yes(*) No
SELECT .. FOR UPDATE NOWAIT(*) Yes Yes Yes No No No No No No No
RETURNING clause No(*) Yes Yes No No Yes No No No No
Parallel DML Yes No No No No No No No No No
Data Types Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
User defined datatypes(*) No Yes No(*) No Yes No No Yes No No
Domains(*) No Yes (Yes)(*) No No Yes Yes Yes No No
Distinct types(*) No No No No Yes No No No No No
Arrays No Yes No No No Yes Yes Yes No No
Enums(*) No Yes No Yes No No No No No No
IP address No Yes No No No No No No No No
BOOLEAN(*) No(*) Yes No(*) No(*) Yes No Yes Yes Yes No
Interval Yes Yes No No Yes No No Yes No No
TIME(*) No Yes Yes Yes Yes Yes Yes Yes No No
DATE(*) No(*) Yes Yes Yes Yes Yes Yes Yes Yes No
TIMESTAMP(*) Yes Yes Yes(*) Yes(*) Yes Yes Yes Yes Yes No
Range types(*) (No)(*) Yes No No No No No No No No
DDL Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
Transactional DDL(*) No Yes Yes No Yes Yes No No No Yes
Computed columns(*) Yes No Yes No Yes Yes Yes Yes No No
Functions as column default(*) Yes Yes Yes No No No Yes No No Yes
Sequences Yes Yes Yes No Yes Yes Yes Yes Yes No
Auto increment columns(*) Yes(*) Yes Yes Yes Yes No Yes Yes Yes Yes
Synonms Yes No Yes No Yes No No No Yes No
Non-blocking index creation(*) Yes Yes Yes Yes Yes No No No No No
Partitioning Yes Yes(*) Yes Yes Yes No No No No No
Cascading DROP(*) Yes Yes No No(*) Yes No Yes Yes No No
DDL Triggers(*) Yes Yes Yes No No No No No No No
TRUNCATE Trigger(*) No Yes No No No No No No No No
Custom name for PK constraint(*) Yes Yes Yes No Yes Yes Yes Yes Yes Yes
ALTER a table used in a view(*) Yes No(*) Yes Yes Yes Yes Yes Yes Yes Yes
Add table column at specific position(*) No No No Yes No No Yes Yes No No
Programming Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
Stored procedures(*) Yes Yes(*) Yes Yes Yes Yes(*) No Yes No(*) No
Table functions(*) Yes Yes Yes No Yes Yes No Yes No No
Custom aggregates(*) Yes Yes No(*) No No No No No No No
Function overloading(*) Yes(*) Yes No No Yes No No No No No
User defined operators(*) No(*) Yes No No No No No No No No
Statement level triggers(*) Yes Yes Yes No Yes No (No)(*) Yes Yes No
Row level triggers(*) Yes Yes No Yes Yes Yes (No)(*) Yes Yes Yes
Before triggers(*) Yes Yes (No)(*) Yes Yes Yes (No)(*) Yes Yes Yes
Dynamic SQL in functions(*) Yes Yes No(*) No No No No No No No
Dynamic SQL in triggers(*) Yes Yes No No No No No No No No
Delete triggers fired by cascading deletes(*) Yes Yes Yes No Yes Yes No Yes Yes No
Built-in scheduler Yes No Yes Yes Yes No No No No No
Views Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
Updateable Views Yes Yes Yes Yes Yes Yes No Yes No No
WITH CHECK OPTION(*) No Yes(*) Yes Yes Yes Yes No No No No
Triggers on views Yes Yes Yes No Yes Yes No Yes No Yes
Views with derived tables(*) Yes Yes Yes No Yes Yes Yes Yes Yes Yes
JOINs and Operators Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
CROSS JOIN Yes Yes Yes No Yes Yes Yes Yes Yes Yes
FULL OUTER JOIN Yes Yes Yes No Yes Yes No Yes Yes No
LATERAL JOIN No Yes Yes(*) No Yes No No Yes No No
JOIN … USING (…)(*) Yes Yes No Yes No Yes No Yes Yes Yes
INTERSECT Yes Yes Yes No Yes No Yes Yes Yes Yes
EXCEPT Yes(*) Yes Yes No Yes No Yes Yes Yes Yes
ORDER BY … NULLS LAST Yes Yes No No Yes Yes Yes Yes Yes No
IS DISTINCT FROM No Yes No (No)(*) No Yes No Yes No No
BETWEEN SYMMETRIC No Yes No No No No No Yes No No
OVERLAPS(*) Yes Yes No No No No No Yes No No(*)
Other Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
Catalogs (“databases”) No Yes Yes Yes No Yes Yes Yes No Yes
Schemas Yes Yes Yes No Yes No Yes Yes Yes No
INFORMATION_SCHEMA(*) No Yes Yes Yes No No Yes Yes No No
NoSQL Features Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
XML Support(*) Yes Yes Yes Yes Yes No No No No No
XPath(*) Yes Yes Yes Yes Yes No No No No No
XQuery No No Yes No Yes No No No No No
JSON(*) Yes(*) Yes No No(*) No No No No No No
Key/Value storage No Yes No No(*) No No No(*) No No No
Administration Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
User groups / Roles Yes Yes Yes No Yes Yes Yes Yes Yes No
Point in time recovery Yes Yes Yes Yes Yes No No No No No
High Availability Oracle Postgres SQL Server MySQL IBM DB2 Firebird H2 HSQLDB Derby SQLite
Multi-master replication No No(*) Yes(*) Yes No No No No No No
Replication and standby servers Yes Yes Yes(*) Yes Yes No No No No No

  • Ask Question
    • Tags: , , , , , , , , ,