SQL - EXISTS Operator

The SQL EXISTS operator is used to verify whether a particular record exists in a SQL table. While using this operator we need to specify the record (for which you have to check the existence) using a subquery.

The EXISTS operator is used in the WHERE clause of a SELECT statement to filter records based on the existence of related records in another table.

It is a logical operator.

It returns a Boolean value TRUE or FALSE.

It returns TRUE if the subquery returns at least one record.

If the EXISTS operator returns TRUE, the outer query will get executed; otherwise not.

It can be used in SELECT, UPDATE, DELETE or INSERT statements.

The EXISTS operator is more efficient than other operators, such as IN, because it only needs to determine whether any rows are returned by the subquery, rather than actually returning the data.

The use of the EXISTS operator is an efficient way to filter data in many real-pfe scenarios, including filtering records based on the existence of related data, aggregating data based on the existence of related records, and optimizing queries.

Syntax

The basic syntax of the SQL EXISTS operator is as follows −

WHERE EXISTS (subquery);

Where, the subquery used is the SELECT statement. The EXISTS operator will evaluate to TRUE if the subquery returns at least one record in its result set; otherwise FALSE.

Using EXISTS operator with SELECT statement

The SELECT statement in SQL is used to retrieve data from one or more tables in a database. We can use the EXISTS operator with a SELECT statement to check for the existence of rows that meet a certain condition.

Example

To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
); 

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Ramesh , 32,  Ahmedabad , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,  Delhi , 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  kaushik , 23,  Kota , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Chaitap , 25,  Mumbai , 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,  Hardik , 27,  Bhopal , 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,  Komal , 22,  MP , 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7,  Muffy , 24,  Indore , 10000.00 );

The table will be created as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Let us create another table CARS, containing the details such as id of the customer, name and price of the car, using the following query −

create table CARS(
   ID INT NOT NULL, 
   NAME VARCHAR(20) NOT NULL, 
   PRICE INT NOT NULL, 
   PRIMARY KEY(ID)
);

Using the INSERT statement, let us insert values into this table −

insert INTO CARS VALUES(2,  Maruti Swift , 450000);
insert INTO CARS VALUES(4,  VOLVO , 2250000);
insert INTO CARS VALUES(7,  Toyota , 2400000);

If you try to retrieve the contents of the table using the SELECT query the table is displayed as follows −

+----+--------------+---------+
| ID | NAME         | PRICE   |
+----+--------------+---------+
|  2 | Maruti Swift |  450000 |
|  4 | VOLVO        | 2250000 |
|  7 | Toyota       | 2400000 |
+----+--------------+---------+

Now, let us try to return the psts of the customers with the price of the car greater than 2,000,000 −

SELECT * FROM CUSTOMERS WHERE EXISTS (SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000);

Output

The result produced is as follows −

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
|  4 | Chaitap |  25 | Mumbai  |  6500.00 |
|  7 | Muffy    |  24 | Indore  | 10000.00 |
+----+----------+-----+---------+----------+

Using EXISTS operator with UPDATE statement

We can also use the SQL EXISTS operator with an UPDATE statement. It helps us to update rows in a table based on the existence of matching rows in another table.

Example

Suppose if we want to change the name of certain customers from the CUSTOMERS and CARS tables previously created, then this can be done using UPDATE statement. Here, we are modifying the name ‘Kushal’ of all the customers whose id is equal to the id of the cars table using EXISTS operator as follows −

UPDATE CUSTOMERS
SET NAME =  Kushal  
WHERE EXISTS (SELECT NAME FROM CARS WHERE CUSTOMERS.ID = CARS.ID);

Output

We get the following result. We can observe that 3 rows have been modified −

(3 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the Customers table −

Select * from customers;

The table is displayed as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Kushal   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Kushal   |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Kushal   |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

As we can see in the above table, the NAME of ‘Khilan’, ‘Chaitap’ and ‘Muffy’ has been updated to ‘Kushal’.

Using EXISTS operator with DELETE statement

The EXISTS operator can also be used with a DELETE statement in SQL to delete rows based on the existence of rows returned by a subquery.

Example

In here, we are trying to delete the row in the CUSTOMERS table whose id is equal to the id of the CARS table having price equal to ‘2250000’ −

DELETE FROM customers
WHERE EXISTS (SELECT * FROM cars WHERE cars.id = customers.id AND cars.price = 2250000);

Output

We get the following result. We can observe that 1 row has been deleted −

(1 row affected)

Verification

We can rectify the changes done in the CUSTOMERS table using the following query −

Select * from customers;

The table is displayed as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

As we can see in the above table, the row with the NAME ‘Chaitap’ has been deleted since the id of Chaitap in customers table was ‘4’, which is equal to the id of the cars table having price equal to ‘2250000’.

Using NOT operator with EXISTS operator

In SQL, the NOT EXISTS operator is used to select records from one table that do not exist in another table.

Syntax

Following is the basic syntax of NOT EXISTS operator in SQL −

WHERE NOT EXISTS (subquery);

Where, the subquery used is the SELECT statement.

Example

The below query gives the names of the customers who have not bought any car −

Select * from CUSTOMERS 
WHERE NOT EXISTS (SELECT * FROM CARS WHERE CUSTOMERS.ID = CARS.ID);

Output

Following output is obtained by executing the above query −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

Usage of SQL EXISTS operator

The EXISTS operator in SQL is widely used in real-pfe scenarios to filter records based on the existence of related data in another table. Some common use cases include −

Checking for the existence of records in a many-to-many relationship − The EXISTS operator can be used to check whether a record exists in a join table for a many-to-many relationship, for example, finding all customers who have purchased a particular product.

Filtering records based on the existence of related records − The EXISTS operator can be used to filter records based on the existence of related records in another table. For example, finding all orders that have associated order details.

Aggregating data based on the existence of related records − The EXISTS operator can be used to aggregate data based on the existence of related records. For example, finding the number of customers who have placed an order.

Optimizing queries − The EXISTS operator can be used to optimize queries by only returning the necessary data. For example, finding the first order for each customer without using a self-join.

These are just a few examples of how the EXISTS operator can be used in real-pfe scenarios. The specific use case will depend on the data and the requirements of the query.