"ALL"、"ANY"、"SOME"、"IN"运算符在 MySQL 子查询中有何用途?
mysqlmysqli database更新于 2024/3/8 14:13:00
‘ALL’、‘ANY’、’SOME’、’IN’ 运算符将值与子查询返回的每个值进行比较。所有这些运算符都必须跟在比较运算符后面。在 MySQL 子查询中使用这些运算符的语法如下 −
使用 ‘ALL’ 的语法
Operand comparison_operator ALL (subquery)
使用‘ANY’的语法
Operand comparison_operator ANY (subquery)
使用‘SOME’的语法
Operand comparison_operator SOME (subquery)
使用‘IN’的语法
Operand comparison_operator IN (subquery)
示例
我们使用下表中的数据来说明如何在子查询中使用‘ALL’、‘ANY’、’SOME’、’IN’运算符。
mysql> Select * from Customers; +-------------+----------+ | Customer_Id | Name | +-------------+----------+ | 1 | Rahul | | 2 | Yashpal | | 3 | Gaurav | | 4 | Virender | +-------------+----------+ 4 rows in set (0.00 sec) mysql> Select * from Reservations; +------+-------------+------------+ | ID | Customer_id | Day | +------+-------------+------------+ | 1 | 1 | 2017-12-30 | | 2 | 2 | 2017-12-28 | | 3 | 2 | 2017-12-29 | | 4 | 1 | 2017-12-25 | | 5 | 3 | 2017-12-26 | +------+-------------+------------+ 5 rows in set (0.00 sec)
以下查询使用‘ALL’运算符和子查询，并在比较子查询返回的所有值后返回结果。
mysql> SELECT customer_id from Customers WHERE customer_id <> ALL(Select customer_id from reservations); +-------------+ | customer_id | +-------------+ | 4 | +-------------+ 1 row in set (0.00 sec)
以下查询将‘ANY’运算符与子查询结合使用，并在比较子查询返回的所有值后返回结果。
mysql> SELECT customer_id from customers WHERE customer_id = ANY(Select customer_id from reservations); +-------------+ | customer_id | +-------------+ | 1 | | 2 | | 3 | +-------------+ 3 rows in set (0.00 sec)
以下查询使用‘SOME’运算符和子查询，并在比较子查询返回的所有值后返回结果。
mysql> SELECT customer_id from customers WHERE customer_id = Some(Select customer_id from reservations); +-------------+ | customer_id | +-------------+ | 1 | | 2 | | 3 | +-------------+ 3 rows in set (0.00 sec)
以下查询使用‘IN’运算符和子查询，并在比较子查询返回的所有值后返回结果。
mysql> SELECT customer_id from customers WHERE customer_id IN (Select customer_id from reservations); +-------------+ | customer_id | +-------------+ | 1 | | 2 | | 3 | +-------------+ 3 rows in set (0.00 sec)
