Back
Outdated Version

You are viewing an older version of this section. View current production version.

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 returns TRUE and NOT EXISTS subquery returns FALSE.
  • If the subquery returns no records, NOT EXISTS subquery returns TRUE and EXISTS subquery returns FALSE.
  • MemSQL 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         |
+--------------+
2 rows in set (0.62 sec)

Questions?

Ask the Community, find solutions in the Troubleshooting Overview, get Self-Paced Training or learn more about our Support Plans.