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?

Share this post