SELECT Syntax

reference

March 23, 2025

+ 12

This page enables you to drill down through the syntax of a SELECT query.

select-query ::= [ with-clause ] select

select ::= select-term [ set-op [ ALL ] select-term ]* [ order-by-clause | limit-clause | offset-clause ]

select-term ::= subselect | '(' select ')'

subselect ::= select-from | from-select

select-from ::= select-clause [ from-clause ] [ let-clause ] [ where-clause ] [ group-by-clause ]
              [ window-clause ]

from-select ::= from-clause [ let-clause ] [ where-clause ] [ group-by-clause ] [ window-clause ]
                select-clause

set-op ::= UNION | INTERSECT | EXCEPT

WITH Clause

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

SELECT Clause

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

FROM Clause

from-clause ::= FROM from-term

from-term ::= from-keyspace | from-subquery | from-generic | join-clause | nest-clause | unnest-clause

from-keyspace | from-subquery | from-generic | join-clause | nest-clause | unnest-clause

from-keyspace ::= keyspace-ref [ [ AS ] alias ] [ use-clause ]

keyspace-ref ::= keyspace-path | keyspace-partial

keyspace-path ::= [ namespace ':' ] bucket [ '.' scope '.' collection ]

( namespace ':' )? bucket ( '.' scope '.' collection )?

keyspace-partial ::= collection

namespace ::= identifier

bucket ::= identifier

scope ::= identifier

collection ::= identifier

from-subquery ::= subquery-expr [ AS ] alias

subquery-expr ::= '(' select ')'

from-generic ::= expr [ AS alias ]

JOIN Clause

join-clause ::= from-term ( ansi-join-clause | lookup-join-clause | index-join-clause )

ANSI JOIN

ansi-join-clause ::= [ ansi-join-type ] JOIN ansi-join-rhs [ ansi-join-hints ] ansi-join-predicate

ansi-join-type ::= INNER | ( LEFT [ OUTER ] ) | ( RIGHT [ OUTER ] )

ansi-join-rhs ::= rhs-keyspace | rhs-subquery | rhs-generic

rhs-keyspace | rhs-subquery | rhs-generic

rhs-keyspace ::= keyspace-ref [ [ AS ] alias ]

rhs-subquery ::= subquery-expr [ AS ] alias

rhs-generic ::= expr [ AS alias ]

ansi-join-hints ::= use-hash-hint | use-nl-hint | multiple-hints

use-hash-hint ::= USE use-hash-term

use-hash-term ::= HASH '(' ( BUILD | PROBE ) ')'

use-nl-hint ::= USE use-nl-term

use-nl-term ::= NL

multiple-hints ::= USE ( ansi-hint-terms other-hint-terms ) | ( other-hint-terms ansi-hint-terms )

ansi-hint-terms ::= use-hash-term | use-nl-term

other-hint-terms ::= use-index-term | use-keys-term

ansi-join-predicate ::= ON expr

Lookup JOIN

lookup-join-clause ::= [ lookup-join-type ] JOIN lookup-join-rhs lookup-join-predicate

lookup-join-type ::= INNER | ( LEFT [ OUTER ] )

lookup-join-rhs ::= keyspace-ref [ [ AS ] alias ]

lookup-join-predicate ::= ON [ PRIMARY ] KEYS expr

Index JOIN

index-join-clause ::= [ index-join-type ] JOIN index-join-rhs index-join-predicate

index-join-type ::= INNER | ( LEFT [ OUTER ] )

index-join-rhs ::= keyspace-ref [ [ AS ] alias ]

index-join-predicate ::= ON [ PRIMARY ] KEY expr FOR alias

NEST Clause

nest-clause ::= from-term ( ansi-nest-clause | lookup-nest-clause | index-nest-clause )

ANSI NEST

ansi-nest-clause ::= [ ansi-nest-type ] NEST ansi-nest-rhs ansi-nest-predicate

ansi-nest-type ::= INNER | ( LEFT [ OUTER ] )

ansi-nest-rhs ::= keyspace-ref [ [ AS ] alias ]

ansi-nest-predicate ::= ON expr

Lookup NEST

lookup-nest-clause ::= [ lookup-nest-type ] NEST lookup-nest-rhs lookup-nest-predicate

lookup-nest-type ::= INNER | ( LEFT [ OUTER ] )

lookup-nest-rhs ::= keyspace-ref [ [ AS ] alias ]

lookup-nest-predicate ::= ON KEYS expr

Index NEST

index-nest-clause ::= [ index-nest-type ] NEST index-nest-rhs index-nest-predicate

index-nest-type ::= INNER | ( LEFT [ OUTER ] )

index-nest-rhs ::= keyspace-ref [ [ AS ] alias ]

index-nest-predicate ::= ON KEY expr FOR alias

UNNEST Clause

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

unnest-type ::= INNER | ( LEFT [ OUTER ] )

USE Clause

use-clause ::= use-keys-clause | use-index-clause

use-keys-clause ::= USE use-keys-term

use-keys-term ::= [ PRIMARY ] KEYS expr

use-index-clause ::= USE use-index-term

use-index-term ::= INDEX '(' index-ref [ ',' index-ref ]* ')'

index-ref ::= [ index-name ] [ index-type ]

index-name ::= identifier

index-type ::= USING ( GSI | FTS )

LET Clause

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

WHERE Clause

where-clause ::= WHERE cond

cond ::= expr

GROUP BY Clause

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

letting-clause ::= LETTING alias '=' expr [ ',' alias '=' expr ]*

having-clause ::= HAVING cond

WINDOW Clause

window-clause ::= WINDOW window-declaration [ ',' window-declaration ]*

window-declaration ::= window-name AS '(' window-definition ')'

window-name ::= identifier

window-definition ::= [ window-ref ] [ window-partition-clause ] [ window-order-clause ]
                      [ window-frame-clause ]

window-ref ::= identifier

window-partition-clause ::= PARTITION BY expr [ ',' expr ]*

window-order-clause ::= ORDER BY ordering-term [ ',' ordering-term ]*

window-frame-clause ::= ( ROWS | RANGE | GROUPS ) window-frame-extent [ window-frame-exclusion ]

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-exclusion ::= EXCLUDE ( CURRENT ROW | GROUP | TIES | NO OTHERS )

ORDER BY Clause

order-by-clause ::= ORDER BY ordering-term [ ',' ordering-term ]*

ordering-term::= expr [ ASC | DESC ] [ NULLS ( FIRST | LAST ) ]

LIMIT Clause

limit-clause ::= LIMIT expr

OFFSET Clause

offset-clause ::= OFFSET expr

SELECT Syntax

WITH Clause

SELECT Clause

FROM Clause

JOIN Clause

ANSI JOIN

Lookup JOIN

Index JOIN

NEST Clause

ANSI NEST

Lookup NEST

Index NEST

UNNEST Clause

USE Clause

LET Clause

WHERE Clause

GROUP BY Clause

WINDOW Clause

ORDER BY Clause

LIMIT Clause

OFFSET Clause

Related Links