![SQL Logo](https://www.tutorialspoint.com/sql/images/sql-mini-logo.jpg)
- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Database
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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-life 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, 'Chaitali', 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 | Chaitali | 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 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);
Output
The result produced is as follows −
+----+----------+-----+---------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+---------+----------+ | 4 | Chaitali | 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’, ‘Chaitali’ 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 ‘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’.
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-life 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-life scenarios. The specific use case will depend on the data and the requirements of the query.