SQL Syntax Reference

The ANSI/ISO SQL standard specifies the syntax of the SQL language using a formal BNF notation. Unfortunately, the standard is difficult to read and understand for several reasons. First, the standard specifies the language bottom-up rather than top-down, making it difficult to get the “big picture” of a SQL statement. Second, the standard uses unfamiliar terms (such as table-expression and predicate). Finally, the BNF in the standard is many layers deep, providing a very precise specification but masking the relatively simple structure of the SQL language.

This appendix presents a complete, simplified BNF for “standard” SQL as it is commonly implemented in the products of most DBMS vendors. Specifically:

The language described generally conforms to that required for entry-level conformance to the SQL2 standard, plus those intermediate-level and full-level conformance features that are commonly found in the major DBMS products.
The module language is omitted because it is replaced in virtually all SQL implementations by embedded SQL or a SQL API.
Components of the language are referred to by the common names generally used in DBMS vendor documentation, rather than by the technical names used in the standard.

The BNF in this appendix uses the following conventions:

SQL keywords appear in all UPPERCASE MONOSPACE characters.
Syntax elements are specified in italics.
The notation element-list indicates an element or a list of elements separated by commas.
Vertical bars (|) indicate a choice between two or more alternative syntax elements.
Square brackets ([ ]) indicate an optional syntax element enclosed within them.
Braces ({ }) indicate a choice among required syntax elements enclosed within them.

1. Data Definition Statements

These statements define the structure of a database, including its tables and views and the DBMS-specific “objects” that it contains:

CREATE TABLE table ( table-def-item-list )

DROP TABLE table [ drop-options ]

ALTER TABLE table alter-action

CREATE VIEW view [( column-list )]

AS query-spec

[ WITH CHECK OPTION ]

DROP VIEW view [ drop-options ]

CREATE db-object-type db-object-name [ db-object-spec ]

DROP db-object-type [ drop-options ]

ALTER db-object-type alter-action

GRANT { ALL PRIVILEGES | privilege-list }

ON { table | db-object-type db-object-name }

TO { PUBLIC | user-list }

[ WITH GRANT OPTION ]

REVOKE { ALL PRIVILEGES | privilege-list }

ON { table | db-object-type db-object-name }

FROM { PUBLIC | user-list }

[ WITH GRANT OPTION ]

The keywords used to specify database objects (db-object-type) depend on the specific DBMS. Typical “database objects” with associated privileges include TABLE, VIEW, SCHEMA, PROCEDURE, DOMAIN, INDEX, and the named storage areas maintained by the DBMS. The SQL syntax used to specify these objects is specific to the DBMS that supports them. The specific set of alter-actions that are supported are also DBMS-specific and object type-specific.

The language elements used in the CREATE, DROP, ALTER, GRANT, and REVOKE statements are:

2. Basic Data Manipulation Statements

The “singleton SELECT” statement retrieves a single row of data into a set of host variables (embedded SQL) or stored procedure variables:

SELECT [ ALL | DISTINCT ] { select-item-list | * }

INTO variable-list

FROM table-ref-list

[ WHERE search-condition ]

The “interactive SELECT” statement retrieves any number of rows of data in an interactive SQL session (multirow retrieval from embedded SQL or stored procedures requires cursor-based statements):

SELECT [ ALL | DISTINCT ] { select-item-list |*}

INTO host-variable-list

FROM table-ref-list [ WHERE search-condition ]

[ GROUP BY column-ref-list ]

[ HAVING search-condition ]

[ ORDER BY sort-item-list ]

These statements modify the data in the database:

INSERT INTO table [( column-list )]

{ VALUES ( insert-item-list ) | query-expr }

DELETE FROM table [ WHERE search-condition ]

UPDATE table SET set-assignment-list [ WHERE search-condition ]

3. Transaction Processing Statements

These statements signal the end of a SQL transaction:

COMMIT [ WORK ]

ROLLBACK [ WORK ]

4. Cursor-Based Statements

These programmatic SQL statements support data retrieval and positioned update of data:

DECLARE cursor [ SCROLL ] CURSOR FOR query-expr

[ ORDER BY sort-item-list ]

[ FOR { READ ONLY | UPDATE [ OF column-list ] } ]

OPEN cursor

CLOSE cursor

FETCH [ [ fetch-dir ] FROM ] cursor INTO variable-list

DELETE FROM table WHERE CURRENT OF cursor

UPDATE table SET set-assignment-list WHERE CURRENT OF cursor

The optional fetch direction (fetch-dir) is specified as the following; and row-nr can be specified as a variable or a literal.

5. Query Expressions

The SQL2 standard provides a rich set of expressions for specifying queries, from simple queries to more complex query expressions that use relational database operations to combine the results of simpler queries.

The basic query specification has the form:

SELECT [ ALL | DISTINCT ] { select-item-list| * }

FROM table-ref-list

[ WHERE search-condition ]

[ GROUP BY column-ref-list ]

[ HAVING search-condition ]

The table references (tbl-ref) in the FROM clause can be:

A simple table reference, consisting of a (possibly qualified) table name.
A derived table reference, consisting of a subquery (see the text that follows) that produces a table-valued result. Not all DBMS brands allow table-valued subqueries to appear in the FROM clause.
A joined table reference (see the text that follows) that combines data from two or more tables using relational OUTER JOIN, INNER JOIN, or other join operators. Not all DBMS brands allow join specifications to appear in the FROM clause.

Joined tables are specified according to the SQL2 standard as follows; in practice, there is wide variation in the specific types of joins supported by individual DBMS brands and the syntax used to specify various join types:

The SQL2 standard allows basic query specifications to be combined with one another using the set-oriented relational operations UNION, EXCEPT, and INTERSECT. The resulting query-expression provides the full relational set-processing power of the standard. Enclosed in parentheses, a query-expression becomes a subquery that can appear in various positions within SQL statements (for example, within certain search conditions in the WHERE clause).

Not all DBMS brands support all of these operations. A simplified form of the SQL2 syntax for the operations (without the details of operator precedence) is given by: