PostgreSQL EXISTS with Examples

Details

What is PostgreSQL Exists?

The 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.

In this PostgreSQL Tutorial, you will learn the following:

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.

With SELECT Statement

Let 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.

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.

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.

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.

With pgAdmin

Now let's see how these actions can be performed using pgAdmin.

With SELECT 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: 

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:

Summary

  • The EXISTS operator tests whether a row(s) exists in a subquery.
  • It is used with a subquery and is said to have been met when the subquery returns at least one row.
  • It is used together with SELECT, UPDATE, INSERT, and DELETE statements.

Download the Database used in this Tutorial

 

YOU MIGHT LIKE: