Subqueries

A subquery is a query nested in another query. It can also be referred to as an inner query or inner select. On the other hand, the query containing a subquery may be referred to as the outer query or outer select.

Raijin supports two types of subqueries:

Scalar

A subquery that returns one row and one column, i.e., a single value.

Table

A subquery returning an arbitrary number of rows and columns.

The following SQL commands and clauses support subqueries:

There is no restriction on the number of nesting levels as long as enough memory is available to process the data.

Scalar subqueries

Scalar subqueries are SELECT queries that return a single value. Raijin returns an error if the subquery returns more than one row or column where a scalar query is expected. A subquery that does not return a record is equivalent to a NULL value and will not cause an error. The subquery can reference fields from the outer query.

Example 1. Subquery in SELECT field list
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE t1();
CREATE TABLE t2();
INSERT INTO t1 {"a": 1, "b": 2};
INSERT INTO t2 {"a": 1, "b": 3};
INSERT INTO t2 {"a": 1, "b": 4};

SELECT a, (SELECT MAX(b) FROM t2 WHERE t2.a = t1.a) AS max
    FROM t1;
Result
{"a":1,"max":4}
Example 2. Subquery in WHERE clause
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE t1();
CREATE TABLE t2();
INSERT INTO t1 {"a": 1, "b": 2};
INSERT INTO t1 {"a": 3, "b": 4};
INSERT INTO t2 {"a": 2};

SELECT a, b FROM t1
    WHERE a > (SELECT MIN(a) FROM t2);
Result
{"a":3,"b":4}
Example 3. Subquery in HAVING clause
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE t1();
CREATE TABLE t2();
INSERT INTO t1 {"a": 1, "b": 2};
INSERT INTO t1 {"a": 3, "b": 4};
INSERT INTO t2 {"a": 1};
INSERT INTO t2 {"a": 2};

SELECT a FROM t1
    GROUP BY a
    HAVING (
        SELECT SUM(a) FROM t2 WHERE t2.a < t1.a
    ) > 0;
Result
{"a":3}

Table subqueries

Subqueries specifying a derived table must be enclosed in parentheses. In addition, subqueries that join with other tables or join a table to itself must be assigned an alias.

Example 4. Subquery in INSERT INTO command
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE t1();
CREATE TABLE t2();
INSERT INTO t1 {"a": 1, "b": 2};
INSERT INTO t1 {"a": 3, "b": 4};

INSERT INTO t2 SELECT a AS x, b AS y FROM t1;
SELECT * FROM t2;
Result
{"_id":1,"x":1,"y":2}
{"_id":2,"x":3,"y":4}
Example 5. Subquery in FROM clause
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE t1();
INSERT INTO t1 {"a": 1, "b": 2};
INSERT INTO t1 {"a": 3, "b": 4};

SELECT * FROM (SELECT a,b FROM t1 WHERE a > 1) AS t2;
Result
{"t2.a":3,"t2.b":4}
Example 6. Subquery in JOIN clause
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE t1();
CREATE TABLE t2();
INSERT INTO t1 {"a": 1, "b": 2};
INSERT INTO t2 {"x": 3, "y": 4};

SELECT * FROM t1
    JOIN (SELECT * FROM t2) AS t2
    ON t1._id = t2._id;
Result
{"t1._id":1,"t1.a":1,"t1.b":2,"t2._id":1,"t2.x":3,"t2.y":4}
Example 7. Subquery with IN operator
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE t1();
CREATE TABLE t2();
INSERT INTO t1 {"a": 1, "b": 2};
INSERT INTO t1 {"a": 3, "b": 4};
INSERT INTO t2 {"a": 2};

SELECT a FROM t1 WHERE b IN (SELECT a FROM t2);
Result
{"a":1}