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;

cURL

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"
}'

Python

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)

JavaScript

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)

Golang

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

cURL

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)"
}'

Python

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)

JavaScript

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)

Golang

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

cURL

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)"
}'

Python

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)

JavaScript

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)

Golang

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

cURL

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)"
}'

Python

sql = "SELECT * FROM bookshop.book WHERE price > (SELECT MIN(price) FROM bookshop.book)"
db = client.db(mk)
db.query(sql)

JavaScript

const sql = "SELECT * FROM bookshop.book WHERE price > (SELECT MIN(price) FROM bookshop.book)"
const db = client.db(mk)
await db.query(sql)

Golang

sql = "SELECT * FROM bookshop.book WHERE price > (SELECT MIN(price) FROM bookshop.book)"
resp, err := glitterClient.Query(ctx, sql)