Subquery

This document describes how to use subquery statements in MatrixOne.

Overview

An subquery is a query within another SQL query. With subquery, the query result can be used in another query.

In most cases, there are five types of subqueries:

  • Scalar Subquery, such as SELECT (SELECT s1 FROM t2) FROM t1.
  • Derived Tables, such as SELECT t1.s1 FROM (SELECT s1 FROM t2) t1.
  • Existential Test, such as WHERE NOT EXISTS(SELECT ... FROM t2), WHERE t1.a IN (SELECT ... FROM t2).
  • Quantified Comparison, such as WHERE t1.a = ANY(SELECT ... FROM t2), WHERE t1.a = ANY(SELECT ... FROM t2).
  • Subquery as a comparison operator operand, such as WHERE t1.a > (SELECT ... FROM t2).

For more information on SQL statement, see SUBQUERY.

In addition, from the execution of SQL statements, subquery generally has the following two types:

  • Correlated Subquery: In Correlated Subquery nested in databases, the inner and outer queries would not be independent, and the inner queries would depend on the outer queries.

The execution sequence is as follows:

  1. + Queries a record from the outer query.
  3. + Put the queried records into the inner query, then put the records that meet the conditions into the outer query.
  5. + Repeat the above steps
  7. For example: ``select * from tableA where tableA.cloumn < (select column from tableB where tableA.id = tableB.id))``
  • Self-contained Subquery: In a database nested query, the inner query is entirely independent of the outer query.

The execution sequence is as follows:

  1. + Execute the inner query first.
  3. + The result of the inner query is carried into the outer layer, and then the outer query is executed.
  5. For example: ``select * from tableA where tableA.column = (select tableB.column from tableB)``

Key Feature:

  • Subqueries allow structured queries so that each part of a query statement can be separated.

  • Subqueries provides another way to perform operations that require complex JOIN and UNION.

Example

Before you start

Make sure you have already Deployed standalone MatrixOne.

Preparation

  1. Download the dataset:

    1. https://community-shared-data-1308875761.cos.ap-beijing.myqcloud.com/tpch/tpch-1g.zip

  2. Create the database and tables:

    1. create database d1;
    2. use d1;
    3. CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL,
    4.                    N_NAME       CHAR(25) NOT NULL,
    5.                    N_REGIONKEY  INTEGER NOT NULL,
    6.                    N_COMMENT    VARCHAR(152),
    7.                    PRIMARY KEY (N_NATIONKEY));
    9. CREATE TABLE REGION  ( R_REGIONKEY  INTEGER NOT NULL,
    10.                    R_NAME       CHAR(25) NOT NULL,
    11.                    R_COMMENT    VARCHAR(152),
    12.                    PRIMARY KEY (R_REGIONKEY));
    14. CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
    15.                  P_NAME        VARCHAR(55) NOT NULL,
    16.                  P_MFGR        CHAR(25) NOT NULL,
    17.                  P_BRAND       CHAR(10) NOT NULL,
    18.                  P_TYPE        VARCHAR(25) NOT NULL,
    19.                  P_SIZE        INTEGER NOT NULL,
    20.                  P_CONTAINER   CHAR(10) NOT NULL,
    21.                  P_RETAILPRICE DECIMAL(15,2) NOT NULL,
    22.                  P_COMMENT     VARCHAR(23) NOT NULL,
    23.                  PRIMARY KEY (P_PARTKEY));
    25. CREATE TABLE SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL,
    26.                     S_NAME        CHAR(25) NOT NULL,
    27.                     S_ADDRESS     VARCHAR(40) NOT NULL,
    28.                     S_NATIONKEY   INTEGER NOT NULL,
    29.                     S_PHONE       CHAR(15) NOT NULL,
    30.                     S_ACCTBAL     DECIMAL(15,2) NOT NULL,
    31.                     S_COMMENT     VARCHAR(101) NOT NULL,
    32.                     PRIMARY KEY (S_SUPPKEY));
    34. CREATE TABLE PARTSUPP ( PS_PARTKEY     INTEGER NOT NULL,
    35.                     PS_SUPPKEY     INTEGER NOT NULL,
    36.                     PS_AVAILQTY    INTEGER NOT NULL,
    37.                     PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
    38.                     PS_COMMENT     VARCHAR(199) NOT NULL,
    39.                     PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY));
    41. CREATE TABLE CUSTOMER ( C_CUSTKEY     INTEGER NOT NULL,
    42.                     C_NAME        VARCHAR(25) NOT NULL,
    43.                     C_ADDRESS     VARCHAR(40) NOT NULL,
    44.                     C_NATIONKEY   INTEGER NOT NULL,
    45.                     C_PHONE       CHAR(15) NOT NULL,
    46.                     C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
    47.                     C_MKTSEGMENT  CHAR(10) NOT NULL,
    48.                     C_COMMENT     VARCHAR(117) NOT NULL,
    49.                     PRIMARY KEY (C_CUSTKEY));
    51. CREATE TABLE ORDERS  ( O_ORDERKEY       BIGINT NOT NULL,
    52.                    O_CUSTKEY        INTEGER NOT NULL,
    53.                    O_ORDERSTATUS    CHAR(1) NOT NULL,
    54.                    O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
    55.                    O_ORDERDATE      DATE NOT NULL,
    56.                    O_ORDERPRIORITY  CHAR(15) NOT NULL,
    57.                    O_CLERK          CHAR(15) NOT NULL,
    58.                    O_SHIPPRIORITY   INTEGER NOT NULL,
    59.                    O_COMMENT        VARCHAR(79) NOT NULL,
    60.                    PRIMARY KEY (O_ORDERKEY));
    62. CREATE TABLE LINEITEM ( L_ORDERKEY    BIGINT NOT NULL,
    63.                     L_PARTKEY     INTEGER NOT NULL,
    64.                     L_SUPPKEY     INTEGER NOT NULL,
    65.                     L_LINENUMBER  INTEGER NOT NULL,
    66.                     L_QUANTITY    DECIMAL(15,2) NOT NULL,
    67.                     L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
    68.                     L_DISCOUNT    DECIMAL(15,2) NOT NULL,
    69.                     L_TAX         DECIMAL(15,2) NOT NULL,
    70.                     L_RETURNFLAG  CHAR(1) NOT NULL,
    71.                     L_LINESTATUS  CHAR(1) NOT NULL,
    72.                     L_SHIPDATE    DATE NOT NULL,
    73.                     L_COMMITDATE  DATE NOT NULL,
    74.                     L_RECEIPTDATE DATE NOT NULL,
    75.                     L_SHIPINSTRUCT CHAR(25) NOT NULL,
    76.                     L_SHIPMODE     CHAR(10) NOT NULL,
    77.                     L_COMMENT      VARCHAR(44) NOT NULL,
    78.                     PRIMARY KEY (L_ORDERKEY, L_LINENUMBER));

  3. Load data into the created tables:

    1. load data infile '/YOUR_TPCH_DATA_PATH/nation.tbl' into table NATION FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
    3. load data infile '/YOUR_TPCH_DATA_PATH/region.tbl' into table REGION FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
    5. load data infile '/YOUR_TPCH_DATA_PATH/part.tbl' into table PART FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
    7. load data infile '/YOUR_TPCH_DATA_PATH/supplier.tbl' into table SUPPLIER FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
    9. load data infile '/YOUR_TPCH_DATA_PATH/partsupp.tbl' into table PARTSUPP FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
    11. load data infile '/YOUR_TPCH_DATA_PATH/orders.tbl' into table ORDERS FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
    13. load data infile '/YOUR_TPCH_DATA_PATH/customer.tbl' into table CUSTOMER FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
    15. load data infile '/YOUR_TPCH_DATA_PATH/lineitem.tbl' into table LINEITEM FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

Then you can query data in MatrixOne with the created table.

Self-contained subquery

For a self-contained subquery that uses subquery as operand of comparison operators (>, >=, <, <=, = , or ! =), the inner subquery queries only once, and MatrixOne rewrites it as a constant during the execution plan phase.

  1. mysql> select p.p_name from (select * from part where p_brand='Brand#21' and p_retailprice between 1100 and 1200)  p, partsupp ps where p.p_partkey=ps.ps_partkey and p.p_name like '%pink%' limit 10;

The inner subquery is executed before MatrixOne executes the above query:

  1. mysql> select * from part where p_brand='Brand#21' and p_retailprice between 1100 and 1200

Result is as below:

  1. +-----------------------------------+
  2. | p_name                            |
  3. +-----------------------------------+
  4. | olive chartreuse smoke pink tan   |
  5. | olive chartreuse smoke pink tan   |
  6. | olive chartreuse smoke pink tan   |
  7. | olive chartreuse smoke pink tan   |
  8. | pink sienna dark bisque turquoise |
  9. | pink sienna dark bisque turquoise |
  10. | pink sienna dark bisque turquoise |
  11. | pink sienna dark bisque turquoise |
  12. | honeydew orchid cyan magenta pink |
  13. | honeydew orchid cyan magenta pink |
  14. +-----------------------------------+
  15. 10 rows in set (0.06 sec)

For self-contained subqueries such as Existential Test and Quantified Comparison, MatrixOne rewrites and replaces them with equivalent queries for better performance.

Correlated subquery

For correlated subquery, because the inner subquery references the columns from the outer query, each subquery is executed once for each row of the outer query. That is, assuming that the outer query gets 10 million results, the subquery will also be executed 10 million times, which will consume more time and resources.

Therefore, in the process of processing, MatrixOne will try to Decorrelate of Correlated Subquery to improve the query efficiency at the execution plan level.

  1. mysql> select p_name from part where P_PARTKEY in (select PS_PARTKEY from PARTSUPP where PS_SUPPLYCOST>=500) and p_name like '%pink%' limit 10;

Rewrites it to an equivalent join query:

  1. select p_name from part join partsupp on P_PARTKEY=PS_PARTKEY where PS_SUPPLYCOST>=500 and p_name like '%pink%' limit 10;

Result is as below:

  1. +------------------------------------+
  2. | p_name                             |
  3. +------------------------------------+
  4. | papaya red almond hot pink         |
  5. | turquoise hot smoke green pink     |
  6. | purple cornsilk red pink floral    |
  7. | pink cyan purple white burnished   |
  8. | sandy dark pink indian cream       |
  9. | powder cornsilk chiffon slate pink |
  10. | rosy light black pink orange       |
  11. | pink white goldenrod ivory steel   |
  12. | cornsilk dim pink tan sienna       |
  13. | lavender navajo steel sandy pink   |
  14. +------------------------------------+
  15. 10 rows in set (0.23 sec)

As a best practice, in actual development, it is recommended to avoid querying through a correlated subquery if you can write another equivalent query with better performance.