# PostgreSQL EXISTS Operator With Practical Examples

> In Postgres, the EXISTS operator takes a subquery as an argument and checks the existence of a record in the subquery. Consequently, it returns true or false.

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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-exists-operator-with-practical-examples/)