In PostgreSQL, the EXISTS operator/clause checks the existence of a record within the subquery. It receives a subquery as an argument, and depending on the existence of the targeted row or record, it returns true or false.

The following aspects of the Postgres EXISTS operator will be discussed in this article with practical examples:

• What Does the EXISTS Operator Do in PostgreSQL?
• What Does the EXISTS Operator Return in Postgres?
• Practical Implementation of Postgres EXISTS Operator

So, let’s learn the working of the EXISTS operator.

What Does the EXISTS Operator Do in PostgreSQL?

Let's begin by understanding the syntax of the EXISTS operator:

EXISTS(subquery);

The syntax illustrates that the EXISTS operator receives a subquery as an argument and checks the existence of some specific records in that subquery.

What Does the EXISTS Operator Return in Postgres?

The EXISTS operator retrieves a true or false:

• If the specified subquery retrieves one or more than one record, then the result of the EXISTS operator will be “TRUE”.
• If the specified subquery doesn’t retrieve a record(i.e., zero rows), then the result of the EXISTS operator will be “FALSE”.
• If the specified subquery retrieves a NULL value, then the result of EXISTS Operator will be “TRUE”.

Practical Implementation of Postgres EXISTS Operator

Until now, we've covered the theoretical part of the Postgres EXISTS operator; now it's time to put it into practice.

Example #1: How Does the EXISTS Operator Work in Postgres?

We have two tables in our database named author_details and article_info. Let’s illustrate the content of each table one-by-one:

SELECT * FROM author_details;

The result set shows that the author-details table consists of six records. Let’s run the SELECT query to depict the article_info table:

SELECT * FROM article_info;

Suppose we want to find authors who have published at least one article and whose experience is greater than or equal to 2 years. For this purpose, we will use the EXISTS operator as follows:

SELECT author_name, author_experience
FROM author_details
WHERE EXISTS(SELECT 1
FROM article_info
WHERE article_info.author_id = author_details.author_id
AND author_experience >= 2);

• The subquery will check if the author has published at least one article (i.e., article_info.author_id = author_details.author_id) and if the author’s experience is more than two year.
• The subquery will return true if at least one author satisfies both conditions.
• The main/outer SELECT query will show the name and experience of all those authors who satisfies both conditions:

The result set proves the working of the EXISTS operator.

Example #2: What if the EXISTS Operator Returns False?

Suppose we want to find authors who have published at least one article and whose experience is greater than five years. To do this, we will utilize the EXISTS operator as follows:

SELECT author_name, author_experience
FROM author_details
WHERE EXISTS(SELECT 1
FROM article_info
WHERE article_info.author_id = author_details.author_id
AND author_experience >5);

The output states that there is not a single author who fulfills the specified criteria.

Example #3: How Does the EXISTS Operator Deal With the NULL Value in Postgres?

As discussed earlier, if the specified subquery retrieves a NULL value, then the result of EXISTS Operator will be “TRUE”. The below snippet will validate this concept:

SELECT author_name, author_experience
FROM author_details
WHERE EXISTS(SELECT NULL);

The result set proves that the EXISTS operator retrieves TRUE, and the SELECT statement fetches the records accordingly.

Conclusion

In PostgreSQL, the EXISTS operator/clause is used to check the existence of a record within the subquery. It receives a subquery as an argument and checks the existence of some specific records in that subquery. EXISTS is a Boolean operator, so it retrieves either true or false based on the existence of the row/record within the subquery. This post demonstrated the working of PostgreSQL EXIST Operator with practical examples.