Description

The SQL keyword NOT IN is used within a WHERE condition to negate a condition that uses the IN keyword to compare a column value against a set of values.

It can be used in any statement that can contain a WHERE clause, like SELECT, UPDATE, DELETE, etc.,

SELECT With NOT IN

The below SQL fetches all the customers, not having customer IDs 1, 2, 3, 4, and 5.

SELECT *
FROM customers
WHERE customer_id NOT IN (1,2,3,4,5);

The below SQL fetches all the customers, not from countries "Spain", "Germany" and "France".

SELECT *
FROM customers
WHERE country NOT IN ("Spain", "Germany", "France");

SELECT Returns Values for NOT IN

The below SQL fetches all the customers from countries, to which suppliers are not available.

SELECT * FROM customers
WHERE country NOT IN (SELECT country FROM suppliers);

UPDATE With NOT IN

The below SQL updates all the customers, not having customer IDs 1, 2, 3, 4, and 5.

UPDATE customers
SET country = 'Spain'
WHERE customer_id NOT IN (1,2,3,4,5);

DELETE With NOT IN

The below SQL deletes all the customers, not having customer IDs 1, 2, 3, 4, and 5.

DELETE customers
WHERE customer_id NOT IN (1,2,3,4,5);

Related Links