SQL Exists Operator – An Ultimate Guide

Filed Under: SQL
SQL Exists Operator

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);

Output:

idCostcity
1100Pune
250Satara
365Pune
497Mumbai
512USA
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);

Output:

Supply Table
SQL Exists–Supply Table

As mentioned above, we have created the tables — ‘Info’ and ‘Supply’ using SQL Create table query and have added data to it using SQL Insert command.


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);

Output:

SQL Exists--SELECT Statement
SQL Exists–SELECT Statement

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);

Output:

SQL Exists--DELETE Statement
SQL Exists–DELETE Statement

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
              );

Output:

SQL Exists--UPDATE Statement
SQL Exists–UPDATE Statement

Conclusion

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.


References

Comments

  1. Juan Jose Elicegui says:

    Outputs do not reflect the implementing examples. It could be confusing.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages