MySQL EXISTS
The EXISTS operator will return true or false if the subquery returns any records. This is mainly used to check whether their is any record in the subquery.
We can use it for select, update, delete and insert statements.
Syntax for EXISTS
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
Consider the following tables.
Customers table:
| customer_id | name | occupation | age |
|---|---|---|---|
| 100 | Andrew | Project Manager | 33 |
| 101 | Beslin | Business Analyst | 31 |
| 102 | Joanna | Senior Developer | 21 |
| 103 | Tianna | Technical Lead | 24 |
Orders table:
| order_id | customer_id | product_name |
|---|---|---|
| 1 | 100 | Iphone |
| 2 | 102 | Lenovo laptop |
Example query for EXISTS
select name, occupation from Customers
where EXISTS
(Select * from Orders
where Customers.customer_id= Orders.customer_id);
Output
| name | occupation |
|---|---|
| Andrew | Project Manager |
| Joanna | Senior Developer |