SQL - EXISTS Operator

---

---

The SQL EXISTS Operator

The SQL EXISTS operator checks if a certain record exists in a table by running a subquery. It is usually used in the WHERE clause of a SELECT statement to filter results based on related data in another table.

• EXISTS is a logical operator that returns TRUE or FALSE.
• It returns TRUE if the subquery returns at least one matching record.
• If TRUE, the main query will include those rows; if FALSE, it will exclude them.
• You can use EXISTS with SELECT, UPDATE, DELETE, or INSERT statements.

EXISTS is faster than other operators like IN because it stops searching as soon as it finds the first match.

Syntax

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

SELECT column1, column2, ...
FROM table_name
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.

SQL EXISTS with SELECT Statement

The SELECT statement in SQL is used to retrieve data from one or more tables in a database. By combining it with the EXISTS operator, you can check whether rows meeting a specific condition exist, and filter the results accordingly.

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 VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

The table will be created as follows:

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),
(4, 'VOLVO', 2250000),
(7, 'Toyota', 2400000);

The 'CARS' table obtained is as follows:

Now, we are retrieving the lists 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
);

The result produced is as follows:

SQL EXISTS with UPDATE Statement

The EXISTS operator can also be used with an UPDATE statement. This allows you to update rows in one table only when matching rows exist in another table, enabling conditional updates based on related data.

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 the EXISTS operator, as follows:

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

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

Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as shown below:

SELECT * FROM CUSTOMERS;

The table is displayed as follows:

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

SQL EXISTS with DELETE Statement

The EXISTS operator can be used with a DELETE statement to remove rows from a table only when matching rows exist in a related subquery, allowing conditional deletion based on specific criteria.

Example

In here, we are deleting 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
);

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

Query OK, 1 row affected (0.01 sec)

Verification

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

SELECT * FROM CUSTOMERS;

The table is displayed as follows:

As we can see in the above table, the row with the NAME 'Chaitali' has been deleted since the id of Chaitali in CUSTOMERS table was '4', which is equal to the id of the CARS table having price equal to '2250000'.

SQL NOT EXISTS Operator

The NOT EXISTS operator in SQL is used to select records from one table that do not have matching records in another table. It helps to identify rows where the specified condition in the subquery is not met.

Syntax

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

SELECT column1, column2, ...
FROM table_name
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
);

Following output is obtained by executing the above query:

SQL EXISTS with INSERT Statements

The EXISTS operator can also be used with an INSERT statement to conditionally insert rows into a table only if certain records exist in another table. This helps maintain data integrity by preventing duplicates or enforcing business rules during insertion.

Example

Suppose we want to insert a new car record into the CARS table only if the customer exists in the CUSTOMERS table. This can be achieved using EXISTS in the following way:

INSERT INTO CARS (ID, NAME, PRICE)
SELECT 5, 'Honda Civic', 1800000
WHERE EXISTS (
   SELECT 1 FROM CUSTOMERS WHERE ID = 5
);

This query inserts the new car record for the customer with ID 5 only if that customer exists in the CUSTOMERS table. If no matching customer is found, no insertion occurs.

Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

Verification

To verify the insertion, run the following SELECT query:

SELECT * FROM CARS WHERE ID = 5;

You get the following output:

SQL EXISTS with Correlated Subqueries

Correlated subqueries are subqueries that reference columns from the outer query. The EXISTS operator is often used with correlated subqueries to check for the existence of related rows for each row processed by the outer query.

Example

Here, we find all customers who have bought cars costing more than their salary. This can be done using EXISTS with a correlated subquery as follows:

SELECT * FROM CUSTOMERS C
WHERE EXISTS (
   SELECT 1 FROM CARS CA 
   WHERE CA.ID = C.ID AND CA.PRICE > C.SALARY
);

Here, the subquery checks for each customer whether there exists a car with a price greater than that customer's salary. We get the following output:

Nested EXISTS Queries

EXISTS operators can be nested inside one another to check if records exist in several related tables. This helps to find data that is connected through multiple conditions.

Example

Let us create another table DEALERS with columns ID (dealer id), CUSTOMER_ID, and DEALER_NAME as shown below:

CREATE TABLE DEALERS (
   ID INT NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   DEALER_NAME VARCHAR(50),
   PRIMARY KEY (ID),
   FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID)
);

Now, let us insert some data into the DEALERS table:

INSERT INTO DEALERS (ID, CUSTOMER_ID, DEALER_NAME) VALUES
(1, 2, 'AutoHub'),
(2, 4, 'AutoHub'),
(3, 7, 'SpeedMotors'),
(4, 5, 'AutoHub');

To find all customers who have bought cars through dealers named 'AutoHub', you can use nested EXISTS queries:

SELECT * FROM CUSTOMERS C
WHERE EXISTS (
   SELECT 1 FROM CARS CA 
   WHERE CA.ID = C.ID 
   AND EXISTS (
       SELECT 1 FROM DEALERS D 
       WHERE D.CUSTOMER_ID = C.ID AND D.DEALER_NAME = 'AutoHub'
   )
);

This query first checks if a customer has a car, then verifies if the customer bought the car through a dealer named 'AutoHub' using a nested EXISTS inside the first EXISTS. We get the following output:

Differences Between EXISTS and IN operators

The EXISTS and IN operators in SQL are both used to filter query results based on matching values in another table, but they work differently and have different performance characteristics:

Aspect	EXISTS	IN
Purpose	Checks if a subquery returns any rows	Checks if a value matches any value in a list or subquery result
Returns	TRUE or FALSE	Matches values in a list
Performance	Often faster with correlated subqueries; stops checking after finding first match	Can be slower with large lists or subqueries, as it processes all values
Subquery Handling	Uses correlated subqueries	Uses non-correlated subqueries or lists
NULL Handling	Ignores NULLs in subquery results	NULLs in the list can affect results
Use Cases	Best for existence checks and correlated conditions	Best for fixed lists or simple value comparisons

Usage of SQL EXISTS Operator

The EXISTS operator is useful when you want to check if related data is present in another table. Here are some simple examples of how it is used:

• Check many-to-many relationships:: Find customers who have bought a certain product by seeing if records exist in a linking table.
• Filter records: Get all orders that have matching order details.
• Count data: Find how many customers have made at least one order.
• Make queries faster: Get the first order of each customer without complicated joins.

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