Subqueries
Tags: opengauss
Published: 2023-03-09 03:26:35

A subquery, also called an internal query, is a nested query. The subquery embeds a query statement in the **WHERE** clause of a database query and it is equivalent to a temporary table. The query result of a SELECT statement can be used as the input value of another statement.

A subquery can be used with the SELECT, INSERT, UPDATE, and DELETE statements.

The subquery must comply with the following rules:

**·** The subquery must be enclosed in parentheses.
**·** The subquery can have only one column in the **SELECT** clause, unless there are multiple columns in the main query to compare with the columns selected by the subquery.
**·** **ORDER BY** cannot be used in the subquery, although it can be used in the main query. **GROUP BY** can be used in the subquery with the same function as **ORDER BY**.
**·** The subquery returns more than one row. It can be used only with multi-value operators, such as IN.
**·** The BETWEEN operator cannot be used with the subquery, but it can be used inside the subquery.
Subquery in the SELECT Statement
**The SELECT statement queries the data returned by the subquery.** 
The basic syntax is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qcm8femwy5xl63p9iaii.png)

Example:

Create the customer table. The data is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hls0njh3gl5njg9mq9wr.png)

Run the following statement to use a subquery in the SELECT statement:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dsrtlwr443qfeq21zmxu.png)

**Subquery in the INSERT Statement**
Subqueries can also be used with the INSERT statement. The INSERT statement inserts the data returned by the subquery into another table. The basic syntax is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kmxvjlxqbqjduam0iui1.png)

Example:

Create the customer_bak table whose structure is the same as that of the customer_t1 table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fdth2e18v2est1es0w0p.png)

Insert data in the customer_t1 table into the customer_bak table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dicvogj9t8h3jfhm0ny0.png)

After the data is inserted, data in the customer_bak table is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d237o05sf2i9my0zdvms.png)

**Subquery in the UPDATE Statement**
When a subquery is run by executing the UPDATE statement, multiple columns in the table are updated. The basic syntax is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3us6qk52m2uhs2xyzcfu.png)

Example:

Change the value of amount of all customers whose c_customer_sk is set to a value greater than 4000 in the customer_t1 table to 0.50 times of the original value.

openGauss=# UPDATE customer_t1 SET amount = amount * 0.50 WHERE c_customer_sk IN (SELECT c_customer_sk FROM customer_bak WHERE c_customer_sk > 5000 );
UPDATE 2

The update operation affects two rows. After the update, the data in the customer_t1 table is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hmzpmdpnn1dhdgnh2l56.png)

**Subquery in the DELETE Statement**
The basic syntax is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s5859jew9gl88pkt3ofj.png)

Example:

Delete all customers whose c_customer_sk is set to a value greater than 4000 from the customer_t1 table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cnkq0d7annewcyns8kpf.png)

The deletion operation affects two rows. After deletion, the data in the customer_t1 table is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rk8vfg4ah7tup44mbn4d.png)