NOT Operator

The NOT operator is a negation operator that is applied on top of another operator or filtering logic. When the NOT operator is applied, we expect the all instanced where the filter logic is false to be returned in the output.

Some specific instances where you will see the NOT operator being used is with:

• LIKE
• IN
• NULL

For example, borrowing from our previous query which selected Customer information from specific countries using the IN operator, we can return the opposite of that by assigning the NOT operator to the IN operator. See the query below:

select *
from orders
where ship_country NOT IN ('Canada', 'Germany', 'France')

| order_id | customer_id | ship_country | ship_via | freight | order_date |
| -------- | ----------- | ------------ | -------- | ------- | ---------- |
| 3004     | 2004        | USA          | 3        | 45      | 2023-02-01 |
| 3006     | 2006        | UK           | 1        | 15.6    | 2023-02-10 |
| 3009     | 2009        | Spain        | 1        | 35.75   | 2023-02-25 |
| 3013     | 2013        | Italy        | 3        | 38.5    | 2023-03-15 |
| 3017     | 2017        | Netherlands  | 1        | 21.6    | 2023-04-01 |

In this example, notice how the results will now contain all records with all countries with the exception of Canada, Germany and, France.

A good reason to use NOT is when you need to filter information by eliminating a subset of data that is smaller than the rest. For example, we could have listed all the countries we wanted in our data and excluded Canada, Germany, and France. However, the query would be long and likely to be computationally expensive. Using the NOT operator makes the query easy and elegant.

We will see a few more examples of using the NOT operator in specific sections.