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 |