A SIMPLIFIED BNF GRAMMAR

For purposes of reference, it seems appropriate to close this chapter, and the main part of this book, with a simplified BNF grammar for SQL table expressions and SQL boolean expressions.[172] The grammar is deliberately somewhat conservative, in that it fails to define as valid certain expressions that are so, according to the SQL standard. (However, I don’t believe it defines as valid any expressions that aren’t so according to that standard.) To be more specific, constructs that I’ve previously advised you not to use—including in particular everything to do with nulls and 3VL—are deliberately omitted; so too are certain somewhat esoteric features (e.g., recursive queries). Also, for reasons explained in Chapter 1, almost all of the syntactic categories in what follows have names that differ from their counterparts in the standard. The following simplifying abbreviations are used:

exp                  for          expression
spec               for          specification

All syntactic categories of the form <... name> are assumed to be <identifier>s and are defined no further here. The category <scalar exp> is also left undefined—though it might help to recall in particular that:

  • A scalar subquery is a legal scalar expression.

  • Most “row expressions” that occur in practice are actually scalar expressions.

  • Boolean expressions are scalar expressions too.

Table Expressions

As you can see, the grammar in this subsection begins with a production for <with exp>, a construct not mentioned (as such) in the body of the book. I introduce this syntactic category partly in order to capture the fact that join expressions can’t appear without being nested inside some other table expression—but it does mean that the construct referred to throughout earlier parts of the book as a table expression doesn’t directly correspond to anything defined in the grammar! (I mean, there’s no production for a syntactic category called <table exp>.) I apologize if you find this state of affairs confusing, but it’s the kind of thing that always happens when you try to define a grammar for a language that violates orthogonality.

     <with exp>
         ::=   [ <with spec> ] <nonjoin exp>

     <with spec>
         ::=   WITH <name intro commalist>

     <name intro>
         ::=   <table name> AS <nonjoin exp>

     <nonjoin exp>
         ::=   <nonjoin term>
             | <nonjoin exp> UNION [ DISTINCT ]
                                   [ CORRESPONDING ] <nonjoin term>
             | <nonjoin exp> EXCEPT [ DISTINCT ]
                                    [ CORRESPONDING ] <nonjoin term>

     <nonjoin term>
         ::=   <nonjoin primary>
             | <nonjoin term> INTERSECT [ DISTINCT ]
                            [ CORRESPONDING ] <nonjoin primary>

     <nonjoin primary>
         ::=   TABLE <table name>
             | <table selector>
             | <select exp>
             | ( <nonjoin exp> )

     <table selector>
         ::=   VALUES <row exp commalist>

     <row exp>
         ::=   <scalar exp>
             | <row selector>
             | <row subquery>

     <row selector>
         ::=   ( <scalar exp commalist> )

     <row subquery>
         ::=   <subquery>

     <subquery>
         ::=   ( <nonjoin exp> )

     <select exp>
         ::=   SELECT [ DISTINCT ] [ * | <select item commalist> ]
                  FROM <table spec commalist>
                   [ WHERE <boolean exp> ]
                     [ GROUP BY <column name commalist> ]
                         [ HAVING <boolean exp> ]

     <select item>
         ::=   <scalar exp> [ AS <column name> ]
             | <range variable name>.*

     <table spec>
         ::=   <table name> [ AS <range variable name> ]
             | [ LATERAL ] <table subquery> AS <range variable name>
             | <join exp>
             | ( <join exp> )

     <table subquery>
         ::=   <subquery>

     <join exp>
         ::=   <table spec> CROSS JOIN <table spec>
             | <table spec> NATURAL JOIN <table spec>
             | <table spec> JOIN <table spec> ON <boolean exp>
             | <table spec> JOIN <table spec>
                            USING ( <column name commalist> )

Boolean Expressions

Note that the grammar in this subsection agrees with the SQL standard in giving AND higher precedence than OR; thus, the boolean expression (e.g.) p AND q OR r is understood as (p AND q) OR r, not as p AND (q OR r). But it’s probably better always to specify parentheses explicitly in such expressions.

     <boolean exp>
         ::=   <boolean term>
             | <boolean exp> OR <boolean term>
     <boolean term>
         ::=   <boolean factor>
             | <boolean term> AND <boolean factor>

     <boolean factor>
         ::=   [ NOT ] <boolean primary>

     <boolean primary>
         ::=   <boolean literal>
             | <boolean variable name>
             | <boolean column name>
             | <condition>
             | ( <boolean exp> )

     <boolean literal>
         ::=   TRUE | FALSE

     <condition>
         ::=   <simple comparison exp>
             | <between exp>
             | <like exp>
             | <in exp>
             | <match exp>
             | <all or any exp>
             | <exists exp>
             | <unique exp>

     <simple comparison exp>
         ::=   <row exp> <simple comp op> <row exp>

     <simple comp op>
         ::=   = | < | <= | > | >= | <>

     <between exp>
         ::=   <row exp> [ NOT ] BETWEEN <row exp> AND <row exp>

     <like exp>
         ::=   <scalar exp> [ NOT ] LIKE <scalar exp> [ ESCAPE <scalar exp> ]

The <scalar exp>s must denote character strings. For ESCAPE, that string must be of length one.

     <in exp>
         ::=   <row exp> [ NOT ] IN <table subquery>
             | <scalar exp> [ NOT ] IN ( <scalar exp commalist> )

     <match exp>
         ::=   <row exp> MATCH [ UNIQUE ] <table subquery>

     <all or any exp>
         ::=   <row exp> <scalar comp op> <all or any> <table subquery>

     <all or any>
         ::=   ALL | ANY | SOME

     <exists exp>
         ::=   EXISTS <table subquery>
     <unique exp>
         ::=   UNIQUE <table subquery>

[172] Appendix D gives a BNF grammar for relational expressions (and assignments) in Tutorial D.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.146.107.55