EXIST 和 EXIST NOT 运算符在 MySQL 子查询中有何用途?
mysqlmysqli database
EXIST 运算符测试子查询结果集中是否存在行。如果找到子查询行值，则 EXISTS 子查询为 TRUE，如果为 FALSE，则 NOT EXISTS 子查询为 FALSE。为了说明这一点，我们使用了具有以下数据的表"Cars"、"Customers"和"Reservations" −
mysql> Select * from Cars; +------+--------------+---------+ | ID | Name | Price | +------+--------------+---------+ | 1 | Nexa | 750000 | | 2 | Maruti Swift | 450000 | | 3 | BMW | 4450000 | | 4 | VOLVO | 2250000 | | 5 | Alto | 250000 | | 6 | Skoda | 1250000 | | 7 | Toyota | 2400000 | | 8 | Ford | 1100000 | +------+--------------+---------+ 8 rows in set (0.02 sec) 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)
以下是使用上述表的 EXIST 的 MySQL 子查询 −
mysql> Select Name from customers WHERE EXISTS (SELECT * FROM Reservations WHERE Customers.customer_id = Reservations.customer_id); +---------+ | Name | +---------+ | Rahul | | Yashpal | | Gaurav | +---------+ 3 rows in set (0.06 sec)
上述查询给出了已预订的客户的姓名。
以下是使用上述表的带有 NOT EXIST 的 MySQL 子查询 −
mysql> Select Name from customers WHERE NOT EXISTS (SELECT * FROM Reservations WHERE Customers.customer_id = Reservations.customer_id); +----------+ | Name | +----------+ | Virender | +----------+ 1 row in set (0.04 sec)
上述查询给出了尚未进行任何预订的客户的姓名。
