The EXISTS operator is a logical operator in MySQL used to test if a subquery returns any rows.

The EXISTS operator returns a boolean value, either TRUE or FALSE, depending on whether the subquery returns any rows.

EXISTS Operator:

• The EXISTS operator is used to test for the existence of any record in a subquery.*

• The EXISTS operator returns TRUE if the subquery returns one or more records.*

Syntax:

SELECT column_name
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

---

Example 1: Retrieve the first and last names and customer_ids of customers who have rented a film.

SELECT customer_id, first_name, last_name
FROM customer
WHERE EXISTS (SELECT * FROM rental WHERE customer.customer_id = rental.customer_id);

Output:

---

Example 2: Let's find all the films which have been rented more than 4 times.

SELECT title
FROM film
WHERE EXISTS (
  SELECT *
  FROM rental
  WHERE rental.inventory_id IN (
    SELECT inventory_id
    FROM inventory
    WHERE inventory.film_id = film.film_id
  )
  GROUP BY rental.inventory_id
  HAVING COUNT(*) > 4
);

Output:

---

Example 3: Let's find the staff members who have NOT managed any store.

SELECT staff_id, first_name, last_name
FROM staff
WHERE NOT EXISTS (
  SELECT *
  FROM store
  WHERE store.manager_staff_id = staff.staff_id
);

Output:

---

Example 4: Let's find the staff members who have managed stores.

SELECT staff_id, first_name, last_name
FROM staff
WHERE EXISTS (
  SELECT *
  FROM store
  WHERE store.manager_staff_id = staff.staff_id
);

Output:

So now we have seen the diff between using the NOT EXISTS and EXISTS Operator.

---

Follow me on LinkedIn: https://www.linkedin.com/in/nikhil-shenoy07/

To see more examples, you can always refer w3 schools; it has always been my go-to website to understand things.

References: w3schools.com/mysql/default.asp

*****All the outputs provided are a snippet of the actual result.

* points are referred from https://www.w3schools.com/mysql/mysql_exists.asp