[not] exists (…)

1. EXISTS subquery is not supported inside join predicate

Exists checks whether or not the result of a subquery is empty.

SELECT *
  FROM customers
 WHERE NOT EXISTS (SELECT *
                     FROM customers.id = orders.customer_id
                  )

The example returns all customers that have not place an order yet—for whom there exists no order. Not exists is also said to be an anti join while exists is a semi join.

The columns selected in the subquery are irrelevant. The asterisk (*) is commonly used and does not mean “all columns” in this context. Exists can be used wherever conditions are allowed: where, having, case when, ….

Related

• Standard Features

  • E061-08, “EXISTS predicate”

  • T501, “Enhanced EXISTS predicate”

  • F671, “Subqueries in CHECK constraints”

• Product specific functions: exception join.

Normative references

The Exists is defined in ISO/IEC 9075-2:2023 as the mandatory feature E061-08.