Skip to main content

import TabItem from "@theme/TabItem"; import Tabs from "@theme/Tabs";

Subquery

A subquery is a SQL statement that is nested within another query, allows the outcome of one query to be used in another. A brief introduction to subqueries is provided below using the example of a bookshop application (the SQL in this document is mostly used to demonstrate supported syntax by the engine and may not have practical value).

Subquery as a Field​

Like this pattern: SELECT (SELECT s1 FROM t2) FROM t1

SQL Example:

SELECT name, price, (SELECT COUNT(*) AS num FROM bookshop.book WHERE price BETWEEN 10 AND 20) FROM bookshop.book ORDER BY price LIMIT 10;

<Tabs defaultValue="cURL" values={[ { label: "cURL", value: "cURL" }, { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT name, price, (SELECT COUNT(*) AS num FROM bookshop.book WHERE price BETWEEN 10 AND 20) FROM bookshop.book ORDER BY price LIMIT 10"
}'
 
sql = "SELECT name, price, (SELECT COUNT(*) AS num FROM bookshop.book WHERE price BETWEEN 10 AND 20) FROM bookshop.book ORDER BY price LIMIT 10"
db = client.db(mk)
db.query(sql)
 
const sql = "SELECT name, price, (SELECT COUNT(*) AS num FROM bookshop.book WHERE price BETWEEN 10 AND 20) FROM bookshop.book ORDER BY price LIMIT 10"
const db = client.db(mk)
await db.query(sql)
 
sql = "SELECT name, price, (SELECT COUNT(*) AS num FROM bookshop.book WHERE price BETWEEN 10 AND 20) FROM bookshop.book ORDER BY price LIMIT 10"
resp, err := glitterClient.Query(ctx, sql)

Derived Table​

For example: SELECT t1.s1 FROM (SELECT s1 FROM t2) t1, - This syntax is currently not supported.

Existence Filtering Condition​

Like this Pattern: WHERE t1.a IN (SELECT ... FROM t2)

SQL Example

SELECT * FROM bookshop.book WHERE name IN (SELECT name FROM bookshop.book WHERE price BETWEEN 10 AND 20);

<Tabs defaultValue="cURL" values={[ { label: "cURL", value: "cURL" }, { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM bookshop.book WHERE name IN (SELECT name FROM bookshop.book WHERE price BETWEEN 10 AND 20)"
}'
 
sql = "SELECT * FROM bookshop.book WHERE name IN (SELECT name FROM bookshop.book WHERE price BETWEEN 10 AND 20)"
db = client.db(mk)
db.query(sql)

 
const sql = "SELECT * FROM bookshop.book WHERE name IN (SELECT name FROM bookshop.book WHERE price BETWEEN 10 AND 20)"
const db = client.db(mk)
await db.query(sql)
 
sql = "SELECT * FROM bookshop.book WHERE name IN (SELECT name FROM bookshop.book WHERE price BETWEEN 10 AND 20)"
resp, err := glitterClient.Query(ctx, sql)

Set Comparison​

Like this pattern: WHERE t1.a = ANY(SELECT ... FROM t2)

SQL Example

SELECT * FROM bookshop.book WHERE name = ANY(SELECT name FROM bookshop.book WHERE price BETWEEN 10 AND 20);

<Tabs defaultValue="cURL" values={[ { label: "cURL", value: "cURL" }, { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM bookshop.book WHERE name = ANY(SELECT name FROM bookshop.book WHERE price BETWEEN 10 AND 20)"
}'
 
sql = "SELECT * FROM bookshop.book WHERE name = ANY(SELECT name FROM bookshop.book WHERE price BETWEEN 10 AND 20)"
db = client.db(mk)
db.query(sql)
 
const sql = "SELECT * FROM bookshop.book WHERE name = ANY(SELECT name FROM bookshop.book WHERE price BETWEEN 10 AND 20)"
const db = client.db(mk)
await db.query(sql)
 
sql = "SELECT * FROM bookshop.book WHERE name = ANY(SELECT name FROM bookshop.book WHERE price BETWEEN 10 AND 20)"
resp, err := glitterClient.Query(ctx, sql)

Subquery As A Comparison Operator Operand​

Like this pattern: WHERE t1.a > (SELECT ... FROM t2)

SQL Example

SELECT * FROM bookshop.book WHERE price > (SELECT MIN(price) FROM bookshop.book);

<Tabs defaultValue="cURL" values={[ { label: "cURL", value: "cURL" }, { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM bookshop.book WHERE price > (SELECT MIN(price) FROM bookshop.book)"
}'
 
sql = "SELECT * FROM bookshop.book WHERE price > (SELECT MIN(price) FROM bookshop.book)"
db = client.db(mk)
db.query(sql)
 
const sql = "SELECT * FROM bookshop.book WHERE price > (SELECT MIN(price) FROM bookshop.book)"
const db = client.db(mk)
await db.query(sql)
 
sql = "SELECT * FROM bookshop.book WHERE price > (SELECT MIN(price) FROM bookshop.book)"
resp, err := glitterClient.Query(ctx, sql)