Subqueries

Subqueries

A subquery is an expression that is evaluated by executing an inner SELECT query.

When an expression is contained in a N1QL statement, the expression is evaluated once for every input document to the statement. In the case of a subquery expression, the inner SELECT is executed once for every input document to the outer statement. A subquery expression returns an array every time it is evaluated; the array contains the results of the inner SELECT query. A subquery can refer to variables in the outer statement.

A subquery is just another expression, so it can appear in most places where an expression can appear; typically, a SELECT list or a WHERE clause. Subqueries are mostly used in SELECT, UPDATE and DELETE statements. Many queries that use subqueries can be made to run faster by rewriting them to use JOINs instead of subqueries.

In N1QL, if a subquery has a FROM clause, then USE KEYS is mandatory for the primary keyspace of the subquery.

Here's an example of a subquery breakdown. Let’s say you want to find all purchases in April 2014 where at least one product costs over $500. You’d break this down into two queries.

  • What products were purchased in April, 2014?
  • Of those products, what products cost more than $500?

Each query builds on the results of the previous one. The first query finds the products purchased in April, 2014. The second query is a subquery. It takes the result set from the first query, and finds the products that cost more than $500.

The final result set contains those products purchased in April, 2014 with a price over $500.

Example:

List all purchases in April 2014 having at least one product costing over $500.


 SELECT purchases.purchaseId, l.product 
 FROM purchases UNNEST purchases.lineItems l 
 WHERE DATE_PART_STR(purchases.purchasedAt,"month") = 4
      AND DATE_PART_STR(purchases.purchasedAt,"year") = 2014 
      AND EXISTS (SELECT product.productId FROM product USE KEYS l.product WHERE product.unitPrice > 500);