Back to: MySQL Tutorials for Beginners and Professionals
EXISTS Operator in MySQL with Examples
In this article, I am going to discuss EXISTS Operator in MySQL with Examples. Please read our previous article where we discussed IN and BETWEEN Operators in MySQL. At the end of this article, you will understand the following pointers.
- What is EXISTS Operator in MySQL?
- Understanding EXISTS Operator in MySQL with Examples
- EXISTS Operator with SELECT Statement in MySQL
- Difference Between Exists and IN Operator in MySQL
- NOT EXISTS Operator in MySQL
- EXISTS Operator in MySQL with Update SQL Statements
- EXISTS Operator in MySQL with Delete SQL Statements
- EXISTS Operator in MySQL with INSERT SQL Statements
What is EXISTS Operator in MySQL?
The EXISTS operator in MySQL 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 checks 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 system won’t return the data rows.
EXISTS Operator Syntax in MySQL:
Where Column_names is the column name contains in the specified table, Table_name is the table name from which we are going to perform the EXISTS operator. 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 MySQL with Examples. We are going to use the following Employee and Projects tables to understand EXISTS Operator.
Please use the below SQL Script to create and populate the Employee and Projects tables with the required data.
CREATE DATABASE Company;
USE Company;
CREATE TABLE Employee (
Id INT PRIMARY KEY,
Name VARCHAR(45) NOT NULL,
Department VARCHAR(45) NOT NULL,
Salary FLOAT NOT NULL,
Gender VARCHAR(45) NOT NULL,
Age INT NOT NULL,
City VARCHAR(45) NOT NULL
);
INSERT INTO Employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'London');
INSERT INTO Employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'London');
INSERT INTO Employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'Mumbai');
INSERT INTO Employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');
CREATE TABLE Projects (
ProjectId INT PRIMARY KEY AUTO_INCREMENT,
Title VARCHAR(200) NOT NULL,
ClientId INT,
EmployeeId INT,
StartDate DATETIME,
EndDate DATETIME
);
INSERT INTO Projects ( Title, ClientId, EmployeeId, StartDate, EndDate) VALUES
('Develop ecommerse website from scratch', 1, 1003, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY)),
('WordPress website for our company', 1, 1002, NOW(), DATE_ADD(NOW(), INTERVAL 45 DAY)),
('Manage our company servers', 2, 1007, NOW(), DATE_ADD(NOW(), INTERVAL 45 DAY)),
('Hosting account is not working', 3, 1009, NOW(), DATE_ADD(NOW(), INTERVAL 7 DAY)),
('MySQL database from my desktop application', 4, 1010, NOW(), DATE_ADD(NOW(), INTERVAL 15 DAY)),
('Develop new WordPress plugin for my business website', 2, NULL, NOW(), DATE_ADD(NOW(), INTERVAL 10 DAY)),
('Migrate web application and database to new server', 2, NULL, NOW(), DATE_ADD(NOW(), INTERVAL 5 DAY)),
('Android Application development', 4, 1004, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY));
EXISTS Operator with SELECT Statement in MySQL
Let us understand how to use the Exists Operator with a SELECT statement in MySQL. Now we need to retrieve only those employees from the employee table who are currently working on any of the active projects. Here, the Id column of the employee table is EmployeeId in the Projects table.
In the following example, we use EXISTS operator with the Select statement to fetch all those employees who are currently working on any active 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 SQL statement “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 active projects as shown in the below image.
As we discussed the EXISTS operator returns either TRUE or FALSE. Let us prove this. Please execute the following SQL statement to check whether an employee with ID 1004 Exists in the Employee table or not.
SELECT EXISTS (SELECT * from Employee WHERE id=1004) AS Result;
Once you execute the above query, it will return the following output. As you can see in the below image, here, we get 1 as output which means TRUE, which indicates that Id=1004 exists in the employee table.
Now execute the following SQL Statement and observe the output.
SELECT EXISTS (SELECT * from Employee WHERE id=1024) AS Result;
Once you execute the above query, you will get the following output. As you can see in the below image, here, we get 0 as output which means FALSE, which indicates that Id=1024 does not exist in the employee table.
Difference Between Exists and IN Operator in MySQL
- EXISTS is used to check the existence of data in a subquery whereas IN is used to minimize the multiple OR conditions.
- The EXISTS operator stopes its further execution once it finds the first true occurrence whereas IN operator compares all the values present inside it.
- 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 MySQL
The EXISTS Operator in MySQL 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 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 assigned to any employees. Now execute the SQL statement and you should get the following result set.
EXISTS Operator in MySQL with Update SQL Statements
Let’s try to update the city of the employee whose client Id is 3. The SQL statement will be:
SET SQL_SAFE_UPDATES = 0; -- To Enable unsafe mode 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 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 MySQL with Delete SQL Statements
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 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 MySQL with INSERT SQL Statements
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. The SQL statement is:
CREATE TABLE Employee_Archive LIKE Employee;
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. The SQL query is:
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 Arithmetic Operators in MySQL with Examples. Here, in this article, I try to explain EXISTS Operator in MySQL with Examples. I hope you enjoy this EXISTS Operator in the MySQL article.