Sql 简明教程

SQL - ANY, ALL Operators

SQL ANY and ALL 运算符用于执行单一值与子查询返回的值的范围之间的比较。

The SQL ANY and ALL operators are used to perform a comparison between a single value and a range of values returned by the subquery.

The SQL ANY Operator

ANY 运算符用于验证查询的任何单一记录是否满足所需条件。

The ANY operator is used to verify if any single record of a query satisfies the required condition.

如果此条件对范围内的任何值满足，则此运算符返回 TRUE。如果指定范围内的任何值都不满足给定条件，则此运算符返回 false。您还可以与此运算符一起使用另一个查询（子查询）。

This operator returns a TRUE, if the given condition is satisfied for any of the values in the range. If none of the values in the specified range satisfy the given condition, this operator returns false. You can also use another query (subquery) along with this operator.

Syntax

SQL - ANY 运算符的基本语法如下：

The basic syntax of the SQL - ANY operator is as follows −

Column_name operator ANY (subquery);

其中，

Where,

column_name is the name of a column in the main query.
operator is a comparison operator such as =, <, >, ⇐, >=, or <>.
subquery is a SELECT statement that returns a single column of values.

ANY with '>' Operator

通常，ANY 运算符用于将值与子查询返回的值集进行比较，在这种情况下，我们可以将它与 > （大于）运算符一起使用，以验证特定列值是否大于子查询返回的任何记录的列值。

Typically, the ANY operator is used to compare a value with a set of values returned by a subquery, in such cases we can use it with the > (greater than) operator to verify if a particular column value is greater than column value of any of the records returned by the sub query.

Example

为了更好地理解它，让我们考虑一下 CUSTOMERS 表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和工资等，如下所示：

To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

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 语句向此表中插入值：

Now, insert values into this table using the INSERT statement as follows −

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 will be created as follows −

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

现在，让我们列出所有 SALARY 为 greater than 的客户的详细信息，即在这个案例中 AGE 为 32 的客户的 SALARY，即 Chaitali、Hardik、Komal 和 Muffy：

Now, let us list out the details of all the CUSTOMERS whose SALARY is greater than the SALARY of any customer whose AGE is 32 i.e. Chaitali, Hardik, Komal and Muffy in this case −

SELECT * FROM CUSTOMERS
WHERE SALARY > ANY (SELECT SALARY FROM CUSTOMERS WHERE AGE = 32);

Output

获得的结果如下 −

The result obtained is as follows −

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

ANY with '<' Operator

类似于 '>' 运算符，我们可以将 '<' （小于）运算符与 ANY 一起使用，以验证特定列值是否小于子查询返回的任何记录的列值。

Similar to the '>' operator, we can use the '<' (less than) operator along with ANY to verify if a particular column value is less than column value of any of the records returned by the sub query.

Example

在此处，我们正在查找拥有 SALARY less than 所有客户的平均工资的客户的不同年龄/年龄，这些客户以前从创建的 CUSTOMERS 表中得到过工资：

In here, we are finding the distinct/different age of customers having any salary less than the average salary of all the customers from the CUSTOMERS table previously created −

SELECT DISTINCT AGE FROM CUSTOMERS
WHERE SALARY < ANY (SELECT AVG(SALARY) FROM CUSTOMERS);

Output

在执行上述查询时，我们会得到以下输出：

We get the following output while executing the above query −

AGE

ANY with '=' Operator

当我们使用 = （等于）运算符和 ANY 时，它会验证特定列值是否等于子查询返回的任何记录的列值。

When we use the = (equal to) operator along with ANY, it verifies if a particular column value is equal to the column value of any of the records returned by the sub query.

Example

在下面给出的查询中，我们正在检索所有年龄为 equal to 的客户的详细信息，即名称以 'K' 开头的任何客户的年龄：

In the query given below, we are retrieving the details of all the customers whose age is equal to the age of any customer whose name starts with 'K' −

SELECT * FROM CUSTOMERS
WHERE AGE = ANY (SELECT AGE FROM CUSTOMERS WHERE NAME LIKE 'K%');

Output

产生的结果如下 −

The result produced is as follows −

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

The SQL ALL Operator

SQL ALL 运算符返回选择语句的所有记录。

The SQL ALL operator returns all the records of the SELECT statement.

It returns TRUE if the given condition is satisfied for ALL the values in the range.
It always returns a Boolean value.
It is used with SELECT, WHERE and HAVING statements in SQL queries.
The data type of the values returned from a subquery must be the same as the outer query expression data type.

Syntax

SQL ALL 运算符的基本语法如下：

The basic syntax of the SQL ALL operator is as follows −

Column_name operator ALL (subquery);

其中，

Where,

column_name − is the name of a column in the main query.
operator − is a comparison operator such as =, <, >, ⇐, >=, or <>.
subquery − is a SELECT statement that returns a single column of values.

ALL with WHERE Statement

当我们对 WHERE 子句使用 ALL 运算符时，它根据指定条件筛选子查询的结果。

When we use the ALL operator with a WHERE clause, it filters the results of the subquery based on the specified condition.

SQL 中的 WHERE 子句用于根据特定条件从查询中筛选行。它在表中的各个行上运行，允许你指定查询返回的数据中每一行必须满足的条件。

The WHERE clause in SQL is used to filter rows from a query based on specific conditions. It operates on individual rows in the table, and it allows you to specify conditions that must be met by each row in the data returned by the query.

Example

如果考虑上面创建的 CUSTOMERS 表，以下查询返回工资为 not equal to 所有客户的详细信息（年龄为 25 的客户的工资 −

If we consider the CUSTOMERS table created above,the following query returns the details of all the customers whose salary is not equal to the salary of any customer whose age is 25 −

SELECT * FROM CUSTOMERS
WHERE SALARY <>
ALL (SELECT SALARY FROM CUSTOMERS WHERE AGE = 25);

Output

以上查询的输出如下 −

The output of the above query is as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

ALL with HAVING Clause

在 SQL 中， ALL 运算符还可以与 HAVING 子句一起使用，以根据应用于组中所有聚合值的条件筛选 GROUP BY 查询的结果。

In SQL, the ALL operator can also be used with the HAVING clause to filter the results of a GROUP BY query based on a condition that applies to all the aggregated values in the group.

Example

以下 SQL 查询用于获取工资为 less than 所有客户的详细信息（平均工资 −

The following SQL query is used to obtain the details of all the customers whose salary is less than the average salary −

SELECT NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
GROUP BY AGE, SALARY
HAVING SALARY < ALL (SELECT AVG(SALARY) FROM CUSTOMERS);

Output

以上查询的输出如下 −

Output of the above query is as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
6	Komal	22	Hyderabad	4500.00