所以我有如下的訂單和新訂單表
**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)
我想在子查詢中使用exists。這是語法
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)
我對 7 的輸出或 orderId 感到困惑，這里的邏輯是什么？
uj5u.com熱心網友回復：
exists(select orderId from orders)如果子查詢回傳任何行，則為真。由于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
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/351123.html
標籤：mysql