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.
    select-query ::= [ with-clause ] select
    select query
    select
    select-term ::= subselect | '(' select ')'
    select term
    subselect ::= select-from | from-select
    subselect
    select from
    from select
    set-op ::= UNION | INTERSECT | EXCEPT
    set op

    WITH Clause

    with-clause ::= WITH alias AS '(' ( select | expression ) ')'
                   [ ',' alias AS '(' ( select | expression ) ')' ]*
    with clause

    SELECT Clause

    select-clause ::= SELECT [ ALL | DISTINCT ]
                    ( result-expr [ , result-expr ]* | ( RAW | ELEMENT | VALUE ) expr [ [ AS ] alias ] )
    select clause

    FROM Clause

    from-clause ::= FROM from-term
    from clause
    from-keyspace | from-subquery | from-generic | join-clause | nest-clause | unnest-clause
    from-keyspace ::= keyspace-ref [ [ AS ] alias ] [ use-clause ]
    from keyspace
    keyspace-ref ::= keyspace-path | keyspace-partial
    keyspace-path | keyspace-partial
    keyspace-path ::= [ namespace ':' ] bucket [ '.' scope '.' collection ]
    ( namespace ':' )? bucket ( '.' scope '.' collection )?
    keyspace-partial ::= collection
    collection
    namespace ::= identifier
    namespace
    bucket ::= identifier
    keyspace
    scope ::= identifier
    keyspace
    collection ::= identifier
    keyspace
    from-subquery ::= subquery-expr [ AS ] alias
    select expr
    subquery-expr ::= '(' select ')'
    subquery expr
    from-generic ::= expr [ AS alias ]
    generic expr

    JOIN Clause

    join clause

    ANSI JOIN

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

    Lookup JOIN

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

    Index JOIN

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

    NEST Clause

    nest clause

    ANSI NEST

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

    Lookup NEST

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

    Index NEST

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

    UNNEST Clause

    unnest-clause ::= from-term [ unnest-type ] ( UNNEST | FLATTEN ) expr [ [ AS ] alias ]
    unnest clause
    unnest-type ::= INNER | ( LEFT [ OUTER ] )
    unnest type

    USE Clause

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

    LET Clause

    let-clause ::= LET alias '=' expr [ ',' alias '=' expr ]*
    let clause

    WHERE Clause

    where-clause ::= WHERE cond
    where clause
    cond ::= expr
    cond

    GROUP BY Clause

    group-by-clause ::= GROUP BY expr [ ',' expr ]* [ letting-clause ] [ having-clause ] | letting-clause
    group by clause
    letting-clause ::= LETTING alias '=' expr [ ',' alias '=' expr ]*
    letting clause
    having-clause ::= HAVING cond
    having clause

    WINDOW Clause

    window-clause ::= WINDOW window-declaration [ ',' window-declaration ]*
    window clause
    window-declaration ::= window-name AS '(' window-definition ')'
    window declaration
    window-name ::= identifier
    window name
    window definition
    window-ref ::= identifier
    window ref
    window-partition-clause ::= PARTITION BY expr [ ',' expr ]*
    window partition clause
    window-order-clause ::= ORDER BY ordering-term [ ',' ordering-term ]*
    window order clause
    window-frame-clause ::= ( ROWS | RANGE | GROUPS ) window-frame-extent [ window-frame-exclusion ]
    window frame clause
    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 ) )
    window frame extent
    window-frame-exclusion ::= EXCLUDE ( CURRENT ROW | GROUP | TIES | NO OTHERS )
    window frame exclusion

    ORDER BY Clause

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

    LIMIT Clause

    limit-clause ::= LIMIT expr
    limit clause

    OFFSET Clause

    offset-clause ::= OFFSET expr
    offset clause