PostgreSQL EXISTS Operator

---

---

In PostgreSQL, an EXIST is used to check if the subquery returns any rows. This returns TRUE if the subquery result has one or more rows; otherwise, it returns FALSE.

A subquery is a simple query inside another query. For example − SELECT, INSERT, UPDATE, or, DELETE that is used to retrieve data by the parent/outer query.

The basic structure of the subquery −

-- Outer Query
SELECT column1, column2, ... 
FROM table_name 
WHERE column_name = (
    -- subquery
    SELECT column_name 
    FROM another_table 
    WHERE condition
);

Syntax

Following is the syntax of PostgreSQL EXISTS operator −

EXISTS (subquery)

How EXISTS Operator works?

Here, we are providing three points that cover the usage of EXISTS operator.

• If the subquery returns at least one row, EXISTS results to TRUE.
• If the subquery returns no rows, EXISTS results to FALSE.
• It is mostly used with WHERE clauses to filter records based on related data.

Example of PostgreSQL EXISTS Operator

Now, we will explain to you how the EXISTS operator is used in PostgreSQL. Look carefully at the table given below, where we have kept the department of the employee Krishna Murari as NULL in the first row. We will use the EXISTS operator to filter out this row.

Follow the below steps to understand each query in postgreSQL.

Step 1: Create the employees Table and Insert Data

To run the below query, let us create an employees table and insert the above data as provided.

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary INT
);

INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'Krishna', 'Murari', NULL, 50000),
(2, 'Sudhir', 'Pandey', 'HR', 60000),
(3, 'Madhav', 'Krishnan', 'IT', 75000),
(4, 'Raghu', 'Goswami', 'Sales', 55000),
(5, 'Iswar', 'Singh', 'IT', 80000);

Step 2: Execute the basic EXIST query

Our main objective is to find the employees who belongs to a department.

SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM employees sub WHERE sub.department IS NOT NULL AND sub.employee_id = e.employee_id
);

Here,

• "e" is an alias for the employees table (outer query) whereas sub is also an alias of the table but named by subquery.
• The outer query selects the list of data from the employees table and applies NOT NULL with the inner query (sub.department) to check the missing data. So as a result, "Krishna Murari" from the employees table is not included since he has NULL in the department column.

The above query produces the following result −

Step 3: Using NOT Exist

Here, we find the employees who do not belong to a department.

SELECT * FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM employees sub WHERE sub.department IS NOT NULL AND sub.employee_id = e.employee_id
);

The above query produces the following result −

Therefore, all of the implemented queries show the basic usage of the EXIST operator in PostgreSQL.