Hey, folks! Hope you all are doing well. In this article, we will be focusing on SQL Exists Operator in detail.
Working of SQL Exists
SQL Exists is an SQL Operator works with a subquery and enables us to check for the presence of particular records in that subquery. Further, it checks whether the subquery returns some records from the table depending upon the condition.
Let us have a look at the below example to the operator.
Consider an Online Portal where the owner decides to offer a discount of $20 to every customer whose bills are higher than $350.
For the same, the developer team at the backend would segregate the database and use the Exists operator to check for the presence of such condition in the various tables.
Thus, SQL Exists executes a certain query, upon the presence of data returned by the subquery.
Let us now understand the Syntax of Exists Operator in detail.
Syntax of the Exists Operator
The Exists Operator works well with SQL Update, Delete, Select and Insert query.
SQL Update/Insert/Delete/Select query WHERE EXISTS (sub-query);
EXISTS evaluates to TRUE, if and only if the subquery gets executed and returns the specified records.
The Sub query can be used to query another database too if the column values are comparable.
Having understood the working and structure of Exists Operator, let us now focus on the implementation of the same in the upcoming section.
Implementing SQL EXISTS through examples
Let us first create the table in SQL that we would be using in the below examples. Using some random names of places here for demonstration.
create table Info(id integer, Cost integer, city varchar(20)); insert into Info(id, Cost, city) values(1, 100, Pune); insert into Info(id, Cost, city) values(2, 50, Satara); insert into Info(id, Cost, city) values(3, 65, Pune); insert into Info(id, Cost, city) values(4, 97, Mumbai); insert into Info(id, Cost, city) values(5, 12, USA);
create table Supply(id integer, Cost integer); insert into Supply(id, Cost) values(1, 100); insert into Supply(id, Cost) values(2, 200); insert into Supply(id, Cost) values(3, 300); insert into Supply(id, Cost) values(4, 400); insert into Supply(id, Cost) values(5, 12);
SQL Exists with Select query
In the below example, we have used SQL Select query along with the Exists Operator. The below query selects and displays row values of ‘id’ and ‘City’ only if ‘Cost’ has data values greater than 50.
SELECT id,City FROM Info WHERE EXISTS (SELECT Cost FROM Info WHERE Cost>50);
SQL Exists with Delete query
Now, we have implemented SQL Delete query with SQL Exists operator to perform the operation as follows–
The below query would delete a data row from the table ‘Info’ if and only if, the column ‘id’ from both the tables(Info and Supply) is the same and there exists a value for ‘Cost’ as 12 in the Supply table.
DELETE FROM Info WHERE EXISTS (SELECT * FROM Supply WHERE Info.id = Supply.id AND Supply.Cost = 12);
SQL Exists with Update query
In this example, we have used the SQL Update query along with SQL Exists Operator. All the data values of the column ‘Cost’ would be set to 20 if and only if the value for column ‘id’ of both the table happens to be equal.
Update Info set Cost = 20 WHERE EXISTS (SELECT * FROM Supply WHERE Info.id = Supply.id );
By this, we have come to the end of this topic. Feel free to comment below, in case you come across any doubt.
For more such posts related to SQL, please do visit SQL JournalDev.