SQL Building Blocks

Alias Name

alias name ::=
identifier

Atomic Expression

atomic expr ::=
constant |
column spec

Between Predicate

between pred ::=
scalar expr [ NOT ] BETWEEN
scalar expr AND
scalar expr

Boolean Expression

boolean expr ::=
  | [ NOT ] { predicate | '('
  boolean expr ')' } [ IS [ NOT ]
  truth value ]
  | boolean expr { AND | OR }
  boolean expr

Case Expression

case expr ::=
    CASE { WHEN boolean expr THEN
    scalar expr }+ [ ELSE
    scalar expr ] END
  | CASE scalar expr { WHEN
  scalar expr THEN
  scalar expr }+ [ ELSE
  scalar expr ] END

Column Definition

column def ::=
column name
data type [
column modifier ]* [ NOT NULL ]

Column Definition List

column def list ::=
column def [ ','
column def ]*

Column Modifier

column modifier ::=
    FORMAT '=' <quoted or nonquoted SAS format specification>
  | LABEL '=' string

Column Name

column name ::= identifier

Column Name List

column name list ::=
column name [ [',']
column name ]*

Column Specifications

column spec ::=
    [ CALCULATED ] column name
  | table alias'.'
  column name

Comparative Operators

comp operator ::=
 | EQ | '='
 | NE | '^=' | '~=' | '!=' | '<>'
 | LT | '<'
 | GT | '>'
 | LE | '<='
 | GE | '>='

Comparison Predicates

comparison pred ::=
scalar expr {
comp operator
scalar expr }+

Connection String

connection string ::= <user-defined
string delimited by ending/matching parenthesis>

Constant

constant ::=
  | number | missing value
  | string | date/time string
  | NULL

Contains Predicate

contains pred ::=
scalar expr { CONTAINS | '?' }
scalar expr

Data Types

data type ::=
    { CHAR[ACTER] | VARCHAR } [ '('unsigned ')' ]
  | { INT[EGER] | SMALLINT }
  | { NUM[ERIC] | DEC[IMAL] | FLOAT }
  [ '(' unsigned [ ',' unsigned ] ')' ]
  | REAL | DOUBLE PRECISION | DATE

Date / Time String

date/time string ::=
string{D|T|DT}

DBMS Options

dbms options ::= <user-defined
string delimited by ending/matching parenthesis>

Digits (Numeric)

digit ::= '0' <through> '9'

Exists Predicate

exists pred ::= EXISTS subquery

Function Arguments

function args ::=
    scalar expr [ ',' scalar expr ]* | DISTINCT scalar expr | [ DISTINCT ] '*'

Function Expressions

function expr ::=
func name '('
function args ')'

Function Name

function name ::=
identifier

Identifier

identifier ::= ['\']{
letter|<underscore>}{
letter|
digit|<underscore>}*

In Predicate

in pred ::=
    scalar expr { [ NOT ] IN | NOTIN } {
    subquery | '('
    constant [ ','
    constant ]* ')' }

Index Name

index name ::=
identifier

Insert Set List

insert set list ::= SET
set value list [ SET
set value list ]*

Insert Source

insert source ::=
  | insert values list
  | insert set list
  | query expr

Insert Value

insert value ::= VALUES '('
scalar expr [ ','
scalar expr ]* ')'

Insert Values List

insert values list ::=
insert value [
insert value ]*

Letter (Alpha)

letter ::= 'a' <through> 'z' <or> 'A' <through> 'Z'

Libref Name

libref name ::=
identifier

LIKE Predicate

like pred ::=
scalar expr [ NOT ] LIKE
scalar expr

Missing Value

missing value ::= '.'[
letter]

Null Predicate

null pred ::=
scalar expr IS [ NOT ] { NULL | MISSING }

Number

number ::=
    {unsigned|{
    digit}+'.'[{
    digit}+]|'.'{
    digit}+}[{'e'|'E'}['+'|'-']{
    digit}+]

ORDER BY Clause

order by clause ::=
    ORDER BY atomic expr [ ASC | DESC ] [ ','
    atomic expr [ ASC | DESC ] ]*

Pass-Through Specification

passthru spec ::=
   <database-specific string delimited by ending/matching parenthesis>

Predicate Types

predicate ::=
  | comparison pred
  | between pred
  | in pred
  | like pred
  | null pred
  | quantified comparison pred
  | exists pred
  | contains pred
  | soundslike pred

Quantified Comparison Predicate

quantified comparison pred ::=
    scalar expr
    comp operator { ALL | SOME | ANY }
    subquery

Query Expression

query expr ::=
    query spec
  | query expr { [ OUTER ] UNION | EXCEPT | INTERSECT } [ CORRESPONDING ] [ ALL ]
  query expr

Query Specification

query spec ::=
    SELECT [ DISTINCT | UNIQUE ] select item [ ','
    select item ]*
    FROM table ref [ ','
    table ref ]*
    [ WHERE boolean expr ]
    [ GROUP BY scalar expr [ ','
    scalar expr ]* ]
    [ HAVING boolean expr ]

Scalar Expression

scalar expr ::=
  | atomic expr
  | function expr
  | '(' scalar expr ')'
  | subquery
  | scalar expr { '+' | '-' | '*' | '/' | '||' | '**' }
scalar expr
  | { '+' | '-' } scalar expr
  | case expr

Select Item

select item ::=
    '*'
  | identifier'.*'
  | scalar expr [ [ AS ]
  identifier ] [
  column modifier ]*

Select Specification

select spec ::=
query expr [
order by clause ]

Set Value List

set value list ::=
column name '='
scalar expr [ ','
column name '='
scalar expr ]*

Soundslike Predicate

soundslike pred ::=
scalar expr '=*'
scalar expr

String

string ::=
<a single- or double-quoted
literal string -- see Strings>

Subquery

subquery ::= '('
query expr

Table Alias

table alias ::=
identifier

Table Join

table join ::=
    table ref [ INNER | { LEFT | RIGHT | FULL }
    [ OUTER ] ] JOIN table ref
    { ON boolean expr | USING '('
column name list ')' }
  | '(' table join ')'

Table Name

table name ::=
identifier

Table Options

table options ::= <user-defined
 string delimited by ending/matching bracket>

Table Reference

table ref ::=
    table spec [ [ AS ]
    identifier ]
  | subquery [ [ AS ]
  identifier ] [ '('
  column name list ')' ]
  | CONNECTION TO identifier '('
  connection string ')' [ [ AS ]
  identifier ]
  | table join

Table Specification

table spec ::=
  | table name [ '['
  table options ']' ]
  | libref name'.'
  table name [ '['
  table options ']' ]

Truth Value

truth value ::= { TRUE | YES } | { FALSE | NO }

Unsigned

unsigned ::= {
digit }+

WHERE Clause

where clause ::= WHERE
boolean expr

With Index Specification

with index spec ::= [ UNIQUE ] INDEX
index name ON '('
column name list ')'