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

转载于:https://www.cnblogs.com/liuwd/p/10904387.html