What exactly does EXISTS do in MySQL?-CodePudding

I read that the output of the subquery doesn't matter and only its existence matter. But, when I change the code in the subquery, why is my output changing? These are the tables:

mysql> select * from boats;
 ------ ----------- ------- 
| bid  | bname     | color |
 ------ ----------- ------- 
|  101 | Interlake | blue  |
|  102 | Interlake | red   |
|  103 | Clipper   | green |
|  104 | Marine    | red   |
 ------ ----------- ------- 

mysql> select * from sailors;
 ------ --------- -------- ------ 
| sid  | sname   | rating | age  |
 ------ --------- -------- ------ 
|   22 | Dustin  |      7 |   45 |
|   29 | Brutus  |      1 |   33 |
|   31 | Lubber  |      8 | 55.5 |
|   32 | Andy    |      8 | 25.5 |
|   58 | Rusty   |     10 |   35 |
|   64 | Horatio |      7 |   35 |
|   71 | Zorba   |     10 |   16 |
|   74 | Horatio |      9 |   40 |
|   85 | Art     |      3 | 25.5 |
|   95 | Bob     |      3 | 63.5 |
 ------ --------- -------- ------ 
10 rows in set (0.00 sec)

mysql> select * from reserves;
 ------ ------ ------------ 
| sid  | bid  | day        |
 ------ ------ ------------ 
|   22 |  101 | 1998-10-10 |
|   22 |  102 | 1998-10-10 |
|   22 |  103 | 1998-10-08 |
|   22 |  104 | 1998-10-08 |
|   31 |  102 | 1998-11-10 |
|   31 |  103 | 1998-11-06 |
|   31 |  104 | 1998-11-12 |
|   64 |  101 | 1998-09-05 |
|   64 |  102 | 1998-09-08 |
|   74 |  103 | 1998-09-08 |
 ------ ------ ------------ 

select sname from sailors s where exists(select * from reserves r where r.bid=103);
     --------- 
    | sname   |
     --------- 
    | Dustin  |
    | Brutus  |
    | Lubber  |
    | Andy    |
    | Rusty   |
    | Horatio |
    | Zorba   |
    | Horatio |
    | Art     |
    | Bob     |
     --------- 
    10 rows in set (0.00 sec)
    
    mysql> select sname from sailors s where exists(select * from reserves r where r.bid=103 and r.sid=s.sid);
     --------- 
    | sname   |
     --------- 
    | Dustin  |
    | Lubber  |
    | Horatio |
     ---------

Also, I am not able to understand what r.sid=s.sid is doing here. All the sid in reserves are already from sailors table. Please someone explain it to me.

CodePudding user response：

The EXISTS is a Boolean Operator which indicates that if there is ANY row in the sub-query you passed to it. When you execute this:

EXISTS(SELECT * FROM reserves r WHERE r.bid=103)

It will return TRUE after finding the FIRST row which has the condition bid = 103 in Reserves table. The first part of the query doesn't matter, it does not matter what you SELECT in Exists and MySQL engine will ignore it, just the WHERE clause is the part which makes the difference, you can use Exists even like this:

EXISTS(SELECT 1 FROM reserves r WHERE r.bid=103)

In the query above, nothing depends on the values in main query, nothing depends on Sailors table, and if there is ANY row in the Reserves table with bid = 103, then it always will return TRUE.

In the second sub-query with EXISTS, you have a different WHERE clause, and it depend on the value of the fields of the main Query, so it will have different result per each row:

EXISTS(SELECT * FROM reserves r WHERE r.bid=103 AND r.sid=s.sid)

In the above query, per each row in Sailors table, MySQL uses sid value to produce the WHERE condition of the sub-query in EXISTS operator, so it will returns TRUE for a row in Sailors table if there are ANY rows in Reserves table which has a bid = 103 and sid = Sailors.sid, and it will returns False for those that has not such a record in Reserves table, and finally you will get a different result

CodePudding user response：

I think I got that. Exists is used to check if the subquery is existing for the main query. I didn't give any link for the main query and subquery in the first query.

For every name in sailors, independently, the subquery is existing. Hence, I got all the names. In the second query, I added s.sid=r.sid which links the main query and subquery. It checks if for a sname, if bid=103, and also, if s.sid=r.sid.

Please comment if I got that right.