NOT EXISTS

The SQL NOT EXISTS operator is used to check if a subquery returns no result. It is often used in combination with a correlated subquery, which is a subquery that depends on values from the outer query.

Syntax

The syntax for the NOT EXISTS operator is as follows:

SELECT column_name(s)
FROM table_name
WHERE NOT EXISTS (subquery);

The subquery must return no result for the NOT EXISTS operator to be true. If the subquery returns any result, the NOT EXISTS operator is false, and the outer query will not return any rows.

Example

Here is an example that demonstrates the use of the NOT EXISTS operator:

Suppose we have two tables, “orders” and “customers”, which are related by the “customer_id” column. We want to find all the customers who have not placed any orders.

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

In this example, the subquery selects all the orders that match each customer in the outer query. The NOT EXISTS operator negates the result of the subquery, so that the outer query returns only those customers for which no orders exist.

Note that the SQL NOT EXISTS operator can be used in combination with other operators, such as the IN operator or the EXISTS operator, to create more complex queries. It is a powerful tool for filtering data based on the absence of certain conditions, and can be particularly useful in cases where negative conditions are difficult to express using other SQL operators.