Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
EXISTS AND NOT EXISTS
On this page
Used with a subquery to check if the subquery returns a record.
Syntax
SELECT column-list FROM table WHERE
{ EXISTS | NOT EXISTS }
( SELECT column FROM table WHERE condition )
Remarks
-
If the subquery returns any records,
EXISTS
subquery returnsTRUE
andNOT EXISTS
subquery returnsFALSE
. -
If the subquery returns no records,
NOT EXISTS
subquery returnsTRUE
andEXISTS
subquery returnsFALSE
. -
SingleStore supports
[NOT] EXISTS
with and without correlated queries.
Examples
The following query lists the name of all the employees in the employee table that have a record in the manager table.
SELECT employee.name AS 'Name'FROM employeeWHERE EXISTS ( SELECT * FROM managerWHERE employee.name = manager.name);
+---------------+
| Name |
+---------------+
| Adam Weaver |
| Leslie Winkle |
| Chris Palms |
| Joanna Miles |
+---------------+
The following query lists all the stock_
SELECT stock_symbolFROM tradeWHERE NOT EXISTS ( SELECT * FROM companyWHERE symbol = stock_symbol) ;
+--------------+
| stock_symbol |
+--------------+
| ZPNM |
| WQOP |
+--------------+
Last modified: June 22, 2022