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 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 returnsFALSE
andEXISTS
subquery returnsFALSE
. - 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.
memsql> 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 trade table which do not match the values in stock field in company table.
memsql> 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)