DocumentationChaosSearch Blog
StatusHome Page

Subqueries

An overview of ChaosSearch subquery support in SQL Analytics.

Suggest Edits

A subquery is an expression which is composed of a query. A subquery is also called a nested query or a subselect.

A subquery could be used in instances of a SELECT FROM condition, or a WITH clause with_query statement, condition. In ChaosSearch, subqueries support a single level of nesting only and cannot include JOINs, DISTINCT, or COUNT DISTINCT.

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.

📘

Syntax limitations

Currently the WHERE EXISTS and WHERE col IN subquery syntax is not supported.

📘

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 1000;

SELECT name
FROM "nation-view"
WHERE n_regionkey = (SELECT min(r_regionkey) FROM "region-view");


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

Updated about 1 month ago