A newer version of this documentation is available.

View Latest

SELECT Syntax

  • reference
    +
    This page enables you to drill down through the syntax of a SELECT query.
    Syntax diagram
    select-term ::= subselect | '(' select ')'
    Syntax diagram
    subselect ::= select-from | from-select
    Syntax diagram
    Syntax diagram
    Syntax diagram
    set-op ::= ( 'UNION' | 'INTERSECT' | 'EXCEPT' ) 'ALL'?
    Syntax diagram

    WITH Clause

    with-clause ::= 'WITH' alias 'AS' '(' ( select | expression ) ')'
                     ( ',' alias 'AS' '(' ( select | expression ) ')' )*
    Syntax diagram
    alias ::= identifier
    Syntax diagram

    SELECT Clause

    select-clause ::= 'SELECT' hint-comment? projection
    Syntax diagram
    projection ::= ( 'ALL' | 'DISTINCT' )?
                   ( result-expr ( ',' result-expr )* |
                   ( 'RAW' | 'ELEMENT' | 'VALUE' ) expr ( 'AS'? alias )? )
    Syntax diagram
    result-expr ::= ( ( path '.' )? '*' | expr ( 'AS'? alias )? )
    Syntax diagram
    path ::= identifier ( '[' expr ']' )* ( '.' identifier ( '[' expr ']' )* )*
    Syntax diagram

    FROM Clause

    from-clause ::= 'FROM' from-terms
    Syntax diagram
    Syntax diagram
    from-keyspace ::= keyspace-ref ( 'AS'? alias )? use-clause?
    Syntax diagram
    keyspace-ref ::= keyspace-path | keyspace-partial
    Syntax diagram
    keyspace-path ::= ( namespace ':' )? bucket ( '.' scope '.' collection )?
    Syntax diagram
    keyspace-partial ::= collection
    Syntax diagram
    namespace ::= identifier
    Syntax diagram
    bucket ::= identifier
    Syntax diagram
    scope ::= identifier
    Syntax diagram
    collection ::= identifier
    Syntax diagram
    from-subquery ::= subquery-expr 'AS'? alias
    Syntax diagram
    subquery-expr ::= '(' select ')'
    Syntax diagram
    from-generic ::= expr ( 'AS' alias )?
    Syntax diagram

    JOIN Clause

    Syntax diagram

    ANSI JOIN

    ansi-join-clause ::= ansi-join-type? 'JOIN' ansi-join-rhs ansi-join-predicate
    Syntax diagram
    ansi-join-type ::= 'INNER' | ( 'LEFT' 'OUTER'? ) | ( 'RIGHT' 'OUTER'? )
    Syntax diagram
    ansi-join-rhs ::= rhs-keyspace | rhs-subquery | rhs-generic
    Syntax diagram
    rhs-keyspace ::= keyspace-ref ( 'AS'? alias )? ansi-join-hints?
    Syntax diagram
    rhs-subquery ::= subquery-expr 'AS'? alias
    Syntax diagram
    rhs-generic ::= expr ( 'AS'? alias )?
    Syntax diagram
    ansi-join-hints ::= use-hash-hint | use-nl-hint | multiple-hints
    Syntax diagram
    use-hash-hint ::= 'USE' use-hash-term
    Syntax diagram
    use-hash-term ::= 'HASH' '(' ( 'BUILD' | 'PROBE' ) ')'
    Syntax diagram
    use-nl-hint ::= 'USE' use-nl-term
    Syntax diagram
    use-nl-term ::= 'NL'
    Syntax diagram
    Syntax diagram
    ansi-hint-terms ::= use-hash-term | use-nl-term
    Syntax diagram
    other-hint-terms ::= use-index-term | use-keys-term
    Syntax diagram
    ansi-join-predicate ::= 'ON' expr
    Syntax diagram

    Lookup JOIN

    lookup-join-clause ::= lookup-join-type? 'JOIN' lookup-join-rhs lookup-join-predicate
    Syntax diagram
    lookup-join-type ::= 'INNER' | ( 'LEFT' 'OUTER'? )
    Syntax diagram
    lookup-join-rhs ::= keyspace-ref ( 'AS'? alias )?
    Syntax diagram
    lookup-join-predicate ::= 'ON' 'PRIMARY'? 'KEYS' expr
    Syntax diagram

    Index JOIN

    index-join-clause ::= index-join-type? 'JOIN' index-join-rhs index-join-predicate
    Syntax diagram
    index-join-type ::= 'INNER' | ( 'LEFT' 'OUTER'? )
    Syntax diagram
    index-join-rhs ::= keyspace-ref ( 'AS'? alias )?
    Syntax diagram
    index-join-predicate ::= 'ON' 'PRIMARY'? 'KEY' expr 'FOR' alias
    Syntax diagram

    NEST Clause

    Syntax diagram

    ANSI NEST

    ansi-nest-clause ::= ansi-nest-type? 'NEST' ansi-nest-rhs ansi-nest-predicate
    Syntax diagram
    ansi-nest-type ::= 'INNER' | ( 'LEFT' 'OUTER'? )
    Syntax diagram
    ansi-nest-rhs ::= keyspace-ref ( 'AS'? alias )?
    Syntax diagram
    ansi-nest-predicate ::= 'ON' expr
    Syntax diagram

    Lookup NEST

    lookup-nest-clause ::= lookup-nest-type? 'NEST' lookup-nest-rhs lookup-nest-predicate
    Syntax diagram
    lookup-nest-type ::= 'INNER' | ( 'LEFT' 'OUTER'? )
    Syntax diagram
    lookup-nest-rhs ::= keyspace-ref ( 'AS'? alias )?
    Syntax diagram
    lookup-nest-predicate ::= 'ON' 'KEYS' expr
    Syntax diagram

    Index NEST

    index-nest-clause ::= index-nest-type? 'NEST' index-nest-rhs index-nest-predicate
    Syntax diagram
    index-nest-type ::= 'INNER' | ( 'LEFT' 'OUTER'? )
    Syntax diagram
    index-nest-rhs ::= keyspace-ref ( 'AS'? alias )?
    Syntax diagram
    index-nest-predicate ::= 'ON' 'KEY' expr 'FOR' alias
    Syntax diagram

    UNNEST Clause

    unnest-clause ::= unnest-type? ( 'UNNEST' | 'FLATTEN' ) expr ( 'AS'? alias )?
    Syntax diagram
    unnest-type ::= 'INNER' | ( 'LEFT' 'OUTER'? )
    Syntax diagram

    Comma-Separated Join

    comma-separated-join ::= ',' ( rhs-keyspace | rhs-subquery | rhs-generic )
    Syntax diagram

    USE Clause

    Syntax diagram
    use-keys-clause ::= 'USE' use-keys-term
    Syntax diagram
    use-keys-term ::= 'PRIMARY'? 'KEYS' expr
    Syntax diagram
    use-index-clause ::= 'USE' use-index-term
    Syntax diagram
    use-index-term ::= 'INDEX' '(' index-ref ( ',' index-ref )* ')'
    Syntax diagram
    index-ref ::= index-name? index-type?
    Syntax diagram
    index-name ::= identifier
    Syntax diagram
    index-type ::= 'USING' ( 'GSI' | 'FTS' )
    Syntax diagram

    LET Clause

    let-clause ::= 'LET' alias '=' expr ( ',' alias '=' expr )*
    Syntax diagram

    WHERE Clause

    where-clause ::= 'WHERE' cond
    Syntax diagram
    cond ::= expr
    Syntax diagram

    GROUP BY Clause

    group-by-clause ::= 'GROUP' 'BY' expr ( ',' expr )* letting-clause? having-clause? | letting-clause
    Syntax diagram
    letting-clause ::= 'LETTING' alias '=' expr ( ',' alias '=' expr )*
    Syntax diagram
    having-clause ::= 'HAVING' cond
    Syntax diagram

    WINDOW Clause

    window-clause ::= 'WINDOW' window-declaration ( ',' window-declaration )*
    Syntax diagram
    window-declaration ::= window-name 'AS' '(' window-definition ')'
    Syntax diagram
    window-name ::= identifier
    Syntax diagram
    Syntax diagram
    window-ref ::= identifier
    Syntax diagram
    window-partition-clause ::= 'PARTITION' 'BY' expr ( ',' expr )*
    Syntax diagram
    window-order-clause ::= 'ORDER' 'BY' ordering-term ( ',' ordering-term )*
    Syntax diagram
    window-frame-clause ::= ( 'ROWS' | 'RANGE' | 'GROUPS' ) window-frame-extent window-frame-exclusion?
    Syntax diagram
    window-frame-extent ::= 'UNBOUNDED' 'PRECEDING' | valexpr 'PRECEDING' | 'CURRENT' 'ROW' |
                            'BETWEEN' ( 'UNBOUNDED' 'PRECEDING' | 'CURRENT' 'ROW' |
                                         valexpr ( 'PRECEDING' | 'FOLLOWING' ) )
                                'AND' ( 'UNBOUNDED' 'FOLLOWING' | 'CURRENT' 'ROW' |
                                         valexpr ( 'PRECEDING' | 'FOLLOWING' ) )
    Syntax diagram
    window-frame-exclusion ::= 'EXCLUDE' ( 'CURRENT' 'ROW' | 'GROUP' | 'TIES' | 'NO' 'OTHERS' )
    Syntax diagram

    ORDER BY Clause

    order-by-clause ::= 'ORDER' 'BY' ordering-term ( ',' ordering-term )*
    Syntax diagram
    ordering-term ::= expr ( 'ASC' | 'DESC' )? ( 'NULLS' ( 'FIRST' | 'LAST' ) )?
    Syntax diagram

    LIMIT Clause

    limit-clause ::= 'LIMIT' expr
    Syntax diagram

    OFFSET Clause

    offset-clause ::= 'OFFSET' expr
    Syntax diagram