YogeshChauhan . com

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?

amazon

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

How does @extend rule work in SCSS (Sass)?SCSSCheck if any column has NULL values in PostgresPostgresKilling A Project Part 1: What criteria should be used to cancel/kill a project?MiscA complete diagram with building blocks of an Angular applicationAngularHow to apply style only to first child and/or only to children other than the first child?CSSFETCH clause in PostgreSQLPostgres