Yogesh Chauhan's Blog

How to Use the EXISTS and NOT EXISTS Operator with a Subquery in SQL and MySQL?

in SQL/MySQL on January 8, 2020

I've discussed about the SQL EXISTS operator in this article:

The SQL EXISTS Operator

Let's take a look at the EXISTS and NOT EXISTS operator with subquery. Both SQL and MySQL supports EXISTS operator and the syntax is same in both as well.

Syntax:


SELECT Columns 
FROM table
WHERE [NOT] EXISTS (Subquery);

Let's take a look at NOT EXISTS example:


SELECT seller_id, seller_name, seller_state FROM sellers
WHERE NOT EXISTS
(SELECT * FROM invoices
WHERE invoices.seller_id = sellers.seller_id);

In the query above, we are using NOT EXISTS with a Subquery which checks if the subquery returns any results back.

All we check using EXISTS and NOT EXISTS is that if the result set exists. 

Also, when we use EXISTS, the subquery won't return any results but it would just specify that whether any rows satisfy the conditions.

The query above fetches all the sellers that don't have invoices in the invoices table. The correlated subquery selects all the invoices that have same seller_id as in the table outside. 

Do we need to code * or column names in the subquery?

It doesn't matter because the subquery actually doesn't return any result set.

After the execution of the subquery, the query will simple check the condition in WHERE clause in which it checks if any invoices found for any sellers. If so, then it won't include those sellers in the result set.

Let's write down query which gets the completely opposite result set using EXISTS.


SELECT seller_id, seller_name, seller_state FROM sellers
WHERE EXISTS
(SELECT * FROM invoices
WHERE invoices.seller_id = sellers.seller_id);

This query will check whether the invoices EXISTS for any sellers and if so, it will include those sellers in the result set.


Most Read

#1 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
3 Ways we can create URLSearchParams Objects in JavaScriptJavaScriptHow to detect if browser supports WebP format on server side PHP script?PHPAn Example of Cross-site Scripting (XSS) Attack in PHP and How to Avoid It?PHPAngular: Templates, directives, data binding, Services and dependency injectionAngularFor Each Loop in Swift for BeginnersSwiftIntroduction to components and templates Part 3: Data bindingAngular