SQL++ for Mobile — Differences from SQL++ for Server
Description — Differences between Couchbase Server SQL++ and Couchbase Lite N1QL
Related Content — Predictive Queries | Live Queries | Indexing
N1QL is Couchbase’s implementation of the developing SQL++ standard. As such the terms N1QL and SQL++ are used interchangeably in Couchbase documentation unless explicitly stated otherwise. |
There are several minor but notable behavior differences between SQL++ for Mobile queries and SQL++ for Server, as shown in Table 1.
In some instances, if required, you can force SQL++ for Mobile to work in the same way as SQL++ for Server. This table compares Couchbase Server and Mobile instances:
SQL++ Comparison |
||
---|---|---|
Feature |
SQL++ for Couchbase Server |
SQL++ for Mobile |
Scopes and Collections |
SELECT * |
SELECT * |
Scopes and Collections |
SELECT * |
SELECT * |
USE KEYS |
SELECT fname, email FROM tutorial USE KEYS ["dave", "ian"]; |
SELECT fname, email FROM tutorial WHERE meta().id IN ("dave", "ian"); |
ON KEYS |
SELECT * |
SELECT * FROM user u, u.order_history s |
ON KEY |
SELECT * |
SELECT * FROM user u |
NEST |
SELECT * |
NEST/UNNEST not supported |
LEFT OUTER NEST |
SELECT * FROM user u |
NEST/UNNEST not supported |
ARRAY |
ARRAY i FOR i IN [1, 2] END |
(SELECT VALUE i FROM [1, 2] AS i) |
ARRAY FIRST |
ARRAY FIRST arr |
arr[0] |
LIMIT l OFFSET o |
Allows OFFSET without LIMIT |
Allows OFFSET without LIMIT |
UNION, INTERSECT, and EXCEPT |
All three are supported (with ALL and DISTINCT variants) |
Not supported |
OUTER JOIN |
Both LEFT and RIGHT OUTER JOIN supported |
Only LEFT OUTER JOIN supported (and necessary for query expressability) |
<, <=, =, etc. operators |
Can compare either complex values or scalar values |
Only scalar values may be compared |
ORDER BY |
Result sequencing is based on specific rules described in SQL++ (server) OrderBy clause |
Result sequencing is based on the SQLite ordering described in SQLite select overview The ordering of Dictionary and Array objects is based on binary ordering. |
SELECT DISTINCT |
Supported |
SELECT DISTINCT VALUE is supported when the returned values are scalars |
CREATE INDEX |
Supported |
Not Supported |
INSERT/UPSERT/DELETE |
Supported |
Not Supported |
Boolean Logic Rules
SQL++ for Couchbase Server | SQL++ for Mobile |
---|---|
Couchbase Server operates in the same way as Couchbase Lite, except:
You can choose to use Couchbase Server’s SQL++ rules by using the |
SQL++ for Mobile’s boolean logic rules are based on SQLite’s, so:
|
Logical Operations
In SQL++ for Mobile logical operations will return one of three possible values; TRUE
, FALSE
, or MISSING
.
Logical operations with the MISSING
value could result in TRUE
or FALSE
if the result can be determined regardless of the missing value, otherwise the result will be MISSING
.
In SQL++ for Mobile — unlike SQL++ for Server — NULL
is implicitly converted to FALSE
before evaluating logical operations.
Table 2 summarizes the result of logical operations with different operand values and also shows where the Couchbase Server behavior differs.
Operand |
SQL++ for Mobile |
SQL++ for Server |
||||
---|---|---|---|---|---|---|
b |
a AND b |
a OR b |
b |
a AND b |
a OR b |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
CRUD Operations
SQL++ for Mobile only supports Read or Query operations.
SQL++ for Server fully supports CRUD operation.
Functions
Division Operator
SQL++ for Server | SQL++ for Mobile |
---|---|
SQL++ for Server always performs float division regardless of the types of the operands. You can force this behavior in SQL++ for Mobile by using the |
The operand types determine the
division operation performed. |
Round Function
SQL++ for Server | SQL++ for Mobile |
---|---|
SQL++ for Server You can force this behavior in Couchbase Lite by using the |
The
|