Back to Top

How to use EXISTS and NOT EXISTS in MySQL?

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.

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:

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.

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.

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.

Also Read:

Transaction in MySQL
MySQL SHOW Commands
MySQL User Defined Functions

Hope this article helpful to you.As always, thanks for reading an article. Don’t Forget to Follow us on Twitter or Subscribe us to Get the Latest Updates.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

Objects and Classes in PHP

Posted on 2 years ago

Bhumi

To parse CSV Data in PHP

Posted on 7 years ago

Bhumi

Difference between echo and print in PHP

Posted on 7 years ago

Bhumi

How To hide WordPress Toolbar

Posted on 2 years ago

Bhumi

The Reader’s Poll – June 2015

Posted on 3 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 4 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 5 years ago

Bhumi