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.

Setting Up Environment

For the demonstration in the following sections, I will first create an employee table and insert some values in it. The following query first creates an employee table that contains information such as id, name, Oracle develops it, and manager_id. Later in the query I inserted some sample data in the table.

Query:

CREATE TABLE employees
( 
    employee_id INTEGER NOT NULL,
    employee_name VARCHAR(50) NOT NULL,
    manager_id INTEGER
);

INSERT INTO employees VALUES (1, 'Jack', 2);
INSERT INTO employees VALUES (2, 'Jill', NULL);
INSERT INTO employees VALUES (3, 'Jim', NULL);
INSERT INTO employees VALUES (4, 'Bill', 3);
INSERT INTO employees VALUES (5, 'Ben', NULL);
INSERT INTO employees VALUES (6, 'Alex', 2);
INSERT INTO employees VALUES (7, 'Andrew', 5);
INSERT INTO employees VALUES (8, 'Chris', 5);

Output:

Output

Explanation: Our employees table has been created as shown in image.

Introduction to IN Condition

The IN condition is a logical condition which is used in queries to specify a range of values. It is commonly used in the WHERE clause of a SELECT, UPDATE, DELETE, or MERGE statement to filter rows based on a specific list of values.With the help of IN condition we can can make queries more simple and readable when dealing with multiple values or subqueries.

Syntax:

query...
IN (subquery/value_list)
query...

Example 1

Let’s find out all employees who have an odd employee_id.

Query:

SELECT * FROM employees
WHERE employee_id IN 
(
    SELECT employee_id FROM employees
    WHERE employee_id%2=1
);

Output:

Output

Explanation: In the above query, We have fetched the information about all employees who have an odd employee_id.

Example 2

Let’s fetch the information about all the employees whose name starts with A.

Query:

SELECT * FROM employees
WHERE employee_id IN 
(
    SELECT employee_id FROM employees
    WHERE employee_name LIKE 'A%'
);

Output:

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
WHERE employee_id IN 
(
    SELECT employee_id FROM employees
    WHERE manager_id IS NOT NULL
);

Output:

Output

Explanation: In the above query, We have fetched the information related all the employee which have a manager assigned to them.

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:

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:

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:

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 vs IN Operator

The following are some of the differences between EXISTS and IN conditions in PostgreQL:

EXISTS	IN
Used to check for existence	Used to check for membership
SQL engine stops as soon as one true value found	SQL engine checks all the values provided
EXISTS is faster when result of subquery is large	IN is faster if result of subquery is small

Technical Example

Let’s go through a technical example for our understanding. We will use a above table and the records in them. Just for completion the following is the statement used to create the table and insert the records.

Query:

CREATE TABLE employees
( 
    employee_id INTEGER NOT NULL,
    employee_name VARCHAR(50) NOT NULL,
    manager_id INTEGER
);

INSERT INTO employees VALUES (1, 'Jack', 2);
INSERT INTO employees VALUES (2, 'Jill', NULL);
INSERT INTO employees VALUES (3, 'Jim', NULL);
INSERT INTO employees VALUES (4, 'Bill', 3);
INSERT INTO employees VALUES (5, 'Ben', NULL);
INSERT INTO employees VALUES (6, 'Alex', 2);
INSERT INTO employees VALUES (7, 'Andrew', 5);
INSERT INTO employees VALUES (8, 'Chris', 5);

In this example we are going to find out all the employees which are manager of any other employee.

Firstly, let use a simple query using IN to find the desired result. The following query selects all the employees which are managers using a subquery.

Query:

SELECT * FROM employees
WHERE employee_id IN 
(
    SELECT manager_id FROM employees
);

Output:

Output

Explanation: As we can see that the simple IN query was able to successfully identify that Jill, Jim and Ben are managers of other employees. Let us now try to check this using EXISTS. In the following query we select only those employees which are manager of some employee in the inner query and then output them.

Query:

SELECT * FROM employees e
WHERE EXISTS 
(
    SELECT 1 FROM employees m
    where m.manager_id=e.employee_id
);

Output:

Output

Explanation: As we can see from the above image the EXISTS statement produced output just as the IN statement.

Conclusion

In this article we went through IN and EXISTS operators and understood what they are actually. We also looked at the differences between them and we finally wrapped the article with a technical example giving a sense of how we can use the concepts we learned in this article in real-life. While both operators are essential for filtering data and differs in terms of performance. EXISTS is more efficient when dealing with large result sets, whereas IN may be faster for smaller sets.