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.

Step 2:

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

Step 3:

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

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.

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.

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

Most Popular Posts

How to use Flickr API with PHP

Posted on 11 years ago

Bhumi

What’s New in Docker 1.12

Posted on 7 years ago

Bhumi