BNF Grammar for ISO/IEC 9075:1992 - Database Language SQL (SQL-92) ================================================================== @(#)$Id: sql-92.bnf,v 2.4 2008/02/16 23:55:41 jleffler Exp $ --p Using Appendix G of "Understanding the New SQL: A Complete Guide" by J Melton and A R Simon (Morgan Kaufmann, 1993, ISBN 0-55860-245-3) as the source of the syntax, here is (most of) the BNF syntax for SQL-92. The missing parts are the Cobol, Fortran, MUMPS, Pascal and PL/I variable definition rules. --/p --p The plain text version of this grammar is --## sql-92.bnf . --/p --hr --h2 Key SQL Statements and Fragments --/h2 --bl --li ALLOCATE CURSOR --li ALTER DOMAIN --li ALTER TABLE --li CLOSE cursor --li Column definition --li COMMIT WORK --li CONNECT --li CREATE ASSERTION --li CREATE CHARACTER SET --li CREATE COLLATION --li CREATE DOMAIN --li CREATE SCHEMA --li CREATE TABLE --li CREATE TRANSLATION --li CREATE VIEW --li Data type --li DEALLOCATE PREPARE --li DECLARE cursor --li DECLARE LOCAL TEMPORARY TABLE --li DELETE --li DESCRIBE --li DESCRIPTOR statements --li DISCONNECT --li EXECUTE --li EXECUTE IMMEDIATE --li FETCH cursor --li GET DIAGNOSTICS --li GRANT --li INSERT --li Literal --li MODULE --li OPEN cursor --li PREPARE --li Preparable statement --li REVOKE --li ROLLBACK WORK --li Search condition --li SELECT --li SET CATALOG --li SET CONNECTION --li SET CONSTRAINTS --li SET NAMES --li SET SCHEMA --li SET SESSION AUTHORIZATION --li SET TIME ZONE --li SET TRANSACTION --li UPDATE --li Value expression --/bl --hr --h2 Basic Definitions of Characters Used, Tokens, Symbols, Etc. --/h2 ::= | ::= | | ::= | ::= A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z ::= a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | p | q | r | s | t | u | v | w | x | y | z ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 ::= | | | | | | | | | | | | | | | | | | | | ::= !! space character in character set in use ::= " ::= % ::= & ::= ' ::= ( ::= ) ::= * ::= + ::= , ::= - ::= . ::= / ::= : ::= ; ::= < ::= = ::= > ::= ? ::= _ ::= | ::= | ::= [ ::= ] ::= | ::= | | | | | ::= ::= [ { | } ... ] ::= !! See the Syntax rules ::= | ::= | ::= ABSOLUTE | ACTION | ADD | ALL | ALLOCATE | ALTER | AND | ANY | ARE | AS | ASC | ASSERTION | AT | AUTHORIZATION | AVG | BEGIN | BETWEEN | BIT | BIT_LENGTH | BOTH | BY | CASCADE | CASCADED | CASE | CAST | CATALOG | CHAR | CHARACTER | CHARACTER_LENGTH | CHAR_LENGTH | CHECK | CLOSE | COALESCE | COLLATE | COLLATION | COLUMN | COMMIT | CONNECT | CONNECTION | CONSTRAINT | CONSTRAINTS | CONTINUE | CONVERT | CORRESPONDING | CREATE | CROSS | CURRENT | CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP | CURRENT_USER | CURSOR | DATE | DAY | DEALLOCATE | DEC | DECIMAL | DECLARE | DEFAULT | DEFERRABLE | DEFERRED | DELETE | DESC | DESCRIBE | DESCRIPTOR | DIAGNOSTICS | DISCONNECT | DISTINCT | DOMAIN | DOUBLE | DROP | ELSE | END | END-EXEC | ESCAPE | EXCEPT | EXCEPTION | EXEC | EXECUTE | EXISTS | EXTERNAL | EXTRACT | FALSE | FETCH | FIRST | FLOAT | FOR | FOREIGN | FOUND | FROM | FULL | GET | GLOBAL | GO | GOTO | GRANT | GROUP | HAVING | HOUR | IDENTITY | IMMEDIATE | IN | INDICATOR | INITIALLY | INNER | INPUT | INSENSITIVE | INSERT | INT | INTEGER | INTERSECT | INTERVAL | INTO | IS | ISOLATION | JOIN | KEY | LANGUAGE | LAST | LEADING | LEFT | LEVEL | LIKE | LOCAL | LOWER | MATCH | MAX | MIN | MINUTE | MODULE | MONTH | NAMES | NATIONAL | NATURAL | NCHAR | NEXT | NO | NOT | NULL | NULLIF | NUMERIC | OCTET_LENGTH | OF | ON | ONLY | OPEN | OPTION | OR | ORDER | OUTER | OUTPUT | OVERLAPS | PAD | PARTIAL | POSITION | PRECISION | PREPARE | PRESERVE | PRIMARY | PRIOR | PRIVILEGES | PROCEDURE | PUBLIC | READ | REAL | REFERENCES | RELATIVE | RESTRICT | REVOKE | RIGHT | ROLLBACK | ROWS | SCHEMA | SCROLL | SECOND | SECTION | SELECT | SESSION | SESSION_USER | SET | SIZE | SMALLINT | SOME | SPACE | SQL | SQLCODE | SQLERROR | SQLSTATE | SUBSTRING | SUM | SYSTEM_USER | TABLE | TEMPORARY | THEN | TIME | TIMESTAMP | TIMEZONE_HOUR | TIMEZONE_MINUTE | TO | TRAILING | TRANSACTION | TRANSLATE | TRANSLATION | TRIM | TRUE | UNION | UNIQUE | UNKNOWN | UPDATE | UPPER | USAGE | USER | USING | VALUE | VALUES | VARCHAR | VARYING | VIEW | WHEN | WHENEVER | WHERE | WITH | WORK | WRITE | YEAR | ZONE ::= ADA | C | CATALOG_NAME | CHARACTER_SET_CATALOG | CHARACTER_SET_NAME | CHARACTER_SET_SCHEMA | CLASS_ORIGIN | COBOL | COLLATION_CATALOG | COLLATION_NAME | COLLATION_SCHEMA | COLUMN_NAME | COMMAND_FUNCTION | COMMITTED | CONDITION_NUMBER | CONNECTION_NAME | CONSTRAINT_CATALOG | CONSTRAINT_NAME | CONSTRAINT_SCHEMA | CURSOR_NAME | DATA | DATETIME_INTERVAL_CODE | DATETIME_INTERVAL_PRECISION | DYNAMIC_FUNCTION | FORTRAN | LENGTH | MESSAGE_LENGTH | MESSAGE_OCTET_LENGTH | MESSAGE_TEXT | MORE | MUMPS | NAME | NULLABLE | NUMBER | PASCAL | PLI | REPEATABLE | RETURNED_LENGTH | RETURNED_OCTET_LENGTH | RETURNED_SQLSTATE | ROW_COUNT | SCALE | SCHEMA_NAME | SERIALIZABLE | SERVER_NAME | SUBCLASS_ORIGIN | TABLE_NAME | TYPE | UNCOMMITTED | UNNAMED --hr --h2 Literal Numbers, Strings, Dates and Times --/h2 ::= | ::= [ [ ] ] | ::= ... ::= E ::= ::= ::= [ ] ::= | ::= N [ ... ] [ { ... [ ... ] }... ] ::= | ::= !! See the Syntax rules ::= ::= { | | }... ::= [ ... ] ::= [...] ::= | ::= !! implementation defined end of line indicator ::= B [ ... ] [ { ... [ ... ] }... ] ::= 0 | 1 ::= X [ ... ] [ { ... [ ... ] }... ] ::= | A | B | C | D | E | F | a | b | c | d | e | f ::= | |
[ ON COMMIT { PRESERVE | DELETE } ROWS ] ::= MODULE ::= ::=
::=
[ {
}... ]
::= |
::= { | } [ ] [ ... ] [ ] ::= --hr --h2 Data Types --/h2 ::= [ CHARACTER SET ] | | | | | ::= CHARACTER [ ] | CHAR [ ] | CHARACTER VARYING [ ] | CHAR VARYING [ ] | VARCHAR [ ] ::= ::= NATIONAL CHARACTER [ ] | NATIONAL CHAR [ ] | NCHAR [ ] | NATIONAL CHARACTER VARYING [ ] | NATIONAL CHAR VARYING [ ] | NCHAR VARYING [ ] ::= BIT [ ] | BIT VARYING [ ] ::= | ::= NUMERIC [ [ ] ] | DECIMAL [ [ ] ] | DEC [ [ ] ] | INTEGER | INT | SMALLINT ::= ::= ::= FLOAT [ ] | REAL | DOUBLE PRECISION ::= DATE | TIME [
[ ]
::= | ::= ::= [ { }... ] ::= FULL | PARTIAL ::= [ ] | [ ] ::= ON UPDATE ::= CASCADE | SET NULL | SET DEFAULT | NO ACTION ::= ON DELETE ::= CHECK --hr --h2 Search Condition --/h2 ::= | OR ::= | AND ::= [ NOT ] ::= [ IS [ NOT ] ] ::= | ::= | | | | | | | | ::= ::= | | ::= | | ::= | | | ::= | | ::= | | ::= [ ] ::= | ::= | | | | | | ::= | ::= | ::= | | | USER | CURRENT_USER | SESSION_USER | SYSTEM_USER | VALUE ::= [ ] ::= ::= [ INDICATOR ] ::= ::= [ ] ::= ::= | | | | | | ::= !! See syntax rules ::= !! See syntax rules ::= !! See syntax rules ::= !! See syntax rules ::= !! See syntax rules ::= !! See syntax rules ::= !! See syntax rules ::= [ INDICATOR ] ::= [ ] ::=
| ::= ::= COUNT | ::= [ ] ::= AVG | MAX | MIN | SUM | COUNT ::= DISTINCT | ALL --hr --h2 Queries --/h2 ::= ::= ::= | ::= | UNION [ ALL ] [ ] | EXCEPT [ ALL ] [ ] ::= | INTERSECT [ ALL ] [ ] ::= | ::= |
| ::= SELECT [ ]
[ { ::= | ::= [ ] ::= [ AS ]
::= [ ] [ ] [ ] ::= FROM
[ {
}... ] --small --i Note that does not appear in the ISO/IEC grammar. The notation is written out longhand several times, instead. --/i --/small
::=
[ ] | | ::= [ AS ] [ ] ::= ::=
::= ::= | | ::=
CROSS JOIN
::=
[ NATURAL ] [ ] JOIN
[ ] ::= INNER | [ OUTER ] | UNION ::= LEFT | RIGHT | FULL ::= | ::= ON ::= USING ::= ::= WHERE ::= GROUP BY ::= [ { }... ] ::= [ ] ::= COLLATE ::= ::= HAVING
::= VALUES
::= [ { }... ] ::= TABLE
::= | ::= CORRESPONDING [ BY ] ::= ::= | --hr --h2 Query expression components --/h2 ::= | ::= NULLIF | COALESCE { }... ::= | ::= CASE ... [ ] END ::= ::= WHEN THEN ::= ::= | NULL ::= ::= ELSE ::= CASE ... [ ] END ::= WHEN THEN ::= CAST AS ::= | NULL ::= | ::= | | ::= POSITION IN ::= | ::= ::= [ ] ::= | ::= | ::= | | | | ::= SUBSTRING FROM [ FOR ] ::= ::= ::= { UPPER | LOWER } ::= CONVERT USING ::= ::= TRANSLATE USING ::= ::= TRIM ::= [ [ ] [ ] FROM ] ::= LEADING | TRAILING | BOTH ::= ::= ::= ::= SUBSTRING FROM [ FOR ] ::= | ::= ::= ::= | ::= EXTRACT FROM ::= |
| ::= { } ... ::= [ NOT ] LIKE [ ESCAPE ] ::= ::= ::= ::= IS [ NOT ] NULL ::=
::= | ::= ALL ::= SOME | ANY ::= EXISTS
::= UNIQUE
::= MATCH [ UNIQUE ] [ PARTIAL | FULL ]
::= OVERLAPS ::= ::= ::= TRUE | FALSE | UNKNOWN --hr --h2 More about constraints --/h2 ::= [ [ NOT ] DEFERRABLE ] | [ NOT ] DEFERRABLE [ ] ::= INITIALLY DEFERRED | INITIALLY IMMEDIATE
::= [ ]
[ ]
::= | | ::= ::= ::= FOREIGN KEY ::= --hr --h2 Module contents --/h2 ::= | | ::= DECLARE [ INSENSITIVE ] [ SCROLL ] CURSOR FOR ::= ::= [ ] [ ] ::= ORDER BY ::= [ { }... ] ::= [ ] [ ] ::= | ::= ASC | DESC ::= FOR { READ ONLY | UPDATE [ OF ] } ::= DECLARE [ INSENSITIVE ] [ SCROLL ] CURSOR FOR ::= --hr --h2 SQL Procedures --/h2 ::= PROCEDURE ::= ::= [ { }... ] ::= | ::= SQLCODE | SQLSTATE ::= | | | | | | --hr --h2 SQL Schema Definition Statements --/h2 ::= | ::= |
| | | | | | | ::= CREATE SCHEMA [ ] [ ... ] ::= | AUTHORIZATION | AUTHORIZATION ::= ::= DEFAULT CHARACTER SET ::= |
| | | | | | ::= CREATE DOMAIN [ AS ] [ ] [ ] [ ] ::= [ ] [ ]
::= CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE
[ ON COMMIT { DELETE | PRESERVE } ROWS ] ::= CREATE VIEW
[ ] AS [ WITH [ ] CHECK OPTION ] ::= ::= CASCADED | LOCAL ::= GRANT ON TO [ { }... ] [ WITH GRANT OPTION ] ::= ALL PRIVILEGES | ::= [ { }... ] ::= SELECT | DELETE | INSERT [ ] | UPDATE [ ] | REFERENCES [ ] | USAGE ::= ::= [ TABLE ]
| DOMAIN | COLLATION | CHARACTER SET | TRANSLATION ::= PUBLIC | ::= CREATE ASSERTION [ ] ::= CHECK ::= CREATE CHARACTER SET [ AS ] [ | ] ::= GET ::= | | ::= ::= COLLATION FROM ::= | ::= | | DESC | DEFAULT ::= EXTERNAL ::= | ::= ::= ::= ::= TRANSLATION [ THEN COLLATION ] ::= CREATE COLLATION FOR FROM [ ] ::= NO PAD | PAD SPACE ::= CREATE TRANSLATION FOR TO FROM ::= ::= ::= ::= | IDENTITY | ::= EXTERNAL ::= | ::= ::= ::= ::= | | | | | | | | | | ::= DROP SCHEMA ::= CASCADE | RESTRICT ::= ALTER TABLE
::= | | | | ::= ADD [ COLUMN ] ::= ALTER [ COLUMN ] ::= | ::= SET ::= DROP DEFAULT ::= DROP [ COLUMN ] ::= ADD
::= DROP CONSTRAINT ::= DROP TABLE
::= DROP VIEW
::= REVOKE [ GRANT OPTION FOR ] ON FROM [ { }... ] ::= ALTER DOMAIN ::= | | | ::= SET ::= DROP DEFAULT ::= ADD ::= DROP CONSTRAINT ::= DROP DOMAIN ::= DROP CHARACTER SET ::= DROP COLLATION ::= DROP TRANSLATION ::= DROP ASSERTION --hr --h2 SQL Data Manipulation Statements --/h2 ::= | | | ::= SELECT [ ]
WHERE CURRENT OF ::= DELETE FROM
[ WHERE ] ::= INSERT INTO
::= [ ] | DEFAULT VALUES ::= ::= UPDATE
SET WHERE CURRENT OF ::= [ { } ... ] ::= ::= ::= | | DEFAULT ::= UPDATE
SET [ WHERE ] ::= | | | ::= SET TRANSACTION [ { }... ] ::= | | ::= ISOLATION LEVEL ::= READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE ::= READ ONLY | READ WRITE ::= DIAGNOSTICS SIZE ::= ::= SET CONSTRAINTS { DEFERRED | IMMEDIATE } ::= ALL | [ { }... ] ::= COMMIT [ WORK ] ::= ROLLBACK [ WORK ] --hr --h2 Connection Management --/h2 ::= | | ::= CONNECT TO ::= [ AS ] [ USER ] | DEFAULT ::= ::= ::= ::= SET CONNECTION ::= DEFAULT | ::= DISCONNECT ::= | ALL | CURRENT --hr --h2 Session Attributes --/h2 ::= | | | | ::= SET CATALOG ::= | ::= SET SCHEMA ::= SET NAMES ::= SET SESSION AUTHORIZATION ::= SET TIME ZONE ::= | LOCAL --hr --h2 Dynamic SQL --/h2 ::= | | | | | | ::= | | | ::= ALLOCATE DESCRIPTOR [ WITH MAX ] ::= [ ] ::= GLOBAL | LOCAL ::= ::= DEALLOCATE DESCRIPTOR ::= SET DESCRIPTOR ::= | VALUE [ { }... ] ::= COUNT ::= ::= ::= ::= TYPE | LENGTH | OCTET_LENGTH | RETURNED_LENGTH | RETURNED_OCTET_LENGTH | PRECISION | SCALE | DATETIME_INTERVAL_CODE | DATETIME_INTERVAL_PRECISION | NULLABLE | INDICATOR | DATA | NAME | UNNAMED | COLLATION_CATALOG | COLLATION_SCHEMA | COLLATION_NAME | CHARACTER_SET_CATALOG | CHARACTER_SET_SCHEMA | CHARACTER_SET_NAME ::= ::= GET DESCRIPTOR ::= | VALUE [ { }... ] ::= COUNT ::= ::= | ::= ::= ::= PREPARE FROM ::= | ::= [ ] ::= ::= DEALLOCATE PREPARE ::= | ::= DESCRIBE INPUT ::= { USING | INTO } SQL DESCRIPTOR ::= DESCRIBE [ OUTPUT ] ::= EXECUTE [ ] [ ] ::= ::= | ::= { USING | INTO } [ { }... ] ::= ::= ::= EXECUTE IMMEDIATE ::= | | | | | ::= ALLOCATE [ INSENSITIVE ] [ SCROLL ] CURSOR FOR ::= [ ] ::= OPEN [ ] ::= | ::= CLOSE ::= FETCH [ [ ] FROM ] ::= DELETE FROM
WHERE CURRENT OF ::= UPDATE
SET [ { }... ] WHERE CURRENT OF ::= ::= GET DIAGNOSTICS ::= | ::= [ { }... ] ::= ::= NUMBER | MORE | COMMAND_FUNCTION | DYNAMIC_FUNCTION | ROW_COUNT ::= EXCEPTION [ { }... ] ::= ::= ::= CONDITION_NUMBER | RETURNED_SQLSTATE | CLASS_ORIGIN | SUBCLASS_ORIGIN | SERVER_NAME | CONNECTION_NAME | CONSTRATIN_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME | MESSAGE_TEXT | MESSAGE_LENGTH | MESSAGE_OCTET_LENGTH ::= | | | | | | ::= !! See the syntax rules ::= !! See the syntax rules ::= !! See the syntax rules ::= !! See the syntax rules ::= !! See the syntax rules ::= !! See the syntax rules ::= !! See the syntax rules ::= [ ] [ ... ] | ::= BEGIN DECLARE SECTION [ ] ::= EXEC SQL | SQL ::= END-EXEC | | ::= SQL NAMES ARE ::= | | | | | | --small --i Note that is written as a literal colon in the ANSI grammar. --/i --/small ::= [ { }... ] [ ] ::= | ::= SQL_STANDARD.CHAR [ CHARACTER SET [ IS ] ] 1 | SQL_STANDARD.BIT 1 | SQL_STANDARD.SMALLINT | SQL_STANDARD.INT | SQL_STANDARD.REAL | SQL_STANDARD.DOUBLE_PRECISION | SQL_STANDARD.SQLCODE_TYPE | SQL_STANDARD.SQLSTATE_TYPE | SQL_STANDARD.INDICATOR_TYPE ::= CHAR 1 | BIT 1 | SMALLINT | INT | REAL | DOUBLE_PRECISION | SQLCODE_TYPE | SQLSTATE_TYPE | INDICATOR_TYPE ::= ::= ::= [ ] [ ] ::= auto | extern | static ::= const | volatile ::= | | ::= { long | short | float | double } [ ] [ { [ ] }... ] ::= ::= char [ CHARACTER SET [ IS ] ] [ ] [ { [ ] }... ] ::= ::= | ::= VARCHAR [ CHARACTER SET [ IS ] ] [ ] [ { [ ] }... ] ::= BIT [ ] [ { [ ] }... ] ::= ...omitted... ::= ...omitted... ::= ...omitted... ::= ...omitted... ::= ...omitted... ::= END DECLARE SECTION [ ] ::= BEGIN DECLARE SECTION [ ] [ ... ] END DECLARE SECTION ::= [ ] ::= | | | | ::= WHENEVER ::= SQLERROR | NOT FOUND ::= CONTINUE | ::= { GOTO | GO TO } ::= | | ::= !! See the syntax rules ::= !! See the syntax rules ::= | | | | ::= | | | | | | ::= ::= ::= DELETE [ FROM
] WHERE CURRENT OF ::= UPDATE [
] SET WHERE CURRENT OF ::= ::= ::= ::= !! See the syntax rules ::= | | | | | ::= | | | | ::= [ ] ::= !! See the syntax rules --hr --h2 Identifying the version of SQL in use --/h2 ::= ::= ::= iso | 1 | iso 1 ::= standard | 0 | standard 0 ::= 9075 ::= ::= <1987> | <1989> | <1992> <1987> ::= 0 | edition1987 0 <1989> ::= <1989 base> <1989 package> <1989 base> ::= 1 | edition1989 1 <1989 package> ::= | ::= 0 | IntegrityNo 0 ::= 1 | IntegrityYes 1 <1992> ::= 2 | edition1992 2 ::= | | ::= 0 | Low 0 ::= 1 | Intermediate 1 ::= 2 | High 2 --hr --h2 END OF SQL-92 GRAMMAR --/h2