EXISTS Operator in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

EXISTS Operator in Oracle with Examples

In this article, I am going to discuss EXISTS Operator in Oracle with Examples. Please read our previous article where we discussed SOME Operator in Oracle with Examples. At the end of this article, you will understand what is EXISTS Operator is and when and how to use EXISTS Operator in Oracle with Examples.

What is EXISTS Operator in Oracle?

The EXISTS operator in Oracle is used in combination with a subquery and is considered to be met if the subquery returns at least one row. That means the EXISTS operator is used to check the existence of a result of a subquery. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

The EXISTS condition is very similar to IN condition. In the IN condition, we directly define constant values to evaluate with data rows, while in EXISTS condition we use a subquery. If the subquery returns TRUE the main query is evaluated. If the subquery returns FALSE the oracle server won’t return the data rows.

Syntax of EXISTS Operator in Oracle:

Where Column_names is the column name contained in the specified table, Table_name is the table name from which we are going to perform the EXISTS operation. WHERE [NOT] EXISTS Tests the subquery for the existence of one or more rows. If a single row satisfies the subquery clause, it returns Boolean TRUE. When the subquery returns no matching rows the optional NOT keyword returns a Boolean TRUE. The subquery is usually the SELECT statement.

Note: The SQL statements that use the EXISTS condition are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query’s table. There are more efficient ways to write most queries, that do not use the EXISTS condition. If you observe the above SQL EXISTS operator syntax, we will get values where the subquery returns any data or rows otherwise it will not return any data.

Understanding EXISTS Operator in MySQL with Examples:

Let us understand EXISTS Operator in Oracle with Examples. We are going to use the following Employee and Projects tables to understand EXISTS Operator.

Please use the following SQL Script to create and populate the Employee and Projects tables with the required sample data.

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR2(10),
  Department VARCHAR2(10),
  Salary INT,
  Gender VARCHAR2(10),
  Age INT,
  City VARCHAR(10)
);

INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1002, 'Mary', 'HR', 45000, 'Female', 27, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', 26, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag', 'IT', 35000, 'Male', 25, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanka', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina', 'HR', 75000, 'Female', 26, 'Mumbai');

CREATE TABLE Projects (
     ProjectId INT PRIMARY KEY,
     Title VARCHAR2(10),
     ClientId INT,
     EmployeeId INT
);

INSERT INTO Projects (ProjectId, Title, ClientId, EmployeeId) VALUES (1, 'Project-1', 1, 1003);
INSERT INTO Projects (ProjectId, Title, ClientId, EmployeeId) VALUES (2, 'Project-2', 1, 1002);
INSERT INTO Projects (ProjectId, Title, ClientId, EmployeeId) VALUES (3, 'Project-3', 2, 1007);
INSERT INTO Projects (ProjectId, Title, ClientId, EmployeeId) VALUES (4, 'Project-4', 3, 1009);
INSERT INTO Projects (ProjectId, Title, ClientId, EmployeeId) VALUES (5, 'Project-5', 4, 1010);
INSERT INTO Projects (ProjectId, Title, ClientId, EmployeeId) VALUES (6, 'Project-6', 2, NULL);
INSERT INTO Projects (ProjectId, Title, ClientId, EmployeeId) VALUES (7, 'Project-7', 2, NULL);
INSERT INTO Projects (ProjectId, Title, ClientId, EmployeeId) VALUES (8, 'Project-8', 4, 1004);

EXISTS Operator with SELECT Statement in Oracle

Let us understand how to use the Oracle Exists Operator with a SELECT statement. Our business requirement is to fetch only those employees from the employee table who are currently working on any of the projects. Here, the Id column of the employee table is EmployeeId in the Projects table. In the below example, we use the Oracle EXISTS operator with the Select statement to fetch all those employees who are currently working on any =projects i.e. employees whose entry exists in the Projects table. Here, the EXISTS condition will check and return all records from the Employee table where there is at least one record in the Projects table with a matching employee Id.

SELECT * FROM Employee
WHERE EXISTS (SELECT * FROM Projects
WHERE Employee.Id = Projects.EmployeeID);

In the above query “SELECT * FROM employees WHERE EXISTS” is the main query and the statement inside the bracket is the subquery. The main statement will only work if the subquery statement returns TRUE. At the moment just focus on the subquery statement “SELECT * FROM Projects WHERE Employee.Id = Projects.EmployeeID“. We are requesting all the records from the Projects table where the EmployeeID column value matches to Id column value of the Employee table. Then the main query will be executed and return the data rows. Now run the SQL statement and you should get all the employee’s information working on the projects as shown in the below image.

Difference Between Exists and IN Operator in Oracle

1. The EXISTS operator is used to check the existence of data in a subquery whereas IN is used to minimize the multiple OR conditions.
2. The EXISTS operator stopes its further execution once it finds the first true occurrence whereas IN operator compares all the values present inside it.
3. EXISTS operator does not perform a comparison between parent query and child query whereas IN operator performs a comparison between parent query and child query.

NOT EXISTS Operator in Oracle

The EXISTS Operator in Oracle can also be used with the NOT operator. This will work as opposed to the EXISTS Operator. Let’s learn about NOT EXISTS Operator with examples. Now we will filter the data rows and display only the employees who have never been into any projects. The following example will return all records from the Employee table where there are no records in the Projects table for the matching employee ID column.

SELECT * FROM Employee
WHERE NOT EXISTS (SELECT * FROM Projects
WHERE Employee.Id = Projects.EmployeeID);

Please note, in this case, we are using NOT EXISTS so we will get only the data records from the projects table, where the Id did not match. So, this condition where Employee.Id = Projects.EmployeeID will be reverted and we will get only data records where the projects have not been assigned to any employees. Now execute the SQL statement and you should get the following result set.

EXISTS Operator in Oracle with Update Statements

Let’s try to update the city of the employee whose client Id is 3. The SQL statement will be:

UPDATE Employee SET City = "Bangalore"
WHERE EXISTS (SELECT 1 FROM Projects
   WHERE ClientId = 3
   AND Projects.EmployeeId = Employee.Id);

Here, the EXISTS operator in the WHERE clause gets only employees whose client Id is 3 and then updates its city to “Bangalore”. Once you execute the above update statement, now verify the database and see the City column of the employee whose Id is 1009 is updated as shown in the below image.

EXISTS Operator in Oracle with Delete Statement:

Let’s delete the employee data that exist in the project table from the employee table. For this, use EXISTS operator in the WHERE clause of the DELETE statement. The SQL statement is as follows:

DELETE FROM Employee WHERE EXISTS( 
  SELECT 1 FROM Projects
  WHERE projects.EmployeeId = employee.Id
  AND employee.city = 'Bangalore');

The above delete query would delete a data row from the table Employee, if and only if the column Id from the table Employee and EmployeeId from the table Projects is the same and there exists a value for the city as Bangalore in the employee table. As we know we have only one employee in that case with the Id 1009, once the above delete query is executed, then that employee will be deleted from the employee table and you can verify the same as shown in the below image.

Here you can see the employee data with city value as “Bangalore” is deleted successfully.

EXISTS Operator in Oracle with INSERT Statement:

Now let’s try to archive employees who don’t have any projects in a separate table. For this, first, create a new table for archiving the employee by copying the structure from the Employee table by executing the below query.

CREATE TABLE Employee_Archive AS SELECT * FROM Employee WHERE 1=0;

Once you execute the above Create Table statement the Employee_Archive table will be created successfully with the same structure as the Employee table without data. Now inserts employees who do not have any projects into the Employee_Archive table using the following INSERT statement.

INSERT INTO Employee_Archive
SELECT * FROM Employee
WHERE NOT EXISTS( 
   SELECT 1 FROM projects
   WHERE Projects.EmployeeId = Employee.Id 
);

Once you execute the above SQL Statement, now verify the Employee_Archive table by executing the below SQL Statement.

SELECT * FROM Employee_Archive;

The above SQL Query will give you the following output. Here, you can see the list of employees who do not have any projects.

In the next article, I am going to discuss Co-Related Subquery in Oracle with examples. Here, in this article, I try to explain EXISTS Operator in Oracle with Examples. I hope you enjoy this Oracle EXISTS Operator article.