Sql 简明教程

SQL - Sub Queries

SQL Subqueries

一个 SQL Subquery 是另一个查询中的SELECT查询。它也被称为 Inner query 或 Nested query ，而包含它的查询是外部查询。

An SQL Subquery, is a SELECT query within another query. It is also known as Inner query or Nested query and the query containing it is the outer query.

外部查询可以包含SELECT、INSERT、UPDATE和DELETE语句。我们可以将子查询用作列表达式，用作SQL子句中的条件，以及与操作符（如 =、>、<、>=、⇐、IN、BETWEEN等）结合使用。

The outer query can contain the SELECT, INSERT, UPDATE, and DELETE statements. We can use the subquery as a column expression, as a condition in SQL clauses, and with operators like =, >, <, >=, ⇐, IN, BETWEEN, etc.

Rules to be followed

以下是编写子查询时需要遵循的规则：

Following are the rules to be followed while writing subqueries −

Subqueries must be enclosed within parentheses.
Subqueries can be nested within another subquery.
A subquery must contain the SELECT query and the FROM clause always.
A subquery consists of all the clauses an ordinary SELECT clause can contain: GROUP BY, WHERE, HAVING, DISTINCT, TOP/LIMIT, etc. However, an ORDER BY clause is only used when a TOP clause is specified. It can’t include COMPUTE or FOR BROWSE clause.
A subquery can return a single value, a single row, a single column, or a whole table. They are called scalar subqueries.

Subqueries with the SELECT Statement

子查询最常与SELECT语句一起使用。基本语法如下所示：

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows −

SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE  column_name
OPERATOR (SELECT column_name [,column_name ] FROM table1 [, table2 ] [WHERE]);

Example

在以下查询中，我们创建了一个名为 CUSTOMERS 的表：

In the following query, we are creating a table named CUSTOMERS −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

在这里，我们使用INSERT INTO语句向上面创建的表中插入记录：

Here, we are inserting records into the above-created table using INSERT INTO statement −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

表格显示为 −

The table is displayed as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

现在让我们使用一个 SELECT 语句检查以下子查询。

Now, let us check the following subquery with a SELECT statement.

SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500);

这将产生以下结果 -

This would produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

Subqueries with the INSERT Statement

我们还可以在 INSERT 语句中使用子查询。子查询返回的数据插入到另一个表中。

We can also use the subqueries along with the INSERT statements. The data returned by the subquery is inserted into another table.

基本语法如下 −

The basic syntax is as follows −

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

Example

在以下示例中，我们正在创建另一个表 CUSTOMERS_BKP，其结构与 CUSTOMERS 表类似 −

In the following example, we are creating another table CUSTOMERS_BKP with similar structure as CUSTOMERS table −

CREATE TABLE CUSTOMERS_BKP (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

现在要将 CUSTOMERS 表的完整记录复制到 CUSTOMERS_BKP 表中，我们可以使用以下查询 −

Now to copy the complete records of CUSTOMERS table into the CUSTOMERS_BKP table, we can use the following query −

INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS);

上面的查询生成以下输出 −

The above query produces the following output −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

使用 SELECT 语句，我们可以验证 CUSTOMERS 表中的记录是否已插入到 CUSTOMERS_BKP 表中 −

Using the SELECT statement, we can verify whether the records from CUSTOMERS table have been inserted into CUSTOMERS_BKP table or not −

SELECT * FROM CUSTOMERS_BKP;

将显示以下形式的表 −

The table will be displayed as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Subqueries with the UPDATE Statement

子查询还可以与 UPDATE 语句一起使用。你可以使用子查询更新表中的一个或多个列。

A subquery can also be used with the UPDATE statement. You can update single or multiple columns in a table using a subquery.

基本语法如下 −

The basic syntax is as follows −

UPDATE table
SET column_name = new_value
[WHERE OPERATOR [VALUE](SELECT COLUMN_NAME FROM TABLE_NAME [WHERE]);

Example

我们有可用的 CUSTOMERS_BKP 表，它是 CUSTOMERS 表的备份。以下示例将年龄大于或等于 27 的所有客户在 CUSTOMERS 表中的 SALARY 更新为 0.25 倍。

We have the CUSTOMERS_BKP table available which is backup of CUSTOMERS table. The following example updates SALARY by 0.25 times in the CUSTOMERS table for all the customers whose AGE is greater than or equal to 27.

UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 );

以下是以上查询的输出：

Following is the output of the above query −

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

这将影响两行，如果你使用如下所示的 SELECT 语句验证 CUSTOMERS 的内容。

This would impact two rows and if you verify the contents of the CUSTOMERS using the SELECT statement as shown below.

SELECT * FROM CUSTOMERS;

将显示以下形式的表 −

The table will be displayed as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	500.00
2	Khilan	25	Delhi	1500.00
3	kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	2125.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Subqueries with the DELETE Statement

子查询也可以与 DELETE 语句一起使用；就像上面提到的任何其他语句一样。

The subquery can be used with the DELETE statement as well; like with any other statements mentioned above.

基本语法如下 −

The basic syntax is as follows −

DELETE FROM TABLE_NAME
[WHERE OPERATOR [ VALUE ](SELECT COLUMN_NAME FROM TABLE_NAME)[WHERE)];

Example

我们有一个可用的 CUSTOMERS_BKP 表，它是 CUSTOMERS 表的备份。以下示例删除了年龄大于或等于 27 的所有客户的 CUSTOMERS 表中的记录。

We have a CUSTOMERS_BKP table available which is a backup of the CUSTOMERS table. The following example deletes the records from the CUSTOMERS table for all the customers whose AGE is greater than or equal to 27.

DELETE FROM CUSTOMERS
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 );

上面的查询生成以下输出 −

The above query generate the following output −

OK, 2 rows affected (0.01 sec)

Verification

如果你使用如下所示的 SELECT 语句验证 CUSTOMERS 表的内容。

If you verify the contents of the CUSTOMERS table using the SELECT statement as shown below.

SELECT * FROM CUSTOMERS;

将显示以下形式的表 −

The table will be displayed as −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00