SQL++ for Analytics vs. SQL++ for Query

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)