EXISTS AND NOT EXISTS

Syntax

SELECT column-list FROM table WHERE
{ EXISTS | NOT EXISTS }
    ( SELECT column FROM table WHERE condition )

Remarks

• If the subquery returns any records, EXISTSsubquery returns TRUE and NOT EXISTSsubquery returns FALSE.

• If the subquery returns no records, NOT EXISTSsubquery returns TRUE and EXISTSsubquery returns FALSE.

• 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 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         |
+--------------+