Introduction to EXISTS Condition
The EXISTS condition is a Boolean condition which checks for the existence of rows in a subquery. It returns TRUE if the subquery returns at least one row, otherwise it returns FALSE. The EXISTS condition is commonly used with the WHERE clause to filter rows based on the result of a subquery. It is useful for checking the existence of related records before performing certain operations such as INSERT, UPDATE, or DELETE.
Syntax:
query...
EXISTS (subquery)
query...
Example 1
The following query will output all the employees who are manager of any other employee.
Query:
SELECT * FROM employees e
WHERE EXISTS
(
SELECT 1 FROM employees m
where m.manager_id=e.employee_id
);
Output:
Explanation: In the above query we have fetched the information related all the employees who are manager of any other employee.
Example 2
Let’s find information about all the employees whose name starts with A.
Query:
SELECT * FROM employees e1
WHERE EXISTS
(
SELECT 1 FROM employees e2
WHERE e1.employee_id=e2.employee_id and e2.employee_name LIKE 'A%'
);
Output:
Explanation: In the above query we have fetched the information related all the employees whose name starts with A.
Example 3
Let’s finds out all the employee which have a manager assigned to them.
Query:
SELECT * FROM employees e1
WHERE EXISTS
(
SELECT 1 FROM employees e2
WHERE e1.employee_id=e2.employee_id and e2.manager_id IS NOT NULL
);
Output:
Explanation: In the above query, We have fetched the information related all the employee which have a manager assigned to them.
Difference Between EXISTS and IN in PostgreSQL
PostgreSQL is one of the most advanced general–purpose object–relational database management systems and is open-source. Being an open-source software, its source code is available under the PostgreSQL license, a liberal open-source license. In this article, we will learn about the EXISTS and IN Condition, various examples, and their differences too in PostgreSQL.