Logic Explanation for Exists in SQL-CodePudding

So I have orders and neworders table like below

**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)

I want to use exists with subqueries. Here's the syntax

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

And here's the result

 --------- ------------ ------------ -------------- ------------ 
| 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)

I'm so confused by the output or orderId which is 7, what is the logic in here?

CodePudding user response：

exists(select orderId from orders) is true if the subquery returns any rows. Since there's no WHERE clause in the subquery, it will return all the rows in the orders table. So the EXISTS condition is true whenever the orders table is not empty.

SInce that's true, the WHERE condition in the main query will be true for all rows of neworders.

If you only want the rows of neworders that are also in orders, you need to add a correlating condition to the subquery.

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

You can also just use a JOIN

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