N1QL for Analytics vs. N1QL for Query

Enterprise Edition

N1QL for Analytics offers the following key advancements beyond N1QL for Query:

WITH: N1QL 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: N1QL for Analytics supports the ANSI join syntax and allows joins on any condition expressions over Analytics collections, arrays, or subqueries.
GROUP BY: In N1QL 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 N1QL for Analytics query can be used as a subquery.

For N1QL for Query users, the following matrix is a quick compatibility cheat sheet for N1QL for Analytics.

Feature	N1QL for Query	N1QL 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 JOIN orders o ON KEYS ARRAY s.order_id FOR s IN u.order_history END;	SELECT * FROM user u, u.order_history s JOIN orders o ON s.order_id = meta(o).id;
ON KEY	SELECT * FROM user u JOIN orders o ON KEY o.user_id FOR u;	SELECT * FROM user u JOIN orders o ON meta(u).id = o.user_id;
NEST	SELECT * FROM user u NEST orders orders ON KEYS ARRAY s.order_id FOR s IN u.order_history END;	SELECT u, orders FROM users u LET orders=(SELECT VALUE o FROM u.order_history s, orders o WHERE meta(o).id = s.order_id) WHERE EXISTS orders;
LEFT OUTER NEST	SELECT * FROM user u LEFT OUTER NEST orders orders ON KEYS ARRAY s.order_id FOR s IN u.order_history END;	SELECT u, (SELECT VALUE o FROM u.order_history s, orders o WHERE meta(o).id = s.order_id) orders FROM users u;
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)

Feature

N1QL for Query

N1QL 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
JOIN orders o ON KEYS ARRAY s.order_id FOR s IN u.order_history END;

SELECT * FROM user u, u.order_history s
JOIN orders o ON s.order_id = meta(o).id;

ON KEY

SELECT * FROM user u
JOIN orders o ON KEY o.user_id FOR u;

SELECT * FROM user u
JOIN orders o ON meta(u).id = o.user_id;

NEST

SELECT * FROM user u
NEST orders orders
ON KEYS ARRAY s.order_id FOR s IN u.order_history END;

SELECT u, orders FROM users u
LET orders=(SELECT VALUE o FROM u.order_history s, orders o WHERE meta(o).id = s.order_id)
WHERE EXISTS orders;

LEFT OUTER NEST

SELECT * FROM user u
LEFT OUTER NEST orders orders
ON KEYS ARRAY s.order_id FOR s IN u.order_history END;

SELECT u, (SELECT VALUE o FROM u.order_history s, orders o WHERE meta(o).id = s.order_id) orders
FROM users u;

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)

N1QL for Analytics generalizes N1QL 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 N1QL for Query queries or expressions to be acceptable. In addition, the general composability of N1QL for Analytics queries eliminates the need for some of N1QL for Query’s special syntax; for example, N1QL for Analytics does not require or support the IN/WITHIN subclauses of N1QL 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 N1QL for Query mutation, and the mutations will then be automatically synchronized into the Couchbase Analytics Service.