时间戳

首页 > 解决方案 > SQL中Exists的逻辑解释

mysql - SQL中Exists的逻辑解释

问题描述

所以我有如下的订单和新订单表

**neworders**
+---------+------------+------------+--------------+------------+
| orderId | customerid | ordertotal | discountrate | orderdate  |
+---------+------------+------------+--------------+------------+
|       1 |          3 |    1910.64 |         5.49 | 2019-12-03 |
|       2 |          4 |     150.89 |        15.33 | 2019-06-11 |
|       3 |          5 |     912.55 |        13.74 | 2019-09-15 |
|       4 |          7 |     418.24 |        14.53 | 2019-05-28 |
|       5 |         55 |     512.55 |        13.74 | 2019-06-15 |
|       7 |         57 |     118.24 |        14.53 | 2019-12-28 |
+---------+------------+------------+--------------+------------+
6 rows in set (0.013 sec)

**orders**
+---------+------------+------------+--------------+------------+
| orderId | customerid | ordertotal | discountrate | orderdate  |
+---------+------------+------------+--------------+------------+
|       1 |          3 |    1910.64 |         5.49 | 2019-12-03 |
|       2 |          4 |     150.89 |        15.33 | 2019-06-11 |
|       3 |          5 |     912.55 |        13.74 | 2019-09-15 |
|       4 |          7 |     418.24 |        14.53 | 2019-05-28 |
|       5 |         55 |     512.55 |        13.74 | 2019-06-15 |
|       6 |         57 |     118.24 |        14.53 | 2019-12-28 |
+---------+------------+------------+--------------+------------+
6 rows in set (0.056 sec)

我想将存在与子查询一起使用。这是语法

select*from neworders where exists(select orderId from orders);

这是结果

+---------+------------+------------+--------------+------------+
| orderId | customerid | ordertotal | discountrate | orderdate  |
+---------+------------+------------+--------------+------------+
|       1 |          3 |    1910.64 |         5.49 | 2019-12-03 |
|       2 |          4 |     150.89 |        15.33 | 2019-06-11 |
|       3 |          5 |     912.55 |        13.74 | 2019-09-15 |
|       4 |          7 |     418.24 |        14.53 | 2019-05-28 |
|       5 |         55 |     512.55 |        13.74 | 2019-06-15 |
|       7 |         57 |     118.24 |        14.53 | 2019-12-28 |
+---------+------------+------------+--------------+------------+
6 rows in set (0.001 sec)

我对输出或 orderId 为 7 感到很困惑，这里的逻辑是什么？

标签： mysql

解决方案

exists(select orderId from orders)如果子查询返回任何行，则为 true。由于子查询中没有WHERE子句，它将返回orders表中的所有行。因此，只要 表不为空，EXISTS条件就为真。orders

既然这是真的，WHERE主查询中的条件对于neworders.

如果您只希望其中的行neworders也在 中orders，则需要在子查询中添加相关条件。

select *
from neworders AS n
where exists(
    select orderId 
    from orders AS o
    WHERE n.orderId = o.orderId
);

您也可以只使用JOIN

SELECT n.*
FROM neworders AS n
JOIN orders AS o ON n.orderId = o.orderId

推荐阅读