Assembled by Dmitry Yemanov. Last updated: February 11, 2006.

This document outlines how much Firebird conforms to the current SQL standard. Please note that the following information is not a full statement of conformance, but just information for those interested in the subject.

The formal name of the SQL standard is ISO/IEC 9075 "Database Language SQL". The version this document refers to is ISO/IEC 9075:2003, or simply SQL:2003. The versions prior to that were SQL:1999 and SQL-92. Each version supersedes the previous one, so claims of conformance to earlier versions have no official standing.

Starting with SQL:1999, the SQL standard defines a large set of individual features rather than the three levels (Entry, Intermediate and Full) declared in SQL-92. A large subset of these features represents the "Core" (mandatory) features, SQL implementation must supply in order to claim conformance. The rest of the features are purely optional.

In the following sections, we provide a list of all SQL:2003 features with an indication of whether it is supported by the given Firebird version.

Every feature consists of an unique identifier and a name. Feature identifiers containing a hyphen are subfeatures. If a particular subfeature is not supported, the main feature is listed as partly supported. Comments are provided where necessary.

Legend:

• (+) : feature is supported entirely
• (−) : feature is not supported
• (*) : feature is supported partly or it does not conform to the standard completely
• (?) : we have no clear opinion about conformance (to be reviewed)

The document is subject to periodic updates, so please feed back if you disagree with any marks of conformance.

Mandatory features

Feature ID	Feature Name	v1.5	v2.0	Comments
E011	Numeric data types	*	*
E011-01	INTEGER and SMALLINT data types (including all spellings)	+	+
E011-02	REAL, DOUBLE PRECISON, and FLOAT data types	+	+
E011-03	DECIMAL and NUMERIC data types	*	*	NUMERIC is not compliant and represents almost the same semantics as DECIMAL
E011-04	Arithmetic operators	+	+
E011-05	Numeric comparison	+	+
E011-06	Implicit casting among the numeric data types	+	+
E021	Character string types	*	*
E021-01	CHARACTER data type (including all its spellings)	+	+
E021-02	CHARACTER VARYING data type (including all its spellings)	+	+
E021-03	Character literals	+	+
E021-04	CHARACTER_LENGTH function	−	+
E021-05	OCTET_LENGTH function	−	+
E021-06	SUBSTRING function	*	+	In Firebird 1.5, SUBSTRING does not accept value expressions as its arguments
E021-07	Character concatenation	+	+
E021-08	UPPER and LOWER functions	*	+	In Firebird 1.5, LOWER is not supported
E021-09	TRIM function	−	+
E021-10	Implicit casting among the fixed-length and variable-length character string types	+	+
E021-11	POSITION function	−	−
E021-12	Character comparison	+	+
E031	Identifiers	+	+
E031-01	Delimited identifiers	+	+
E031-02	Lower case identifiers	+	+
E031-03	Trailing underscore	+	+
E051	Basic query specification	*	*
E051-01	SELECT DISTINCT	+	+
E051-02	GROUP BY clause	+	+
E051-04	GROUP BY can contain columns not in <select list>	+	+
E051-05	Select list items can be renamed	+	+
E051-06	HAVING clause	+	+
E051-07	Qualified * in select list	+	+
E051-08	Correlation names in the FROM clause	+	+	In Firebird 1.5, keyword AS is not supported
E051-09	Rename columns in the FROM clause	−	−
E061	Basic predicates and search conditions	+	+
E061-01	Comparison predicate	+	+
E061-02	BETWEEN predicate	+	+
E061-03	IN predicate with list of values	+	+
E061-04	LIKE predicate	+	+
E061-05	LIKE predicate: ESCAPE clause	+	+
E061-06	NULL predicate	+	+
E061-07	Quantified comparison predicate	+	+
E061-08	EXISTS predicate	+	+
E061-09	Subqueries in comparison predicate	+	+
E061-11	Subqueries in IN predicate	+	+
E061-12	Subqueries in quantified comparison predicate	+	+
E061-13	Correlated subqueries	+	+
E061-14	Search condition	+	+
E071	Basic query expressions	*	*
E071-01	UNION DISTINCT table operator	*	+	In Firebird 1.5, keyword DISTINCT is not supported, but the default behaviour conforms
E071-02	UNION ALL table operator	+	+
E071-03	EXCEPT DISTINCT table operator	−	−
E071-05	Columns combined via table operators need not have exactly the same data type	−	+
E071-06	Table operators in subqueries	−	+
E081	Basic Privileges	*	*
E081-01	SELECT privilege at the table level	+	+
E081-02	DELETE privilege	+	+
E081-03	INSERT privilege at the table level	+	+
E081-04	UPDATE privilege at the table level	+	+
E081-05	UPDATE privilege at the column level	+	+
E081-06	REFERENCES privilege at the table level	+	+
E081-07	REFERENCES privilege at the column level	+	+
E081-08	WITH GRANT OPTION	+	+
E081-09	USAGE privilege	−	−
E081-10	EXECUTE privilege	+	+
E091	Set functions	+	+
E091-01	AVG	+	+
E091-02	COUNT	+	+
E091-03	MAX	+	+
E091-04	MIN	+	+
E091-05	SUM	+	+
E091-06	ALL quantifier	+	+
E091-07	DISTINCT quantifier	+	+
E101	Basic data manipulation	+	+
E101-01	INSERT statement	+	+
E101-03	Searched UPDATE statement	+	+
E101-04	Searched DELETE statement	+	+
E111	Single row SELECT statement	+	+
E121	Basic cursor support	*	*
E121-01	DECLARE CURSOR	−	+
E121-02	ORDER BY columns need not be in select list	+	+
E121-03	Value expressions in ORDER BY clause	+	+
E121-04	OPEN statement	−	+
E121-06	Positioned UPDATE statement	*	+	In Firebird 1.5, only non-standard FOR SELECT cursors support positioned updates/deletes
E121-07	Positioned DELETE statement	*	+	(same as above)
E121-08	CLOSE statement	−	+
E121-10	FETCH statement: implicit NEXT	−	+
E121-17	WITH HOLD cursors	−	−
E131	Null value support (nulls in lieu of values)	−	+
E141	Basic integrity constraints	*	*
E141-01	NOT NULL constraints	+	+
E141-02	UNIQUE constraints of NOT NULL columns	+	+
E141-03	PRIMARY KEY constraints	+	+
E141-04	Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action	+	+
E141-06	CHECK constraints	+	+
E141-07	Column defaults	+	+
E141-08	NOT NULL inferred on PRIMARY KEY	−	+
E141-10	Names in a foreign key can be specified in any order	−	−
E151	Transaction support	+	+
E151-01	COMMIT statement	+	+
E151-02	ROLLBACK statement	+	+
E152	Basic SET TRANSACTION statement	*	*
E152-01	SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause	−	−
E152-02	SET TRANSACTION statement: READ ONLY and READ WRITE clauses	+	+
E153	Updatable queries with subqueries	+	+
E161	SQL comments using leading double minus	+	+
E171	SQLSTATE support	*	*	Available in PSQL via SQLCODE and GDSCODE, but uses non-standard errors encoding
E182	Module language	−	−
F031	Basic schema manipulation	*	*
F031-01	CREATE TABLE statement to create persistent base tables	+	+
F031-02	CREATE VIEW statement	+	+
F031-03	GRANT statement	+	+
F031-04	ALTER TABLE statement: ADD COLUMN clause	+	+
F031-13	DROP TABLE statement: RESTRICT clause	*	*	RESTRICT keyword is not supported, but the default behaviour conforms
F031-19	REVOKE statement: RESTRICT clause	*	*	(same as above)
F041	Basic joined table	+	+
F041-01	Inner join (but not necessarily the INNER keyword)	+	+
F041-02	INNER keyword	+	+
F041-03	LEFT OUTER JOIN	+	+
F041-04	RIGHT OUTER JOIN	+	+
F041-05	Outer joins can be nested	+	+
F041-07	The inner table in a left or right outer join can also be used in an inner join	+	+
F041-08	All comparison operators are supported (rather than just =)	+	+
F051	Basic date and time	*	*
F051-01	DATE data type (including support of DATE literal)	+	+
F051-02	TIME data type (including support of TIME literal) with fractional seconds precision of at least 0	+	+
F051-03	TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6	+	+
F051-04	Comparison predicate on DATE, TIME, and TIMESTAMP data types	+	+
F051-05	Explicit CAST between datetime types and character string types	+	+
F051-06	CURRENT_DATE	+	+
F051-07	LOCALTIME	*	*	Supported as CURRENT_TIME instead
F051-08	LOCALTIMESTAMP	*	*	Supported as CURRENT_TIMESTAMP instead
F081	UNION and EXCEPT in views	−	*	EXCEPT is not supported
F131	Grouped operations	+	+
F131-01	WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views	+	+
F131-02	Multiple tables supported in queries with grouped views	+	+
F131-03	Set functions supported in queries with grouped views	+	+
F131-04	Subqueries with GROUP BY and HAVING clauses and grouped views	+	+
F131-05	Single row SELECT with GROUP BY and HAVING clauses and grouped views	+	+
F181	Multiple module support	−	−
F201	CAST function	+	+
F221	Explicit defaults	−	−
F261	CASE expression	+	+
F261-01	Simple CASE	+	+
F261-02	Searched CASE	+	+
F261-03	NULLIF	+	+
F261-04	COALESCE	+	+
F311	Schema definition statement	−	−
F311-01	CREATE SCHEMA	−	−
F311-02	CREATE TABLE for persistent base tables	−	−
F311-03	CREATE VIEW	−	−
F311-04	CREATE VIEW: WITH CHECK OPTION	−	−
F311-05	GRANT statement	−	−
F471	Scalar subquery values	+	+
F481	Expanded NULL predicate	+	+
F812	Basic flagging	−	−
S011	Distinct data types	−	−
T321	Basic SQL-invoked routines	*	*
T321-01	User-defined functions with no overloading	*	*	Only external (non-SQL) functions are supported
T321-02	User-defined stored procedures with no overloading	*	*	Only SQL procedures are supported
T321-03	Function invocation	+	+
T321-04	CALL statement	*	*	Available as EXECUTE PROCEDURE instead
T321-05	RETURN statement	*	*	Available as EXIT in SQL procedures
T631	IN predicate with one list element	+	+

Optional features

Feature ID	Feature Name	Firebird 1.5	Firebird 2.0	Comments
B011	Embedded Ada	+	+
B012	Embedded C	+	+
B013	Embedded COBOL	+	+
B014	Embedded Fortran	+	+
B015	Embedded MUMPS	−	−
B016	Embedded Pascal	+	+
B017	Embedded PL/I	−	−
B021	Direct SQL	+	+
B031	Basic dynamic SQL	*	*	Not all standard features are supported
B032	Extended dynamic SQL	−	−
B032-01	<describe input statement>	−	−
B033	Untyped SQL-invoked function arguments	−	−
B034	Dynamic specification of cursor attributes	−	−
B041	Extensions to embedded SQL exception declarations	−	−
B051	Enhanced execution rights	−	−
B111	Module language Ada	−	−
B112	Module language C	−	−
B113	Module language COBOL	−	−
B114	Module language Fortran	−	−
B115	Module language MUMPS	−	−
B116	Module language Pascal	−	−
B117	Module language PL/I	−	−
B121	Routine language Ada	−	−
B122	Routine language C	+	+
B123	Routine language COBOL	−	−
B124	Routine language Fortran	−	−
B125	Routine language MUMPS	−	−
B126	Routine language Pascal	+	+
B127	Routine language PL/I	−	−
B128	Routine language SQL	+	+
F032	CASCADE drop behavior	−	−
F033	ALTER TABLE statement: DROP COLUMN clause	+	+
F034	Extended REVOKE statement	*	*	REVOKE CASCADE is not supported
F034-01	REVOKE statement performed by other than the owner of a schema object	+	+
F034-02	REVOKE statement: GRANT OPTION FOR clause	+	+
F034-03	REVOKE statement to revoke a privilege that the grantee has WITH GRANT OPTION	?	?
F052	Intervals and datetime arithmetic	*	*	Intervals are not supported
F053	OVERLAPS predicate	−	−
F111	Isolation levels other than SERIALIZABLE	*	*
F111-01	READ UNCOMMITTED isolation level	−	−
F111-02	READ COMMITTED isolation level	+	+
F111-03	REPEATABLE READ isolation level	+	+	Available as SNAPSHOT
F121	Basic diagnostics management	−	−
F121-01	GET DIAGNOSTICS statement	−	−
F121-02	SET TRANSACTION statement: DIAGNOSTICS SIZE clause	−	−
F171	Multiple schemas per user	−	−
F191	Referential delete actions	+	+
F222	INSERT statement: DEFAULT VALUES clause	−	−
F231	Privilege tables	−	−
F231-01	TABLE_PRIVILEGES view	−	−
F231-02	COLUMN_PRIVILEGES view	−	−
F231-03	USAGE_PRIVILEGES view	−	−
F251	Domain support	+	+
F262	Extended CASE expression	−	−
F263	Comma-separated predicates in simple CASE expression	−	−
F271	Compound character literals	−	−
F281	LIKE enhancements	+	+
F291	UNIQUE predicate	−	−
F301	CORRESPONDING in query expressions	−	−
F302	INTERSECT table operator	−	−
F302-01	INTERSECT DISTINCT table operator	−	−
F302-02	INTERSECT ALL table operator	−	−
F304	EXCEPT ALL table operator	−	−
F312	MERGE statement	−	−
F321	User authorization	*	*	Only CURRENT_USER is supported
F341	Usage tables	?	?
F361	Subprogram support	?	?
F381	Extended schema manipulation	+	+
F381-01	ALTER TABLE statement: ALTER COLUMN clause	+	+
F381-02	ALTER TABLE statement: ADD CONSTRAINT clause	+	+
F381-03	ALTER TABLE statement: DROP CONSTRAINT clause	+	+
F391	Long identifiers	*	*	Maximum identifier length is equal to 31
F392	Unicode escapes in identifiers	−	−
F393	Unicode escapes in literals	−	−
F401	Extended joined table	*	*
F401-01	NATURAL JOIN	−	−
F401-02	FULL OUTER JOIN	+	+
F401-04	CROSS JOIN	−	+
F402	Named column joins for LOBs, arrays, and multisets	−	−
F411	Time zone specification	−	−
F421	National character	*	*	Neither national string literals nor NCHAR VARYING datatype are supported
F431	Read-only scrollable cursors	−	−
F431-01	FETCH with explicit NEXT	−	−
F431-02	FETCH FIRST	−	−
F431-03	FETCH LAST	−	−
F431-04	FETCH PRIOR	−	−
F431-05	FETCH ABSOLUTE	−	−
F431-06	FETCH RELATIVE	−	−
F441	Extended set function support	+	+
F442	Mixed column references in set functions	+	+
F451	Character set definition	−	−
F461	Named character sets	+	+
F491	Constraint management	+	+
F502	Enhanced documentation tables	−	−
F502-01	SQL_SIZING_PROFILES view	−	−
F502-02	SQL_IMPLEMENTATION_INFO view	−	−
F502-03	SQL_PACKAGES view	−	−
F521	Assertions	−	−
F531	Temporary tables	−	−
F555	Enhanced seconds precision	*	*	Maximum supported precision is equal to 3, precision in datatype declarations are not supported
F561	Full value expressions	+	+
F571	Truth value tests	−	−
F591	Derived tables	−	+
F611	Indicator data types	−	−
F641	Row and table constructors	−	−
F651	Catalog name qualifiers	−	−
F661	Simple tables	−	−
F671	Subqueries in CHECK	+	+
F672	Retrospective check constraints	+	+
F691	Collation and translation	*	*	CREATE/DROP COLLATION is not supported
F692	Enhanced collation support	+	+
F693	SQL-session and client module collations	−	−
F695	Translation support	−	−
F696	Additional translation documentation	−	−
F701	Referential update actions	+	+
F711	ALTER domain	+	+
F721	Deferrable constraints	−	−
F731	INSERT column privileges	−	−
F741	Referential MATCH types	−	−
F751	View CHECK enhancements	−	−
F761	Session management	−	−
F771	Connection management	−	−
F781	Self-referencing operations	+	+
F791	Insensitive cursors	−	−
F801	Full set function	−	−
F813	Extended flagging	−	−
F821	Local table references	−	−
F831	Full cursor update	−	−
F831-01	Updateable scrollable cursors	−	−
F831-02	Updateable ordered cursors	−	−
S023	Basic structured types	−	−
S024	Enhanced structured types	−	−
S025	Final structured types	−	−
S026	Self-referencing structured types	−	−
S027	Create method by specific method name	−	−
S028	Permutable UDT options list	−	−
S041	Basic reference types	−	−
S043	Enhanced reference types	−	−
S051	Create table of type	−	−
S071	SQL paths in function and type name resolution	−	−
S081	Subtables	−	−
S091	Basic array support	*	*
S091-01	Arrays of built-in data types	*	*	Implementation is non-standard and limited to DSQL only
S091-02	Arrays of distinct types	−	−
S091-03	Array expressions	−	−
S092	Arrays of user-defined types	−	−
S094	Arrays of reference types	−	−
S095	Array constructors by query	−	−
S096	Optional array bounds	−	−
S097	Array element assignment	−	−
S111	ONLY in query expressions	−	−
S151	Type predicate	−	−
S161	Subtype treatment	−	−
S162	Subtype treatment for references	−	−
S201	SQL-invoked routines on arrays	−	−
S201-01	Array parameters	−	−
S201-02	Array as result type of functions	−	−
S202	SQL-invoked routines on multisets	−	−
S211	User-defined cast functions	−	−
S231	Structured type locators	−	−
S232	Array locators	−	−
S233	Multiset locators	−	−
S241	Transform functions	−	−
S242	Alter transform statement	−	−
S251	User-defined orderings	−	−
S261	Specific type method	−	−
S271	Basic multiset support	−	−
S272	Multisets of user-defined types	−	−
S274	Multisets of reference types	−	−
S275	Advanced multiset support	−	−
S281	Nested collection types	−	−
S291	Unique constraint on entire row	−	−
T011	Timestamp in Information Schema	−	−
T031	BOOLEAN data type	−	−
T041	Basic LOB data type support	*	*
T041-01	BLOB data type	+	+
T041-02	CLOB data type	*	*	Available as BLOB SUB_TYPE TEXT
T041-03	POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types	*	*	Partially supported
T041-04	Concatenation of LOB data types	−	−
T041-05	LOB locator: non-holdable	−	−
T042	Extended LOB data type support	−	−
T051	Row types	−	−
T052	MAX and MIN for row types	−	−
T053	Explicit aliases for all-fields reference	−	−
T061	UCS support	−	−
T071	BIGINT data type	+	+
T111	Updatable joins, unions, and columns	−	−
T121	WITH (excluding RECURSIVE) in query expression	−	−
T122	WITH (excluding RECURSIVE) in subquery	−	−
T131	Recursive query	−	−
T132	Recursive query in subquery	−	−
T141	SIMILAR predicate	−	−
T151	DISTINCT predicate	−	+
T152	DISTINCT predicate with negation	−	+
T171	LIKE clause in table definition	−	−
T172	AS subquery clause in table definition	−	−
T173	Extended LIKE clause in table definition	−	−
T174	Identity columns	−	−
T175	Generated columns	−	−
T176	Sequence generator support	+	+
T191	Referential action RESTRICT	+	+
T201	Comparable data types for referential constraints	*	*	Numerics, timestamps and strings are not considered comparable
T211	Basic trigger capability	*	*
T211-01	Triggers activated on UPDATE, INSERT, or DELETE of one base table	+	+
T211-02	BEFORE triggers	+	+
T211-03	AFTER triggers	+	+
T211-04	FOR EACH ROW triggers	+	+
T211-05	Ability to specify a search condition that shall be True before the trigger is invoked	−	−
T211-06	Support for run-time rules for the interaction of triggers and constraints	?	?
T211-07	TRIGGER privilege	−	−
T211-08	Multiple triggers for the same event are executed in the order in which they were created in the catalog	*	*	Execution order is specified by the developer
T212	Enhanced trigger capability	−	−
T231	Sensitive cursors	−	−
T241	START TRANSACTION statement	+	+	Available as SET TRANSACTION
T251	SET TRANSACTION statement: LOCAL option	−	−
T261	Chained transactions	−	−
T271	Savepoints	+	+
T272	Enhanced savepoint management	−	−
T281	SELECT privilege with column granularity	−	−
T301	Functional dependencies	+	+
T312	OVERLAY function	−	−
T322	Overloading of SQL-invoked functions and procedures	−	−
T323	Explicit security for external routines	−	−
T324	Explicit security for SQL routines	−	−
T325	Qualified SQL parameter references	−	−
T326	Table functions	−	−
T331	Basic roles	*	*	SET ROLE is not supported
T332	Extended roles	*	*	GRANTED BY CURRENT_ROLE is not supported
T351	Bracketed SQL comments (/*...*/ comments)	+	+
T431	Extended grouping capabilities	−	−
T432	Nested and concatenated GROUPING SETS	−	−
T433	Multi-argument GROUPING function	−	−
T434	GROUP BY DISTINCT	−	−
T441	ABS and MOD functions	−	−
T461	Symmetric BETWEEN predicate	−	−
T471	Result sets return value	−	−
T491	LATERAL derived table	−	−
T501	Enhanced EXISTS predicate	+	+
T511	Transaction counts	−	−
T551	Optional keywords for default syntax	+	+
T561	Holdable locators	−	−
T571	Array-returning external SQL-invoked functions	−	−
T572	Multiset-returning external SQL-invoked functions	−	−
T581	Regular expression substring function	−	−
T591	UNIQUE constraints of possibly null columns	+	+
T601	Local cursor references	−	−
T611	Elementary OLAP operations	−	−
T612	Advanced OLAP operations	−	−
T613	Sampling	−	−
T621	Enhanced numeric functions	−	−
T641	Multiple column assignment	−	−
T651	SQL-schema statements in SQL routines	−	−
T652	SQL-dynamic statements in SQL routines	+	+
T653	SQL-schema statements in external routines	−	−
T654	SQL-dynamic statements in external routines	−	−
T655	Cyclically dependent routines	+	+