EXISTS AND NOT EXISTS
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
.SingleStoreDB 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 employee WHERE EXISTS ( SELECT * FROM manager WHERE employee.name = manager.name ); **** +---------------+ | Name | +---------------+ | Adam Weaver | | Leslie Winkle | | Chris Palms | | Joanna Miles | +---------------+
The following query lists all the stock_symbol values in the trade table which do not match the values in the stock field in the company table.
SELECT stock_symbol FROM trade WHERE NOT EXISTS ( SELECT * FROM company WHERE symbol = stock_symbol ) ; **** +--------------+ | stock_symbol | +--------------+ | ZPNM | | WQOP | +--------------+