Yogesh Chauhan's Blog

The SQL EXISTS Operator

in SQL/MySQL on September 5, 2019

SQL EXISTS Operator

The SQL EXISTS Operator checks the existence of the result of a subquery. A bit complicated. Let’s simplify this.

A Subquery is the SQL query nested inside another SQL query. So, we get result for that nested query(sub query). We check that result of a subquery by EXISTS operator. 

The EXISTS operator checks whether that subquery returns at least one row. The returns could be multiple rows but all we need to check is if there is any returns of the query applied. If it does return one or more row, then the EXISTS operator returns TRUE for that. If the query doesn’t return any rows then it will be set as FALSE.

Let’s take a look at the syntax.


SELECT column(s) FROM table WHERE EXISTS
(SELECT column FROM table WHERE condition);

It’s pretty easy query syntax comparing to other complex one we have seen recently. The SQL will check for the returns of EXISTS first before pulling up any data. If it returns TRUE then and then the whole query will be executed. If not, it won’t return anything.

Let’s take a look at the example.


SELECT MerchantName, MerchantID FROM merchants
WHERE EXISTS (SELECT ItemName FROM items WHERE items.MerchantID = merchants.MerchantID AND Price < 3);

In the query above, I am selecting Merchant details for some specific conditions. I am checking if I get any rows for the subquery in returns of EXISTS. The subquery in EXISTS checks for item names from the table items which are also available in table merchants (checking using merchantID in both). Also, I have added one more condition of price less than 3. So if both of the conditions are true then the EXISTS will return TRUE and then the whole query will be executed. It will give us Merchant Name and IDs for the matching values. 

Another example. Let’s get the merchant names and IDs who are selling a products for a dollar. 


SELECT MerchantName, MerchantID FROM merchants
WHERE EXISTS (SELECT ItemName FROM items WHERE items.MerchantID = merchants.MerchantID AND Price = 1);

Pretty interesting. Right?


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
Open Source Security Tools for Defense SecurityMiscellaneousPre-defined DatePipe format options in Angular 9AngularHow to catch .keypress() on body using jQuery?jQueryHow to compile and watch Sass using Gulp in WordPress?SCSSHow to make a web page look good on any device?HTMLIs Software Really Eating The World? I don’t think soMiscellaneous