EXISTS in PostgreSQL

In this tutorial, I will be explaining how can we use the EXISTS in a subquery.
Let's get started...
EXISTS is a boolean operator, and it tests for the existence of rows in a subquery.
Syntax
Following is the syntax of using EXISTS:

EXISTS (subquery)

The EXISTS operator accepts a subquery as an argument.
Result of EXISTS is true if the subquery returns at least one row.
Result of EXISTS is false if the subquery returns no row.

Let's see an example:
For example we have following customer and payment tables in the database.

1) Find customers whose at least one payment is greater than 11

SELECT first_name,
       last_name
FROM customer c
WHERE EXISTS
    (SELECT 1
     FROM payment p
     WHERE p.customer_id = c.customer_id
       AND amount > 11 )
ORDER BY first_name,
         last_name;

In this, subquery checks payment table for each customer and find that if there is any customer that has at least one payment that has amount greater than 11.
The above query returns the following output:

2) NOT EXISTS
The NOT EXIST is opposite to EXISTS operator, that means the result of NOT EXISTS is true if the subquery returns no row and vice versa.

SELECT first_name,
       last_name
FROM customer c
WHERE NOT EXISTS
    (SELECT 1
     FROM payment p
     WHERE p.customer_id = c.customer_id
       AND amount > 11 )
ORDER BY first_name,
         last_name;

The above subquery checks payment table for each customer and find that if there is any customer that has not made any payment amount greater than 11.

The output of the query is as:

Conclusion
In this tutorial we explored the basic level use of EXISTS operator.