DEV Community

Malik M
Malik M

Posted on

1

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)
Enter fullscreen mode Exit fullscreen mode

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.

Image description

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;
Enter fullscreen mode Exit fullscreen mode

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:

Image description

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;
Enter fullscreen mode Exit fullscreen mode

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:

Image description

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

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

Top comments (0)

Heroku

This site is powered by Heroku

Heroku was created by developers, for developers. Get started today and find out why Heroku has been the platform of choice for brands like DEV for over a decade.

Sign Up

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay