PostgreSQL EXISTS with SELECT Operator (Example)

What is EXISTS in PostgreSQL?

The PostgreSQL EXISTS operator tests whether a row(s) exists in a subquery. This means that the operator is used together with a subquery. The EXISTS operator is said to have been met when at least one row is found in the subquery. You can use this operation along with SELECT, UPDATE, INSERT, and DELETE statements.

PostgreSQL EXISTS Query Syntax

Here is the syntax for the PostgreSQL EXISTS statement:

WHERE EXISTS (subquery);

The above syntax shows that the EXISTS operator takes in an argument, which is a subquery. The subquery is simply a SELECT statement that should begin with a SELECT * instead of a list of column names or expressions.

PostgreSQL EXISTS with SELECT Statement

Let us see how to use a SELECT statement with the EXISTS operator. We have the following tables:

Book:

Price:

Run the following statement:

SELECT *
FROM Book
WHERE EXISTS (SELECT *
              FROM Price
              WHERE Book.id = Price.id);

This returns the following:

The above command should return all records in the Book table whose id matches the id of any records by the subquery. Only one id was matched. Hence, only one record was returned.

PostgreSQL EXISTS with INSERT Statement

We can use the EXISTS operator in an INSERT statement. We have the following 3 tables:

Book:

Price:

Price2:

We can then run the following statement:

INSERT INTO Price
SELECT id, price
FROM Price2
WHERE EXISTS (SELECT id
              FROM Book
              WHERE Book.id = Price2.id);

The Price table is now as follows:

The row with an id of 5 in the table named Price2 was matched. This record was then inserted into the Price table.

PostgreSQL EXISTS with UPDATE Statement

We can use the EXISTS operator in an UPDATE statement.

Run the following query:

UPDATE Price
SET price = (SELECT price
              FROM Price2
              WHERE Price2.id = Price.id)
WHERE EXISTS (SELECT id, price
FROM Price2
WHERE Price2.id = Price.id);

We are updating the price column of the Price table. Our aim is to have the prices of items sharing an id to be the same. Only one row was matched, that is, 5.

However, since the prices are equal, that is, 205, no update was made. If there was a difference, an update would have been made.

PostgreSQL EXISTS with DELETE Statement

A PostgreSQL DELETE statement can use the EXISTS operator. Here is an example:

DELETE FROM Price
WHERE EXISTS (SELECT *
              FROM Price2
              WHERE Price.id = Price2.id);

The Price table is now as follows:

The row with an id of 5 has been deleted.

PostgreSQL EXISTS Statement using pgAdmin

Beyond the SQL shell, the same EXISTS queries can be executed visually through the pgAdmin interface. Now let us see how these actions can be performed using pgAdmin:

With SELECT Statement

Below are the steps to use an EXISTS query in PostgreSQL with a SELECT statement using pgAdmin:

Step 1) Login to your pgAdmin account. Open pgAdmin and Login to your account using your credentials.

Step 2) Create a Demo Database.

1. From the navigation bar on the left, click Databases.
2. Click Demo.

Step 3) Type the below query in the query editor:

SELECT *
FROM Book
WHERE EXISTS (SELECT *
              FROM Price
              WHERE Book.id = Price.id);

Step 4) Click the Execute button.

It should return the following:

With INSERT Statement

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

1. From the navigation bar on the left, click Databases.
2. Click Demo.

Step 3) Type the query in the query editor:

INSERT INTO Price
SELECT id, price
FROM Price2
WHERE EXISTS (SELECT id
              FROM Book
              WHERE Book.id = Price2.id);

Step 4) Click the Execute button.

The Price table should now be as follows:

With UPDATE Statement

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

1. From the navigation bar on the left, click Databases.
2. Click Demo.

Step 3) Type the query in the query editor:

UPDATE Price
SET price = (SELECT price
              FROM Price2
              WHERE Price2.id = Price.id)
WHERE EXISTS (SELECT id, price
FROM Price2
WHERE Price2.id = Price.id);

Step 4) Click the Execute button.

The Price table should now be as follows:

With DELETE Statement

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

1. From the navigation bar on the left, click Databases.
2. Click Demo.

Step 3) Type the query in the query editor:

DELETE FROM Price
WHERE EXISTS (SELECT *
              FROM Price2
              WHERE Price.id = Price2.id);

Step 4) Click the Execute button.

The Price table should now be as follows:

Download the Database used in this Tutorial

FAQs