> This page location: Subquery > EXISTS Operator
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL EXISTS Operator

**Info:** The EXISTS operator works the same way in any PostgreSQL deployment, so everything you learn here carries over to Postgres running anywhere. If you're an enterprise that wants the best managed cloud Postgres for the AI era, [Lakebase](https://www.databricks.com/product/lakebase) delivers high performance, strong security, and native integration with the Lakehouse. If you're a developer or startup that needs to ship and scale fast, [Neon](https://neon.com) gives you the most productive Postgres platform to build on.

**Summary**: in this tutorial, you will learn how to use the PostgreSQL `EXISTS` operator to test for the existence of rows in a subquery.

## Introduction to PostgreSQL EXISTS operator

The `EXISTS` operator is a boolean operator that checks the existence of rows in a [subquery](https://neon.com/postgresql/tutorial/postgresql-subquery).

Here's the basic syntax of the `EXISTS` operator:

```sql
EXISTS (subquery)
```

Typically, you use the `EXISTS` operator in the [`WHERE`](https://neon.com/postgresql/tutorial/postgresql-where) clause of a `SELECT` statement:

```sql
SELECT
  select_list
FROM
  table1
WHERE
  EXISTS(
    SELECT
      select_list
    FROM
      table2
    WHERE
      condition
  );
```

If the subquery returns at least one row, the `EXISTS` operator returns `true`. If the subquery returns no row, the `EXISTS` returns `false`.

Note that if the subquery returns `NULL`, the `EXISTS` operator returns `true`.

The result of `EXISTS` operator depends on whether any row is returned by the subquery, and not on the row contents. Therefore, columns that appear in the `select_list` of the subquery are not important.

For this reason, the common coding convention is to write `EXISTS` in the following form:

```sql
SELECT
  select_list
FROM
  table1
WHERE
  EXISTS(
    SELECT
      1
    FROM
      table2
    WHERE
      condition
  );
```

To negate the `EXISTS` operator, you use the `NOT EXISTS` operator:

```sql
NOT EXISTS (subquery)
```

The `NOT EXISTS` operator returns `true` if the subquery returns no row or `false` if the subquery returns at least one row.

In practice, you often use the `EXISTS` operator in conjunction with the [correlated subqueries](https://neon.com/postgresql/tutorial/postgresql-correlated-subquery).

## PostgreSQL EXISTS examples

We will use the following `customer` and `payment` tables in the [sample database](../postgresql-getting-started/postgresql-sample-database) for the demonstration:

![customer and payment tables](https://neon.com/postgresqltutorial/customer-and-payment-tables.png)

### 1) Basic EXISTS operator example

The following example uses the `EXISTS` operator to check if the payment value is zero exists in the `payment` table:

```sql
SELECT
  EXISTS(
    SELECT
      1
    FROM
      payment
    WHERE
      amount = 0
  );
```

Output:

```text
 exists
--------
 t
(1 row)
```

### 2) Using the EXISTS operator to check the existence of a row

The following example uses the `EXISTS` operator to find customers who have paid at least one rental with an amount greater than 11:

```sql
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;
```

The query returns the following output:

```text
 first_name | last_name
------------+-----------
 Karen      | Jackson
 Kent       | Arsenault
 Nicholas   | Barfield
 Rosemary   | Schmidt
 Tanya      | Gilbert
 Terrance   | Roush
 Vanessa    | Sims
 Victoria   | Gibson
(8 rows)
```

In this example, for each customer in the `customer` table, the subquery checks the `payment` table to find if that customer made at least one payment (`p.customer_id = c.customer_id`) and the amount is greater than 11 ( `amount > 11`)

### 2) NOT EXISTS example

The following example uses the `NOT EXISTS` operator to find customers who have not made any payment more than 11.

```sql
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;
```

Here is the output:

```
first_name  |  last_name
-------------+--------------
 Aaron       | Selby
 Adam        | Gooch
 Adrian      | Clary
 Agnes       | Bishop
 Alan        | Kahn
...
```

### 3) EXISTS and NULL example

The following example returns all rows from the `customers` table because the subquery in the `EXISTS` operator returns `NULL`:

```sql
SELECT
  first_name,
  last_name
FROM
  customer
WHERE
  EXISTS(
    SELECT NULL
  )
ORDER BY
  first_name,
  last_name;
```

Output:

```
first_name  |  last_name
-------------+--------------
 Aaron       | Selby
 Adam        | Gooch
 Adrian      | Clary
 Agnes       | Bishop
...
```

## Summary

- Use the PostgreSQL `EXISTS` to check the existence of rows in a subquery.

---

## Related docs (Subquery)

- [Subquery](https://neon.com/postgresql/tutorial/subquery)
- [Correlated Subquery](https://neon.com/postgresql/tutorial/correlated-subquery)
- [ANY Operator](https://neon.com/postgresql/tutorial/any)
- [ALL Operator](https://neon.com/postgresql/tutorial/all)