SQL 子查询

In this tutorial you will learn how to embed a query within another query in SQL.

## What Is a Subquery?
A subquery, also known as a nested query or subselect, is a SELECT query embedded within the WHERE or HAVING clause of another SQL query. The data returned by the subquery is used by the outer statement in the same way a literal value would be used.

Subqueries provide an easy and efficient way to handle the queries that depend on the results from another query. They are almost identical to the normal `SELECT` statements, but there are few restrictions. The most important ones are listed below:

- A subquery must always appear within parentheses.
- A subquery must return only one column. This means you cannot use `SELECT *` in a subquery unless the table you are referring has only one column. You may use a subquery that returns multiple columns, if the purpose is row comparison.
- You can only use subqueries that return more than one row with multiple value operators, such as the `IN` or `NOT IN` operator.
- A subquery cannot be a `UNION`. Only a single `SELECT` statement is allowed.

Subqueries are most frequently used with the SELECT statement, however you can use them within a INSERT, UPDATE, or DELETE statement as well, or inside another subquery.

## Subqueries with the SELECT Statement
The following statement will return the details of only those customers whose order value in the orders table is more than 5000 dollar. Also note that we've used the keyword DISTINCT in our subquery to eliminate the duplicate cust_id values from the result set.

```sql
SELECT * FROM customers
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders 
                      WHERE order_value > 5000);
```
<div class="callout callout-success mb-3">Tip: A subquery can return a single value, a single row, a single column, or a table containing one or more rows of one or more columns.</div>

<div class="callout callout-info mb-3">Note: A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.</div>

## Subqueries with the INSERT Statement
Subqueries can also be used with INSERT statements. Here's an example:

```sql
INSERT INTO premium_customers 
SELECT * FROM customers 
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders 
                      WHERE order_value > 5000);
```
The above statement will insert the records of premium customers into a table called premium_customers, by using the data returned from subquery. Here the premium customers are the customers who had placed order worth more than 5000 dollar.

<div class="callout callout-success mb-3">Tip: Checkout the tutorial on SQL cloning tables to learn how to quickly insert many rows into a table from another table using INSERT ... SELECT statement.</div>

## Subqueries with the UPDATE Statement
You can also use the subqueries in conjunction with the UPDATE statement to update the single or multiple columns in a table, as follow:

```sql
UPDATE orders
SET order_value = order_value + 10
WHERE cust_id IN (SELECT cust_id FROM customers 
                      WHERE postal_code = 75016);
```
The above statement will update the order value in the orders table for those customers who live in the area whose postal code is 75016, by increasing the current order value by 10 dollar.

## Subqueries with the DELETE Statement
Similarly, you can use the subqueries in conjunction with the DELETE statement to delete the single or multiple rows in a table, as follow:

```sql
DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM order_details 
                   WHERE product_id = 5);
```
The SQL statement in the example above will delete those orders from the orders table that contains the product whose product_id is 5.