PostgreSQL EXISTS Condition Explained with Examples

Master the PostgreSQL EXISTS condition with this comprehensive guide. Learn how to use EXISTS with SELECT, INSERT, UPDATE, and DELETE statements, including handling NULLs and NOT EXISTS scenarios. Practical examples demonstrate its usage for efficient data manipulation.

PostgreSQL EXISTS Condition

The PostgreSQL EXISTS Condition is used with the WHERE clause to evaluate the existence of rows in a subquery. This guide explains its functionality with examples using INSERT, SELECT, NOT EXISTS, NULL, UPDATE, and DELETE.

Introduction to PostgreSQL EXISTS Condition

The EXISTS condition in PostgreSQL works with SELECT, INSERT, UPDATE, and DELETE commands. It checks for the presence of data in a subquery and returns TRUE if records exist, otherwise FALSE.

TRUE is represented by 1.
FALSE is represented by 0.

Syntax


WHERE EXISTS (subquery);

The EXISTS condition evaluates if the subquery fetches any rows. Here's a more detailed syntax:

Syntax


SELECT column1, column2, ...
FROM table1
WHERE [NOT] EXISTS (
    SELECT 1
    FROM table2
    WHERE condition
);

The NOT operator negates the result of the EXISTS condition, returning TRUE when the subquery yields no rows.

Parameters

Parameter	Description
`column1, column2...`	Specifies the column names from the table.
`table_name`	The name of the table where the query operates.
`condition`	Specifies the criteria for filtering rows.
`subquery`	A nested query used within the `EXISTS` condition.

Key Notes

Using SELECT 1 instead of SELECT * in a subquery improves performance.
The EXISTS condition terminates once a match is found, saving processing time.

Examples of PostgreSQL EXISTS Condition

1. Basic Example with SELECT

Fetch clients with at least one record in client_details where client_salary exceeds 30,000:

Syntax


SELECT client_name, client_profession
FROM Client c
WHERE EXISTS (
    SELECT 1
    FROM Client_details cd
    WHERE cd.client_id = c.client_id
    AND cd.client_salary > 30000
)
ORDER BY client_name;

Output


client_name  | client_profession
------------ | -----------------
John Doe     | Engineer
Jane Smith   | Doctor

2. EXISTS Condition with INSERT

Insert records into the department table from employee table where conditions are met:

Syntax


INSERT INTO department (phone, address)
SELECT phone, address
FROM employee
WHERE EXISTS (
    SELECT 1
    FROM Jobs
    WHERE employee.emp_id = Jobs.job_id
);

Output


INSERT 0 4

3. NOT EXISTS Condition

Fetch clients not present in client_details:

Syntax


SELECT *
FROM Client
WHERE NOT EXISTS (
    SELECT 1
    FROM Client_details
    WHERE Client.client_id = Client_details.client_id
);

Output


client_id | client_name
--------- | -----------
5         | Michael Brown

4. EXISTS with DELETE

Delete records from the Course table based on matching course_id:

Syntax


DELETE FROM Course
WHERE EXISTS (
    SELECT 1
    FROM Course_categories
    WHERE Course.course_id = Course_categories.course_category_id
);

Output


DELETE 3

Conclusion

The PostgreSQL EXISTS condition is a powerful tool for evaluating row existence in subqueries, offering flexibility with SELECT, INSERT, UPDATE, and DELETE statements.

Follow On

TutorialsArena

PostgreSQL EXISTS Condition Explained with Examples