HOW TO USE EXISTS AND NOT EXISTS IN MYSQL?

EXISTS and NOT EXISTS are mysql conditions which is used with Subqueries. EXISTS and NOT EXISTS work like IN or NOT IN with subqueries.

What is EXISTS and NOT EXISTS?

EXISTS checks if subquery contain any rows and if yes, EXISTS return true. NOT EXISTS satisfy if subquery returns no rows.EXISTS is more efficient and optimized then the IN because it returns as soon as the first value is found in the table.However, IN is slow in performance.

Traditionally MySQL documented that an EXISTS subquery starts with the SELECT *; but you can start anything like SELECT column, select 5, where as subquery with IN have only single row existence.

Let’s understand an EXISTS and NOT EXISTS with examples:

Step 1:

First of all, create two tables say one is users and other is orders. Here is the statement for creating a tables.

PHP

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE `users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `user_name` varchar(255) NOT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1   CREATE TABLE `orders` ( `order_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

Step 2:

Next, Insert some records in both the tables using following queries:

PHP

1 2 3 4 5 6 7 8 9 10

INSERT INTO `users` (`user_id` ,`user_name`) VALUES (NULL , 'Bhumi'),                                                     (NULL , 'Zinal'),                                                     (NULL , 'Ankit'),                                                     (NULL , 'Jigisha'),                                                     (NULL , 'Ronil');   INSERT INTO `orders` (`order_id` ,`user_id`) VALUES (NULL , '2'),                                                     (NULL , '3');

Step 3:

Now, Let’s check EXISTS and NOT EXISTS query and understand it.

PHP

1 2 3

SELECT * FROM `users` WHERE EXISTS (SELECT * from orders WHERE users.user_id = orders.user_id);

Above query will return all rows from table users with matched user_id of parent table users are exists in subquery table orders. If the result set of the subquery is NULL then this result of this query will be empty.

PHP

1 2 3

SELECT * FROM `users` WHERE NOT EXISTS (SELECT * from orders WHERE users.user_id = orders.user_id);

This query will provide totally reverse result from the first query. This will return all rows from users with not existence rows of user_id in orders table.