19. MySQL: EXISTS Operator
2 min read
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.
EXISTSoperator is used to test for the existence of any record in a subquery.*
EXISTSoperator returns TRUE if the subquery returns one or more records.*
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);
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 );
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 );
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 );
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.
*****All the outputs provided are a snippet of the actual result.
* points are referred from https://www.w3schools.com/mysql/mysql_exists.asp