SQL NOT

The NOT operator is used to query for items in a table that return NOT TRUE for some condition(s).

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column NOT condition;

The condition will usually involve comparison operators such as the following:

• =: Equal to
• >: Greater than
• <: Less than
• >=: Greater than or equal to
• <=: Less than or equal to
• !=: Not equal

Example 1

To return items without abc in their sequence:

SELECT *
FROM inventory
WHERE item_name NOT LIKE '%abc%';

Copy to clipboard
Copy to clipboard

Example 2

To return items not within the ('Cat', 'Dog', 'Fish') list:

SELECT *
FROM inventory
WHERE item_name NOT IN ('Cat', 'Dog', 'Fish');

Copy to clipboard
Copy to clipboard

Example 3

To return all items from the inventory table that do not exist within the store table by item_id:

SELECT *
FROM inventory
WHERE NOT EXISTS (
   SELECT *
   FROM store
   WHERE inventory.item_id = store.item_id);

Copy to clipboard
Copy to clipboard

Example 4

To return items that do not have a price within 10 and 12:

SELECT *
FROM inventory
WHERE price NOT BETWEEN 10 AND 12;

Copy to clipboard
Copy to clipboard

Example 5

To return items that do not contain NULL values in their sequence:

SELECT *
FROM inventory
WHERE item_name IS NOT NULL;

Copy to clipboard
Copy to clipboard