Subquery and EXISTS issue

You are viewing an old version of this question. View the current version here.
MariaDB [employee_prof]> Select e1.name from employee e1 where exists (Select e2.name from employee e2 where e2.salary > 2000);

+---------+
| name    |
+---------+
| ramon   |
| fidel   |
| Elpidio |
+---------+
3 rows in set (0.000 sec)

MariaDB [employee_prof]> Select name from employee where salary > 2000;
--------------
Select name from employee where salary > 2000
--------------

+---------+
| name    |
+---------+
| fidel   |
| Elpidio |
+---------+
2 rows in set (0.000 sec)

MariaDB [employee_prof]> Select * from employee;
--------------
Select * from employee
--------------

+----+---------+--------+
| id | name    | salary |
+----+---------+--------+
|  1 | ramon   |   2000 |
|  2 | fidel   |   2500 |
|  3 | Elpidio |   3000 |
+----+---------+--------+
3 rows in set (0.000 sec)

From the sample above. I can't get the correct value of the query. I am using MariaDB 10.11.8

Answer

If you're trying to get the first query to return the same result as the second query, this is not how EXISTS works. If the subquery returns any rows at all, then the subquery returns true (and therefore the query is effectively Select e1.name from employee e1), otherwise it returns false. See Subqueries and EXISTS.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.