SQL++ for Analytics offers the following key advancements beyond SQL++ for Query:
-
WITH: SQL++ for Analytics supports the ANSI SQL WITH clause to allow the definition of inlined views or variables of primitive types to simplify complex query construction.
-
JOIN: SQL++ for Analytics supports the ANSI join syntax and allows joins on any condition expressions over Analytics collections, arrays, or subqueries.
-
GROUP BY: In SQL++ for Analytics, in addition to a set of aggregate functions as in standard SQL, the groups created by the
GROUP BY
clause are directly usable in nested queries and/or to obtain nested results. -
Subquery: Any valid SQL++ for Analytics query can be used as a subquery.
For SQL++ for Query users, the following matrix is a quick compatibility cheat sheet for SQL++ for Analytics.
Feature | SQL++ for Query | SQL++ for Analytics Equivalent |
---|---|---|
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 * FROM user u |
SELECT * FROM user u, u.order_history s |
ON KEY |
SELECT * FROM user u |
SELECT * FROM user u |
NEST |
SELECT * FROM user u |
SELECT u, orders FROM users u |
LEFT OUTER NEST |
SELECT * FROM user u |
SELECT u, (SELECT VALUE o FROM u.order_history s, orders o WHERE meta(o).id = s.order_id) orders |
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 |
Doesn’t support OFFSET without LIMIT |
UNION, INTERSECT, and EXCEPT |
All three are supported (with ALL and DISTINCT variants) |
Only UNION ALL is supported (and necessary for query expressibility) |
<, <=, =, etc. operators |
Can compare either complex values or scalar values |
Only scalar values may be compared |
ORDER BY |
Can order by complex values or scalar values |
Can only order by scalar values |
SELECT DISTINCT |
Supported |
SELECT DISTINCT VALUE is supported when the returned values are scalars |
CREATE INDEX |
Supported |
Supported but different (e.g., typed) |
INSERT/UPSERT/DELETE |
Supported |
Unsupported (by design) |
SQL++ for Analytics generalizes SQL++ for Query’s syntax constructs such as USE KEYS
, ON KEYS
, ON KEY
, NEST
,
LEFT OUTER NEST
and ARRAY
and thus eliminates cases where must-be-indexed or must-use-keys
restrictions are required for certain SQL++ for Query queries or expressions to be acceptable.
In addition, the general composability of SQL++ for Analytics queries eliminates the need for some of SQL++ for Query’s
special syntax; for example, SQL++ for Analytics does not require or support the IN/WITHIN subclauses of
SQL++ for Query’s existential (SOME, ANY, or EVERY) expressions.
Note that INSERT/UPSERT/DELETE are not supported at all in the Couchbase Analytics Service. Data is mutated in Couchbase Server, using the Couchbase Server SDK or SQL++ for Query mutation, and the mutations will then be automatically synchronized into the Couchbase Analytics Service.