You are viewing an older version of this section. View current production version.
Join the MemSQL Community Today
Get expert advice, develop skills, and connect with others.

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)