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(*) |
CTE in a sub-query(*) | 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 |
Parallel queries(*) | 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 |
Query variables(*) | 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 |
Comparison based on RegEx(*) | Yes | Yes | No | No | Yes | Yes | Yes | Yes | Yes | No | No |
Substring(*) | Yes | Yes | No | No | Yes(*) | Yes(*) | Yes(*) | No | Yes | No | No |
Replace(*) | 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 |
ON DELETE CASCADE(*) | Yes | Yes | (Yes)(*) | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
ON UPDATE CASCADE(*) | 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 |
Descending Index(*) | 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(*) |
Index include columns(*) | No | Yes(*) | Yes | Yes | No | No | No | No | No | No | No |
Clustered index(*) | 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 |
Multi-row INSERTs(*) | No | Yes | Yes | Yes | Yes | Yes | No | Yes | Yes | Yes | Yes |
TRUNCATE table with FK(*) | 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(*) |
MERGE support(*) | Yes | Yes(*) | Yes | Yes | Yes(*) | Yes(*) | Yes | Yes | Yes | Yes(*) | No |
SELECT .. FOR UPDATE NOWAIT(*) | 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 |
Parallel DML(*) | 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 |
Enums(*) | No | Yes | No | No | Yes | Yes | No | No | No | No | No |
IP address | No | Yes | No | No | No | No | No | No | No | No | No |
BOOLEAN(*) | No(*) | Yes | No(*) | No(*) | No(*) | No(*) | Yes(*) | Yes | Yes | Yes | No |
Interval | Yes | Yes | No | No | No | No | No | No | Yes | No | No |
TIME(*) | No | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | No | No |
DATE(*) | No(*) | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | No |
TIMESTAMP(*) | Yes | Yes | Yes(*) | Yes | Yes(*) | Yes(*) | Yes | Yes | Yes | Yes | No |
TIME ZONE Support(*) | 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 |
Computed columns(*) | Yes | No(*) | Yes | Yes | Yes(*) | Yes(*) | Yes | Yes | Yes | No | No |
Functions as column default(*) | (Yes)(*) | Yes | Yes | No | No | Yes(*) | Yes(*) | Yes | Yes(*) | No | (Yes)(*) |
Sequences | Yes | Yes | Yes | Yes | No | Yes(*) | Yes | Yes | Yes | Yes | No |
Auto increment columns(*) | Yes(*) | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Synonyms | Yes | No | Yes | Yes | No | No | No | No | Yes(*) | Yes | No |
Non-blocking index creation(*) | 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 |
DDL Triggers(*) | Yes | Yes | Yes | No | No | No | Yes(*) | No | No | No | No |
TRUNCATE Trigger(*) | (No)(*) | Yes | No | No | No | No | No | No | No | No | No |
Custom name for PK constraint(*) | Yes | Yes | Yes | Yes | No | No | Yes | Yes | Yes | Yes | Yes |
ALTER a table used in a view(*) | Yes | No(*) | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Add table column at specific position(*) | No | No | No | No | Yes | Yes | Yes | Yes | Yes | No | No |
Materialized views(*) | Yes | Yes | Yes(*) | Yes(*) | No | No | No | No | No | No | No |
MVIEW with query rewrite(*) | Yes | No | Yes | No | No | No | No | No | No | No | No |
Automatically updated MVIEWS(*) | 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 |
Permanent global temporary tables(*) | Yes | No | No | Yes | No | No | Yes | No | Yes | No | No |
Global temporary tables(*) | No | No | Yes | No | No | No | No | Yes | No | No | No |
Session local temporary tables(*) | 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 |
Stored procedures(*) | Yes | Yes(*) | Yes | Yes | Yes | Yes | Yes | No | Yes | No(*) | No(*) |
Table functions(*) | Yes | Yes | Yes | Yes | No | No | Yes | No | Yes | No | No(*) |
Custom aggregates(*) | Yes | Yes | No(*) | No | No | No | No | No | Yes | No | No |
Function overloading(*) | Yes(*) | Yes | No | Yes | No | No | No | No | Yes | No | No |
User defined operators(*) | No(*) | Yes | No | No | No | No | No | No | No | No | No |
Statement level triggers(*) | Yes | Yes | Yes | Yes | No | No | No | No(*) | Yes | Yes | No |
Row level triggers(*) | Yes | Yes | No | Yes | Yes | Yes | Yes | No(*) | Yes | Yes | Yes |
RETURNING clause in a programming language(*) | Yes | Yes | Yes | No | No | No | Yes | No | No | No | No |
Before triggers(*) | Yes | Yes | (No)(*) | Yes | Yes | Yes | Yes | No(*) | Yes | Yes | Yes |
Dynamic SQL in functions(*) | Yes | Yes | No(*) | Yes | No | No | Yes | No | No | No | No |
Dynamic SQL in triggers(*) | Yes | Yes | Yes | No | No | No | Yes | No | No | No | No |
Delete triggers fired by cascading deletes(*) | 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 |
WITH CHECK OPTION(*) | 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 |
Catalogs ("databases") | (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 |
XML Support(*) | Yes | Yes | Yes | Yes | Yes | Yes | No | No | No | No | No |
XPath(*) | Yes | Yes | Yes | Yes | Yes | Yes | No | No | No | No | No |
XQuery | Yes | No | Yes | Yes | No | No | No | No | No | No | No |
JSON(*) | Yes(*) | Yes | Yes(*) | (Yes)(*) | Yes(*) | Yes(*) | No | No | No | No(*) | Yes(*) |
Indexes on JSON documents(*) | 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 |
Row level security(*) | Yes | Yes(*) | Yes | Yes | No | No | No | No | No | No | No |
Grant on column level(*) | Yes | Yes | Yes | Yes | Yes | Yes | Yes | No | Yes | No | No |