In PostgreSQL, the NOT EXISTS operator negates the working of the EXISTS operator. This means the NOT EXISTS operator will return TRUE if the subquery retrieves zero row/record, and it will retrieve FALSE if the subquery returns one or more rows.

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

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

So, let’s get started!

What Does the NOT EXISTS Operator Do in PostgreSQL?

The syntax of the NOT EXISTS operator will be as follows:

SELECT col_1
FROM tab_1
WHERE NOT EXISTS(
SELECT 1 FROM tab_2
WHERE col_2 = table_1.col_1);

The syntax shows that the NOT EXISTS operator receives a subquery as an argument, and it will check the existence of some specific records in that subquery.

What Does the NOT EXISTS Operator Return in Postgres?

The NOT EXISTS operator retrieves a true or false:

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

Practical Implementation of Postgres NOT EXISTS Operator

Let’s implement the NOT EXISTS operator practically to get a profound understanding.

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

We have already created two tables named author_details and article_info in our database, whose details are depicted in the below-given snippets:

SELECT * FROM author_details;

Let’s demonstrate the content of the article_info table:

SELECT * FROM article_info;

From the output snippets, we can observe that the author_details table has six records, and the article_info table has eight records. Moreover, the author_id of the author_details table is a foreign key in the article_info table.

Suppose we want to find the authors whose experience is not more than two years:

SELECT author_name, author_experience
FROM author_details
WHERE NOT 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’s experience is more than two years. If the specified subquery retrieves one or more than one record, then the result of the NOT EXISTS operator will be “FALSE”. Else it will return “TRUE”:

The output authenticates the working of the NOT EXISTS operator, i.e., it retrieves the result opposite to the EXISTS operator.

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

If the specified subquery retrieves a NULL value, then the result of the NOT EXISTS Operator will be “FALSE”:

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

The result set proves that the EXISTS operator retrieves FALSE; hence the outer SELECT statement doesn’t retrieve any record.

Conclusion

In PostgreSQL, the NOT EXISTS operator negates the working of the EXISTS operator. This means the NOT EXISTS operator will return TRUE if the subquery retrieves zero row/record, and it will retrieve FALSE if the subquery returns one or more rows. This post demonstrated the working of PostgreSQL NOT EXIST Operator with practical examples.