SELECT Syntax

Capella Operational

reference

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

select ::= select-term ( set-op select-term )* order-by-clause? limit-clause? offset-clause?

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

subselect ::= select-from | from-select

select-from ::= with-clause? select-clause from-clause? let-clause? where-clause?
                group-by-clause? window-clause?

from-select ::= with-clause? from-clause let-clause? where-clause? group-by-clause?
                window-clause? select-clause

set-op ::= ( 'UNION' | 'INTERSECT' | 'EXCEPT' ) 'ALL'?

WITH Clause

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

alias ::= identifier

SELECT Clause

select-clause ::= 'SELECT' hint-comment? projection

projection ::= ( 'ALL' | 'DISTINCT' )?
               ( result-expr ( ',' result-expr )* |
               ( 'RAW' | 'ELEMENT' | 'VALUE' ) expr ( 'AS'? alias )? )

result-expr ::= ( ( path '.' )? '*' | expr ( 'AS'? alias )? )

path ::= identifier ( '[' expr ']' )* ( '.' identifier ( '[' expr ']' )* )*

FROM Clause

from-clause ::= 'FROM' from-terms

from-terms ::= ( from-keyspace | from-subquery | from-generic )
               ( join-clause | nest-clause | unnest-clause )*  comma-separated-join*

from-keyspace ::= keyspace-ref ( 'AS'? alias )? use-clause?

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

keyspace-path ::= ( 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 ::= ansi-join-clause | lookup-join-clause | index-join-clause

ANSI JOIN

ansi-join-clause ::= ansi-join-type? 'JOIN' ansi-join-rhs ansi-join-predicate

ansi-join-type ::= 'INNER' | ( 'LEFT' 'OUTER'? ) | ( 'RIGHT' 'OUTER'? )

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

rhs-keyspace ::= keyspace-ref ( 'AS'? alias )? ansi-join-hints?

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 ::= 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 ::= unnest-type? ( 'UNNEST' | 'FLATTEN' ) expr ( 'AS'? alias )?

unnest-type ::= 'INNER' | ( 'LEFT' 'OUTER'? )

Comma-Separated Join

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

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

Comma-Separated Join

USE Clause

LET Clause

WHERE Clause

GROUP BY Clause

WINDOW Clause

ORDER BY Clause

LIMIT Clause

OFFSET Clause

Related Links