Updated July 4, 2023
Introduction to SQL EXISTS
The EXISTS operator in standard query language (SQL) is a conditional operator that developers use in the WHERE clause of a query to determine whether the result set obtained from a correlated nested subquery contains any data or not. It evaluates the subquery and returns a boolean value of true or false. When the correlated subquery yields one or more rows, the condition is considered fulfilled, and the EXISTS operator returns TRUE. Conversely, if the subquery does not produce any rows, the condition is not fulfilled, and the EXISTS operator returns FALSE.
We will discuss the EXISTS conditional operator in great detail with the help of some examples in this post.
Syntax and Parameters
The basic syntax for using EXISTS operator in SQL queries is as follows:
Syntax:
SELECT column_name1, column_name2, ...
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);Parameters:
The parameters used in the syntax mentioned above are as follows :
- column_name1, column_name2, … : The columns or field names that have to be fetched for the final result have to be mentioned here.
- table_name : The database table from which the said columns must be fetched.
- (SELECT column_name FROM table_name WHERE condition) : Subquery, which will be evaluated as a condition. EXISTS operator returns TRUE if the subquery returns one or more rows.
Examples of SQL EXISTS
In order to illustrate the functionality of EXISTS in SQL, what could be better than trying a few examples on a dummy table? Therefore, let us create two database tables, “employee” and “departments” respectively. We can use the following CREATE table statements to create these tables.
Creating employee table:
CREATE TABLE employee
(
employee_id integer,
employee_name character varying(255),
department_id character varying(255),
salary numeric,
highest_qualification character varying(255)
)Creating departments table:
CREATE TABLE departments
(
department_id character varying(255),
department_name character varying(255),
location character varying(255),
manager_id integer
)We have successfully created both the tables, namely “employee” and “departments.”Now with the help of the given INSERT queries, let us insert a few records in both tables to work with.
INSERT query for employee tables.
INSERT INTO employee(
employee_id, employee_name, department_id, salary, highest_qualification)
VALUES (101,'Roy Bernard','D01',5000,'B.Sc'),
(102,'Gina Messenger','D01',6200,'M.Sc'),
(103,'Shelly H.','D02',4000,'B.A'),
(104,'Radhika Gupta','D02',6600,'MBA'),
(105,'Jim Perkins','D03',5000,'B.A'),
(106,'Erica Silverman','D03',7000,'MBA'),
(107,'Priyanka M','D01',5000,'B.Tech');INSERT query for departments table.
INSERT INTO departments(
department_id, department_name, location, manager_id)
VALUES ('D01','Research','Singapore',102),
('D02','Human Resource','Santa Monica',104),
('D03','Sales','New York',106);Selecting tables:
select * from employee;
select * from departments;
We are all set to try a few examples based on these tables using the EXISTS operator.
Use of EXISTS with SELECT statement
Use of exists with a select statement with an example:
Example #1
Find the employee_ids of all the employees who are not located at the New York office.
SELECT employee_id
FROM employee
WHERE EXISTS (SELECT location
FROM departments
WHERE employee.department_id = departments.department_id
AND location <> 'New York');
Example #2
Find the employee_ids, names, and highest qualifications of all the employees who are managers of a department in the office.
SELECT employee_id, employee_name, highest_qualification
FROM employee
WHERE EXISTS (SELECT manager_id
FROM departments
WHERE employee.employee_id = departments.manager_id);
Example #3
Find the department_id, name, and location of all the departments where managers earn more than $6500.
SELECT department_id, department_name,location
FROM departments
WHERE EXISTS (SELECT employee_id
FROM employee
WHERE employee.employee_id = departments.manager_id
AND salary > 6500);
Example #4
Find the department_id, name, and location of all the departments where the total expenditure on employee salary is less than $15000.
SELECT department_id, department_name,location
FROM departments
WHERE EXISTS (SELECT sum(salary)
FROM employee
WHERE employee.department_id = departments.department_id
GROUP BY department_id
HAVING sum(salary) < 15000);
Example #5
Find the employee_id, name, department_id, and highest qualification of all the employees who do not serve as managers of their respective departments.
SELECT employee_id, employee_name, department_id, highest_qualification
FROM employee
WHERE NOT EXISTS (SELECT manager_id
FROM departments
WHERE employee.employee_id = departments.manager_id);
Use of EXISTS with DELETE statement
Use of exists with delete statement with an example:
Example #6
Delete the details of all the employees who are from D02 or the Human Resources department.
DELETE
FROM employee
WHERE EXISTS (SELECT * FROM departments
WHERE departments.department_id = employee.department_id
AND department_id = 'D02');Next, let us check if the query has successfully deleted the records using the following SELECT query.
SELECT * FROM employee;
We can observe from the above table that the query has successfully deleted details pertaining to employees from D02 department.
Use of EXISTS with UPDATE statement
Use of exists with update statement with an example:
Example #7
Update department_id from D03 to D003 in the employee table for ‘sales’ department.
UPDATE employee
SET department_id = 'D003'
WHERE EXISTS (SELECT * FROM departments
WHERE departments.department_id = employee.department_id
AND department_name = 'Sales');We can clearly observe from the image below that department_id for the sales department has been successfully updated.
SELECT * FROM employee;
Conclusion
EXISTS is used as an operator in the WHERE clause of a SQL query to check if the result set obtained from the correlated nested subquery is empty or not. If the result set is empty, EXISTS returns FALSE. Otherwise, it returns TRUE.
Recommended Articles
We hope that this EDUCBA information on “SQL EXISTS” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
