The following SQL statements define the Microsoft Jet SQL grammar, using the Backus-Naur Form (BNF).
This BNF follows the notation standards used by the American National Standards Institute (ANSI) in defining standard X.135-1992 (ANSI-SQL 92).
| Symbol | Meaning |
| <> | Angle brackets delimit character strings that are the names of syntactic elements. |
| ::= | Definition operator. Used in a production rule to separate the elements defined by the rule from its definition. <defined_element> ::= <definition> |
| {} | Grouping delimiter. The portion of the formula within the braces is explicitly specified. |
| [] | Optional elements delimiter. The portion of the formula within the brackets can be explicitly specified or it can be omitted. |
| | | Alternative operator. The vertical bar indicates that the portion of the formula following the bar is an alternative to the portion preceding the bar. If the vertical bar appears at a position where it is not enclosed in braces or square brackets, it specifies a complete alternative for the element defined by the production rule. If the vertical bar appears in a portion of a formula enclosed in braces or square brackets, it specifies alternatives for the contents of the innermost pair of braces or brackets. |
| ... | Repeat indicator. The ellipsis indicates that the element to which it applies in a formula can be repeated any number of times. |
| !! | English text. Defines a syntactic element without the benefit of BNF notation. |
<SQL-language-character> ::=
<SQL-alpha-numeric-character>
| <SQL-special-characters>
<SQL-alpha-numeric-character> ::=
<lower-case-letter>
| <upper-case-letter>
| <digit>
<lower-case-letter> ::= [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]
<upper-case-letter> ::= [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]
<digit> ::= [0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9]
<SQL-special-characters> ::=
<space>
| <tab>
| <newline>
| <carriage-return>
| <form-feed>
| <double-quote>
| <percent>
| <ampersand>
| <quote>
| <asterisk>
| <plus-sign>
| <minus-sign>
| <back-slash>
| <solidus>
| <colon>
| <semicolon>
| <less-than-character>
| <equals-character>
| <greater-than-character>
| <question-mark>
| <underscore>
| <number-sign>
<space> ::= !! space character
<tab> ::= !! tab character
<newline> ::= !! newline character
<carriage-return> ::= !! carriage-return character
<form-feed> ::= !! form-feed character
<double-quote> ::= "
<back-quote> ::= `
<percent> ::= %
<ampersand> ::= &
<quote> ::= '
<left-paren> ::= (
<right-paren> ::= )
<asterisk> ::= *
<plus-sign> ::= +
<comma> ::= ,
<minus sign> ::= -
<period> ::= .
<back-slash> ::= \
<solidus> ::= /
<colon> ::= :
<semicolon> ::= ;
<less-than-character> ::= <
<equals-character> ::= =
<greater-than-character> ::= >
<question-mark> ::= ?
<underscore> ::= _
<vertical-bar> ::= |
<left-bracket> ::= [
<right-bracket> ::= ]
<number-sign> ::= #
<whitespace> ::=
<space>
| <tab>
| <newline>
| <carriage-return>
| <form-feed>
<key-word> ::=
ALL | ANY | AS | ASC | AUTOINCREMENT | AVG
| BETWEEN | BINARY | BOOLEAN | BY | BYTE
| CHAR[ACTER] | COUNT | COUNTER | CURRENCY
| DATABASE | DATABASENAME | DATE | DATETIME | DELETE | DESC | DISTINCT | DISTINCTROW
| DOUBLE
| EXISTS
| FIRST | FLOAT | FROM
| GROUP
| HAVING
| IN | INNER | INSERT | INT | INTEGER | INTO
| JOIN
| LAST | LEFT | LEVEL | LIKE | LONG | LONGBINARY | LONGTEXT
| MAX | MIN
| NOT | NULL
| OLEOBJECT | ON | OPTION | ORDER | OUTER | OWNERACCESS
| PARAMETERS | PERCENT | PIVOT | PROCEDURE
| REAL | RIGHT
| SELECT | SET | SHORT | SINGLE | SMALLINT | SOME | STDEV | STDEVP | SUM
| TABLEID | TIMESTAMP | TEXT | TOP | TRANSFORM
| UPDATE
| VALUES | VAR | VARBINARY | VARCHAR | VARP
| WHERE | WITH
| YESNO
<token> ::=
<literal>
| <comparison-operator>
| <special-symbol>
| <identifier>
| <key-word>
<special-symbol> ::=
<left-paren>
| <right-paren>
| <comma>
| <period>
| <colon>
| <semicolon>
| <equals-operator>
| <asterisk>
| <left-bracket>
| <right-bracket>
<identifier> ::=
<SQL-alpha-numeric-character> [<SQL-language-character>...]
!! Note: Can include up to 64 characters.
<identifier-delimiter> ::=
<left-id-delim>
| <right-id-delim>
<left-id-delim> ::= !! Left identifier delimiter
<back-quote>
| <left-bracket>
<right-id-delim> ::= !! Right identifier delimiter
<back-quote>
| <right-bracket>
<scalar-expression> ::=
<numeric-scalar-expression>
| <character-string-literal>
<numeric-scalar-expression> ::=
<term>
| <numeric-scalar-expression> [<plus-operator> | <minus-operator>] <term>
<term> ::=
<factor>
| <term> [<multiplication-operator> | <divides-operator>] <factor>
<factor> ::= [<plus-operator> | <minus-operator>]<primary>
<primary> ::=
<atom>
| <column-reference>
| <function-reference>
| <left-paren><scalar-expression><right-paren>
<atom> ::=
<literal>
| <parameter-name>
<literal> ::=
<character-string-literal>
| <numeric-literal>
| <boolean-literal>
| <datetime-literal>
| NULL
<character-string-literal> ::= {<single-quote> | <double-quote>} <char-list> {<single-quote> | <double-quote>} !! Note that the leading and trailing delimiter must be the same. Embedded delimiters must be doubled as in Visual Basic.
<numeric-literal> ::=
<exact-numeric-literal>
| <approximate-numeric-literal>
<exact-numeric-literal> ::=
<signed-integer>[<period><unsigned-integer>]
|<period><unsigned-integer>
<approximate-numeric-literal> ::=
<exact-numeric-literal>{E|e}<signed-integer>
<signed-integer> ::=
[<plus-sign> | <minus-sign>]<unsigned-integer>
<unsigned-integer> ::= <digit> ...
<boolean-literal> ::= {TRUE | FALSE | YES | NO | ON | OFF}
<datetime-literal> ::= <number-sign><date-time-text><number-sign>
<date-time-text> ::= !! any literal that can be parsed as a valid date/time by the
expression service
<table-name> ::=
<base-table-name>
| <query-name>
| <link-name>
<base-table-name> ::= <identifier>
<query-name> ::= <identifier>
<link-name> ::= <identifier>
<qualified-table-name> ::= [[ <connect-string> <period> ] <database-name> <period>]
<table-name>
<connect-string> ::= <identifier> !!<connect-string> specifies the data source to look for the given <database-name> in. If the connect string starts with the name of a Microsoft Jet data provider followed by a semicolon, then the given database is opened using that data provider. If the data provider is missing, or if it is "ODBC", then the rest of the connect string is interpreted as an ODBC connect string. In this case, a DSN field is added to the connect string containing the <database-name>. If a DSN field had previously existed, it is replaced with this new DSN field.
<column-reference> ::= <identifier>
<column-reference-list> ::= <column-reference> [<comma> <column-reference> ...]
<constraint-name> ::= <identifier>
<index-name> ::= <identifier>
<database-name> ::= <identifier>
<manipulation-statement> ::= [<parameter-declaration> <semicolon>] <data-manipulation-language-statement> <semicolon>
!! Note: Microsoft Jet SQL data manipulation statements consist of an optional <parameter-declaration> followed by a single <data-manipulation-language-statement>. Manipulation statements define stored or temporary query objects in Microsoft Jet. Note that subqueries are not supported and similar functionality may be used by using one query as the input to another.
<parameter-declaration> ::= PARAMETERS <parameter-definition-list>
<parameter-definition-list> ::= <parameter-definition> [{<comma> <parameter-definition>} ...]
<parameter-definition> ::= <parameter-name> <parameter-datatype>
<parameter-name> ::= <identifier>
<parameter-datatype> ::=
<Jet-parameter-datatype-name>
| <ANSI-SQL-parameter-datatype-name>
<Jet-parameter-datatype-name> ::=
BOOLEAN
| BYTE <left-paren> <length> <right-paren>
| SHORT
| LONG
| CURRENCY
| SINGLE
| DOUBLE
| DATETIME
| TEXT <left-paren> <length> <right-paren>
| BINARY
| LONGTEXT <left-paren> <length> <right-paren>
| LONGBINARY
| DATABASE
| TABLEID
| OLEOBJECT
| YESNO
<ANSI-SQL-parameter-datatype-name> ::=
SMALLINT
| INT[EGER]
| REAL
| FLOAT
| CHAR[ACTER] <left-paren> <length> <right-paren>
| VARCHAR <left-paren> <length> <right-paren>
<data-definition-language-statement> ::=
<table-definition>
| <alter-table-statement>
| <drop-table-statement>
| <index-definition>
| <drop-index-statement>
<alter-table-statement> ::=
ALTER TABLE <base-table-name>
{ADD
{COLUMN <column-reference>> <parameter-datatype>[CONSTRAINT <field-constraint>]
| CONSTRAINT <table-constraint> }
| DROP {COLUMN <column-reference> | CONSTRAINT <constraint-name> }}
<index-definition> ::=
CREATE [ UNIQUE ] INDEX <index-name>
ON <base-table-name> <left-paren> <column-reference>
[ASC|DESC][{<comma> <column-reference> [ASC|DESC]} ...] <right-paren>
[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]
<table-definition> :==
CREATE TABLE <base-table-name>
<left-paren> {<column-reference> <parameter-datatype>
[CONSTRAINT <field-constraint>]}
[{<comma> <column-reference> <parameter-datatype>
[CONSTRAINT <field-constraint>]}...]]]<right-paren>
<drop-table-statement> ::=
DROP TABLE <base-table-name>
<drop-index-statement> ::=
DROP INDEX <index-name> ON <base-table-name>
<column-constraint-definition> ::= <constraint-name-definition>
<column-constraint>
<column-constraint> ::= <unique-specification> |
<references-specification>
<constraint-name-definition> ::= CONSTRAINT <constraint-name>
<table-constraint-definition> ::= <constraint-name-definition>
<table-constraint>
<table-constraint> ::=
<unique-constraint-definition>
| <referential-constraint-definition>
<unique-constraint-definition> ::=
<unique-specification> <left-paren> <column-reference-list> <right-paren>
<unique-specification> ::= UNIQUE | PRIMARY KEY
<referential-constraint-definition> ::=
FOREIGN KEY <left-paren> <referencing-columns> <right-paren>
<references-specification>
<references-specification> ::=
REFERENCES <referenced-table-and-columns>
<referencing-columns> ::= <column-reference-list>
<referenced-table-and-columns> ::= <table-name>
<left-paren> <reference-column-list> <right-paren>
<reference-column-list> ::= <column-reference-list>
<data-manipulation-language-statement> :==
<delete-statement>
| <insert-statement>
| <select-statement>
| <select-into-statement>
| <transform-statement>
| <update-statement>
<delete-statement> ::= DELETE <from-clause> [where-clause]
<insert-statement> ::=
INSERT INTO <table-name> [IN external-database] <insert-columns-and-source>
<insert-columns-and-source> ::=
{<left-paren> <column-reference-list> <right-paren>} {SELECT <column-reference-list>
FROM <table-expression>
| VALUES <left-paren><scalar-expression-list><right-paren>}
<select-statement> ::=
SELECT <set-quantifier> <select list> <table expression>
<table-expression> ::= <from-clause>
[<where-clause>]
[<group-by-clause>]
[<having-clause>]
[<order-by-clause>]
[<with-owneraccess-clause]
<select-list> ::=
<asterisk>
| <select-sublist> [{<comma> <select-sublist>}...]
<select-sublist> ::=
<column-qualifier><period><asterisk>
| <scalar-expression> [AS <column-reference>]
<from-clause> ::= FROM <table-reference-list>
<table-reference-list> ::=
<table-reference> [{<comma> <table-reference>}...] [<in-clause>]
<table-reference> ::=
{<from-table-name> [AS <correlation-name>]} | <joined-table>
<from-table-name> ::=
<table-name>
| <tableid-parameter>
<tableid-parameter> ::= <parameter> <identifier> of type TABLEID
<in-clause> ::=
IN
<filename-path> !! For local, Jet-native databases
| {<left-id-delim><filename-path><right-id-delim> <left-id-delim><database-name><right-id-delim>}
<filename-path> ::= !! as defined by operating system
<joined-table> ::=
<table-reference> [ <join-type> ] JOIN <table-reference> ON <search-condition>
<join-type> ::=
INNER
| LEFT
| RIGHT
<where-clause> ::= WHERE <search-condition>
<search-condition> ::=
<boolean-term>
| <search-condition> OR <boolean-term>
<boolean-term> ::=
<boolean-factor>
| <boolean-term> AND <boolean-factor>
<boolean-factor> ::= [ NOT ] <boolean-primary>
<boolean-primary> ::=
<predicate>
| <left-paren><search-condition><right-paren>
<predicate> ::=
<comparison-predicate>
| <between-predicate>
| <in-predicate>
| <like-predicate>
| <null-predicate>
<comparison-predicate> ::=
<scalar-expression><comparison-operator><scalar-expression>
<between-predicate> ::=
<scalar-expression> [NOT] BETWEEN <scalar-expression> AND <scalar-expression>
!! The BETWEEN predicate y BETWEEN x AND z is semantically equivalent to (x <= y AND y <= z) OR (z <= y AND y <= x).
<in-predicate> ::=
<scalar-expression> [NOT] IN <left-paren> <scalar-expression-list> <right-paren>
<scalar-expression-list> ::= <scalar-expression> [{<comma> <scalar-expression>}...]
<like-predicate> ::= <scalar-expression> [NOT] LIKE
<scalar-expression>
| <pattern>
<pattern> ::=
<left-bracket> <char-list> <right-bracket>
!! matches any single character in <char-list>
| <left-bracket> <bang> <char-list> <right-bracket>
!! matches any single character not in <char-list>
| <left-bracket> <char-range> <right-bracket>
!! matches any single character in <char-range>
| <left-bracket> <bang> <char-range> <right-bracket>
!! matches any single character not in <char-range>
<pattern-matching-character> ::=
<question-mark> !! matches any single <SQL-alphabetic-character>
| <pound-sign> !! matches any single <digit>
| <asterisk> !! matches any sequence of zero or more <SQL-alphabetic characters>
<dash> ::= -
<escape-character> ::= ^
<bang> ::= !
<char-list> ::=
<SQL-alpha-numeric-character>
| {<escape-character> <special-symbol>}
[[<SQL-alpha-numeric-character>
| {<escape-character> <special-symbol>}] ...]
<char-range> ::=
<SQL-alpha-numeric-character> <dash> <SQL-alpha-numeric-character>
!! Where the first <SQL-alpha-numeric-character> has lower ordinal value than the
!! second <SQL-alpha-numeric-character>
<null-predicate> ::= <scalar-expression> IS [NOT] NULL
<group-by-clause> ::= GROUP BY <grouping-specification-list>
<grouping-specification-list> ::= <grouping-specification>[{<comma> <grouping-specification>}...]
<grouping-specification> ::=
<column-reference>
| <scalar-expression>
<having-clause> ::= HAVING <search-condition>
<order-by-clause> ::= ORDER BY <sort-specification-list>
<sort-specification-list> ::=
<sort-specification> [{<comma> <sort-specification>}...]
<sort-specification> ::=
<scalar-expression> [ASC | DESC]
<select-into-statement> ::=
SELECT <set-quantifier> <reference-column-list>INTO <base-table-name> [in-clause]
<table-expression>
<transform-statement> ::=
TRANSFORM <aggregate-function> <select-statement> PIVOT <pivot-field>
[IN <left-paren> <scalar-expression> [{<comma> <scalar-expression>}...]<right-paren>]
<union-clause> ::=
<tabular-data> UNION [ALL] <tabular-data> [{UNION [ ALL ] <tabular-data>} ...]
<update-statement> ::=
UPDATE <table-reference-list> SET <set-clause-list> [<where-clause>]
<set-clause-list> ::= <set-clause> [{<comma> <set-clause>}...]
<set-clause> ::=
<column-reference> <equals-operator>
{<scalar-expression>
| NULL}
<aggregate-reference>::=
{Avg
| Count
| First
| Last
| Min
| Max
| StDev
| StDevP
| Sum
| Var
| VarP} <left-paren><scalar-expression><right-paren>
<comparison-operator> ::=
<equals-operator>
| <greater-than-operator>
| <less-than-operator>
| <greater-than-or-equals-to-operator>
| <less-than-or-equals-to-operator>
| <not-equals-operator>
<less-than-operator> ::= <less-than-character>
<greater-than-operator> ::= <greater-than-character>
<equals-operator> ::= <equals-character>
<greater-than-or-equals-to-operator> :== <greater-than-operator> <equals-operator>
<less-than-or-equals-to-operator> ::= <less-than-operator> <equals-operator>
<not-equals-operator> ::= <bang> <equals-operator>
<plus-operator> ::= <plus-sign>
<minus-operator> ::= <minus-sign>
<database-type> ::=
{dBASE III
| dBASE IV
| Paradox 3.x
| Paradox 4.x
| Btrieve
| FoxPro 2.5
| Excel 3.0
| Excel 4.0
| Excel 5.0
| ODBC
}<semicolon>
<pivot-field> ::= <identifier>
<set-quantifier> ::=
ALL
| DISTINCT
| {TOP <unsigned-integer> [PERCENT]}
<tabular-data> ::=
<select-statement>
| <stored-query>
| TABLE <base_table_name>