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.

SQL

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.

SQL

SELECT stock_symbol
FROM trade
WHERE NOT EXISTS ( SELECT * FROM company
                  WHERE symbol = stock_symbol
                ) ;

+--------------+
| stock_symbol |
+--------------+
| ZPNM         |
| WQOP         |
+--------------+