Subqueries

An overview of ChaosSearch subquery support in SQL Analytics.

A subquery is an expression which is composed of a query. A subquery could be used in instances of a SELECT FROM condition, or a WITH clause with_query statement, condition.

A correlated subquery is one that refers to columns outside of the subquery. Logically, the subquery will be evaluated for each row in the surrounding query. The referenced columns will thus be constant during any single evaluation of the subquery.

📘

Support for correlated subqueries is limited.

Not every standard form is supported.

Examples

SELECT _id AS _id,
       p_partkey AS PartKey,
       p_name AS p_name,
       p_mfgr AS p_mfgr,
       p_brand AS p_brand
FROM
  (SELECT _id,
          p_partkey,
          p_name,
          p_mfgr,
...
          p_retailprice,
          p_comment
   FROM chaos.partview123
   LIMIT 100) AS virtual_table
WHERE p_mfgr != 'Manufacturer#4'
LIMIT 10000;
3050
SELECT fieldName1,fieldName2 FROM tableName1 WHERE fieldName1 = (SELECT MAX(fieldName1table2) FROM tableName2);

SELECT fieldName1,fieldName2 FROM tableName1 WHERE fieldName1 in (SELECT fieldName1table2 FROM tableName2 WHERE fieldName2table2 > 1000);

SELECT fieldName1,fieldName2 FROM (SELECT fieldName1, fieldName2, fieldName3 FROM tableName2 WHERE fieldName1 > 1000) as tableName1 WHERE fieldName2 < 1000;

SELECT fieldName1,fieldName2, fieldName3 FROM tableName1 WHERE fieldName1 > 1000 GROUPBY fieldName2 HAVING SUM(fieldName3) > (SELECT MAX(fieldName1Table2) FROM tableName2);