A newer version of this documentation is available.

View Latest

SELECT Syntax

  • reference

Couchbase Server Enterprise Edition 7.6 reaches End of Life on March 31, 2027. Please upgrade to 8.0.x by following Upgrade, or contact Couchbase Support.

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 exclude-clause?
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
exclude-clause ::= 'EXCLUDE' exclude-term ( ',' exclude-term )*
Syntax diagram
exclude-term ::= identifier | string-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