Description

The SQL keyword NOT EXISTS is used to negate a condition that uses EXISTS keyword, to test the non-existence of records in a subquery.

  • The NOT EXISTS keyword returns FALSE if the subquery returns one or more records.
  • The NOT EXISTS keyword returns TRUE if the subquery returns no records.

The below SQL fetches all the customers, not having orders of value more than 2000.

SELECT *
FROM customers
WHERE NOT EXISTS (SELECT * FROM orders WHERE orders.customer_id = customers.customer_id AND order_value > 2000);

Similarly, the below SQL fetches all the customers, not having orders of value less than 2000.

SELECT *
FROM customers
WHERE NOT EXISTS (SELECT * FROM orders WHERE orders.customer_id = customers.customer_id AND order_value < 2000);

Similarly, the below SQL fetches all the customers, not having orders of value equal to 2000.

SELECT *
FROM customers
WHERE NOT EXISTS (SELECT * FROM orders WHERE orders.customer_id = customers.customer_id AND order_value = 2000);

Related Links