Exists Operator in SQL

If the argument sub-query is non-empty, exists construct returns the value true, otherwise false. To check whether a row is returned through this sub-query or not, it is used. True is returned if one or more rows are returned by executing the sub-query, otherwise False when no rows are returned.

Syntax

SELECT columnName(s) FROM tableName1 WHERE EXISTS

(subquery);

subquery is of the form : SELECT columnName FROM tableName2 WHERE condition

Here the sub-query is executed first. 

The sub-query is a SELECT statement.  The EXISTS condition will be met & it will return TRUE if the subquery returns at least one record in its result set, else,  the EXISTS condition will not be met and it will return FALSE.

Note: The sub-query is returning for EVERY row in the outer query’s table.

Now, let up suppose we have another table: “Accounts” as:

Create Accounts table

CREATE table Accounts(
  CUSTOMERID INT PRIMARY KEY,
  ACCNO INT,
  ACCTYPE VARCHAR(50))
INSERT INTO Accounts(CUSTOMERID,ACCNO,ACCTYPE) VALUES
(1,101,'Savings'),
(2,102,'Savings'),
(3,103,'Savings'),
(4,104,'Current');

Output

Now to see all the customers whose any kind of account exists, we can make use of exists keyword as:

Query

SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Accounts 
WHERE Customer.CUSTOMERID=Accounts.CUSTOMERID);

Output

We here check: match for every CUSTOMERID in the customer table with the CUSTOMERID in the accounts table. 

If Customer.CUSTOMERID = Accounts.CUSTOMERID evaluates to be true in the sub-query, and one row is returned & thus the sub-query evaluates to be true & our outer query (Select * from Customer) gets, executed for that particular customer.

Note: To do the same thing, i.e. to see all the customers whose any kind of account exists, we can make use of IN keyword also.

Query

SELECT * FROM Customer WHERE CUSTOMERID IN (SELECT CUSTOMERID FROM Accounts);

Output

SQL stands for Structured Query Language. SQL is used for retrieving useful information from a large set of data and it is used for storing the data in the Database, modifying, or manipulating the data from the database.

In this article, we are going to discuss IN Operator and EXISTS Operator in SQL as well as the difference between these two operators in detail.