YogeshChauhan.com
The SQL EXISTS Operator
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?

dreamhost

Leave a Reply

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to set opacity or transparency using CSS? #3 Pagination in CSS with multiple examples #4 How to make HTML form interactive and using CSS? #5 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

Mar 3 How to embed YouTube or other video links in WordPress? Mar 3 How to change the Login Logo in WordPress? Mar 3 substring() Method in JavaScript Mar 3 Window setInterval() Method in JavaScript Mar 2 How to zoom an element on hover using CSS? Mar 2 the box-sizing property in CSS

You might also like these

Values and Types Basics in JavaScriptJavaScriptHow to remove N/A from Radio Button list in Drupal?DrupalThe Difference Between the echo and print Commands in PHPPHPHow to obfuscate JavaScript code to hide it from View Source?JavaScript2 Ways we can create an Array in JavaScriptJavaScriptHow to get current timestamp in Swift 4 and 5?Swift