SQL的特性,各种数据库的支持

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

Feature Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
Queries                      
Window functions Yes Yes(*) Yes(*) Yes Yes(*) Yes(*) Yes(*) No No No Yes(*)
Common Table Expressions Yes Yes Yes Yes Yes(*) Yes(*) Yes No Yes No Yes(*)
(*) Yes Yes No No Yes(*) No Yes No Yes No Yes
Recursive Queries Yes Yes Yes Yes Yes(*) Yes Yes (Yes)(*) Yes No Yes(*)
Row constructor(*) No Yes Yes(*) Yes No No No No Yes Yes Yes
Filtered aggregates(*) No Yes(*) No No No No No No Yes No Yes(*)
PIVOT Support Yes No(*) Yes No No No No No No No No
GROUP BY .. ROLLUP Yes Yes(*) Yes Yes Yes Yes No No No Yes No
GROUP BY .. GROUPING SETS(*) Yes Yes(*) Yes Yes No No No No No No No
Temporal queries(*) Yes No Yes(*) Yes No Yes(*) No No No No No
SELECT without a FROM clause No Yes Yes No (Yes)(*) (Yes)(*) No Yes Yes(*) No Yes
(*) Yes Yes(*) Yes Yes No No No No No No No
Aggregates for strings Yes(*) Yes Yes(*) Yes Yes Yes Yes Yes Yes No Yes
Tuple comparison (Yes)(*) Yes No Yes Yes(*) Yes(*) No (Yes)(*) Yes No (Yes)(*)
Tuple updates Yes Yes(*) No Yes No No No Yes Yes No Yes(*)
UPDATE with a join No Yes Yes No Yes Yes No No No No No
ANSI date literals(*) Yes Yes No Yes Yes Yes Yes Yes Yes No No
(*) No No Yes No Yes Yes No Yes No No No
UNNEST(*) No Yes No Yes No No No No Yes No No
Regular Expressions Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
(*) Yes Yes No No Yes Yes Yes Yes Yes No No
(*) Yes Yes No No Yes(*) Yes(*) Yes(*) No Yes No No
(*) Yes Yes No No Yes(*) Yes(*) No Yes Yes(*) No No
Constraints Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
Deferred constraints(*) Yes Yes No No No No No No No Yes(*) Yes
Check constraints Yes Yes Yes Yes No Yes(*) Yes Yes Yes Yes Yes
Check constraints with sub-query No No No No No No Yes No No No No
Check constraints using custom functions(*) No Yes Yes Yes No No Yes No No No No(*)
Exclusion constraints(*) No Yes No Yes(*) No No No No No No No
Statement based constraint evaluation Yes Yes Yes Yes No No No Yes Yes Yes Yes
(*) Yes Yes (Yes)(*) Yes Yes Yes Yes Yes Yes Yes Yes
(*) No Yes (Yes)(*) No Yes Yes Yes Yes Yes No Yes
Indexing Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
Partial index(*) Yes(*) Yes (Yes)(*) No No No No No No No Yes
(*) Yes Yes Yes Yes Yes(*) No (Yes)(*) Yes Yes No Yes
Index on expression(*) Yes Yes (No)(*) (Yes)(*) (No)(*) (No)(*) (Yes)(*) No No No Yes(*)
Index using a custom function(*) Yes Yes No Yes No No No No No No No(*)
(*) No Yes(*) Yes Yes No No No No No No No
(*) Yes(*) No Yes Yes Yes Yes No No No No Yes
Duplicate NULL values in unique index(*) No(*) Yes No No Yes(*) Yes(*) No Yes Yes No Yes
DML Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
Writeable CTEs(*) No Yes(*) Yes(*) No No No No No No No No
(*) No Yes Yes Yes Yes Yes No Yes Yes Yes Yes
(*) Yes(*) Yes No No No No No No No No No
Read consistency during DML operations(*) Yes Yes Yes Yes No Yes(*) Yes Yes Yes Yes Yes
Use target table in sub-queries(*) Yes Yes Yes Yes No No Yes Yes Yes Yes No(*)
(*) Yes Yes(*) Yes Yes Yes(*) Yes(*) Yes Yes Yes Yes(*) No
(*) Yes Yes No(*) No Yes(*) No No No No No No
RETURNING clause as a result set No Yes Yes No No No Yes No No No No
(*) Yes No No No No No No No No No No
Data Types(*) Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
User defined datatypes(*) Yes Yes No(*) Yes No No No No Yes No No
Domains(*) No Yes (Yes)(*) No No No Yes Yes Yes No No
Distinct types(*) No No No Yes No No No No No No No
Arrays No Yes No No No No (Yes)(*) Yes Yes No No
(*) No Yes No No Yes Yes No No No No No
IP address No Yes No No No No No No No No No
(*) No(*) Yes No(*) No(*) No(*) No(*) Yes(*) Yes Yes Yes No
Interval Yes Yes No No No No No No Yes No No
(*) No Yes Yes Yes Yes Yes Yes Yes Yes No No
(*) No(*) Yes Yes Yes Yes Yes Yes Yes Yes Yes No
(*) Yes Yes Yes(*) Yes Yes(*) Yes(*) Yes Yes Yes Yes No
(*) Yes Yes Yes(*) No No No No Yes Yes No No
Range types(*) (No)(*) Yes No No No No No No No No No
DDL Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
Transactional DDL(*) No Yes Yes Yes No No Yes No No No Yes
(*) Yes No(*) Yes Yes Yes(*) Yes(*) Yes Yes Yes No No
(*) (Yes)(*) Yes Yes No No Yes(*) Yes(*) Yes Yes(*) No (Yes)(*)
Sequences Yes Yes Yes Yes No Yes(*) Yes Yes Yes Yes No
(*) Yes(*) Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
Synonyms Yes No Yes Yes No No No No Yes(*) Yes No
(*) Yes Yes Yes Yes No No No No No No No
Partitioning Yes (Yes)(*) Yes Yes Yes Yes No No No No No
Cascading DROP(*) Yes Yes No Yes No(*) No(*) No Yes Yes No No
(*) Yes Yes Yes No No No Yes(*) No No No No
(*) (No)(*) Yes No No No No No No No No No
(*) Yes Yes Yes Yes No No Yes Yes Yes Yes Yes
(*) Yes No(*) Yes Yes Yes Yes Yes Yes Yes Yes Yes
(*) No No No No Yes Yes Yes Yes Yes No No
(*) Yes Yes Yes(*) Yes(*) No No No No No No No
(*) Yes No Yes No No No No No No No No
(*) Yes No Yes Yes No No No No No No No
Temporary Tables Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
(*) Yes No No Yes No No Yes No Yes No No
(*) No No Yes No No No No Yes No No No
(*) No Yes Yes No Yes Yes No Yes Yes No Yes
Use a temporary table twice in a single query Yes Yes Yes Yes No No Yes Yes Yes No(*) Yes
Programming Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
(*) Yes Yes(*) Yes Yes Yes Yes Yes No Yes No(*) No(*)
Table functions(*) Yes Yes Yes Yes No No Yes No Yes No No(*)
(*) Yes Yes No(*) No No No No No Yes No No
(*) Yes(*) Yes No Yes No No No No Yes No No
(*) No(*) Yes No No No No No No No No No
(*) Yes Yes Yes Yes No No No No(*) Yes Yes No
(*) Yes Yes No Yes Yes Yes Yes No(*) Yes Yes Yes
(*) Yes Yes Yes No No No Yes No No No No
(*) Yes Yes (No)(*) Yes Yes Yes Yes No(*) Yes Yes Yes
(*) Yes Yes No(*) Yes No No Yes No No No No
(*) Yes Yes Yes No No No Yes No No No No
(*) Yes Yes Yes Yes No No Yes No Yes Yes Yes(*)
Built-in scheduler Yes No Yes Yes Yes Yes No No No No No
Views Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
Updateable Views Yes Yes Yes Yes Yes Yes Yes No Yes No No
(*) Yes Yes Yes Yes Yes Yes Yes No Yes No No
Triggers on views Yes Yes Yes Yes No No Yes No Yes No Yes
Views with derived tables(*) Yes Yes Yes Yes No No Yes Yes Yes Yes Yes
JOINs and Operators Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
CROSS JOIN Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
FULL OUTER JOIN Yes Yes Yes Yes No No Yes No Yes Yes No
LATERAL JOIN Yes(*) Yes (Yes)(*) Yes No No No No Yes No No
JOIN ... USING (...)(*) Yes Yes No No Yes Yes Yes No Yes Yes Yes
JOINs using tuple comparison(*) Yes Yes No Yes Yes Yes No Yes Yes No No
INTERSECT (Yes)(*) Yes (Yes)(*) Yes No Yes(*) No (Yes)(*) Yes Yes (Yes)(*)
EXCEPT (Yes)(*) Yes (Yes)(*) Yes No Yes(*) No (Yes)(*) Yes Yes (Yes)(*)
ORDER BY ... NULLS LAST Yes Yes No Yes No No Yes Yes Yes Yes No
IS DISTINCT FROM No Yes No (Yes)(*) Yes(*) Yes(*) Yes No Yes No No
BETWEEN SYMMETRIC No Yes No No No No No No Yes No No
OVERLAPS(*) (Yes)(*) Yes No Yes(*) No No No No Yes No No(*)
Other Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
(Yes)(*) (Yes)(*) Yes No Yes Yes (Yes)(*) Yes Yes No Yes
Schemas Yes Yes Yes Yes No No No Yes Yes Yes No
INFORMATION_SCHEMA(*) No Yes Yes No Yes Yes No Yes Yes No No
NoSQL Features Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
(*) Yes Yes Yes Yes Yes Yes No No No No No
(*) Yes Yes Yes Yes Yes Yes No No No No No
XQuery Yes No Yes Yes No No No No No No No
(*) Yes(*) Yes Yes(*) (Yes)(*) Yes(*) Yes(*) No No No No(*) Yes(*)
(*) Yes Yes(*) (Yes)(*) Yes (No)(*) No(*) No No No No (Yes)(*)
Key/Value storage No Yes No No No(*) No No No(*) No No No
Security Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite
User groups / Roles Yes Yes Yes Yes Yes(*) Yes(*) Yes Yes Yes Yes No
(*) Yes Yes(*) Yes Yes No No No No No No No
(*) Yes Yes Yes Yes Yes Yes Yes No Yes No No