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

      +

      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
      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)

      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.