NOT IN

In the previous sections we have looked at IN and NOT operators separators. The IN operator provides a filtering option when we have a pre-defined finite set of values we wish to filter for. The NOT operator returns all records for which the filter evaluates to false. Using the NOT IN filter will thus return all records whose values are not in the pre-defined IN operator.

To demonstrate this with an example, let's use the query we developed before that filtered the records of customers based on a pre-defined list of countries they are from.

select customer_id, first_name, city , country
FROM customers
WHERE country in ('Canada', 'Germany', 'France', 'USA')
LIMIT 15

| customer_id | first_name | city     | country |
| ----------- | ---------- | -------- | ------- |
| 2001        | Alice      | New York | USA     |
| 2003        | Carol      | Toronto  | Canada  |
| 2005        | Emma       | Paris    | France  |
| 2006        | Frank      | Berlin   | Germany |
| 2027        | Aaron      | Munich   | Germany |

The resulting output will only have customer information if they belong to the countries specified in the query.

Now let's implement a NOT IN operator on the same query with the same country list.

select customer_id, first_name, city , country
from customers
where country not in ('Canada', 'Germany', 'France', 'USA')
LIMIT 10

| customer_id | first_name | city        | country     |
| ----------- | ---------- | ----------- | ----------- |
| 2002        | Bob        | London      | UK          |
| 2004        | David      | Sydney      | Australia   |
| 2007        | Grace      | Tokyo       | Japan       |
| 2008        | Henry      | Madrid      | Spain       |
| 2009        | Ivy        | Rome        | Italy       |
| 2010        | Jack       | Amsterdam   | Netherlands |
| 2011        | Kate       | Stockholm   | Sweden      |
| 2012        | Liam       | Oslo        | Norway      |
| 2013        | Mia        | Barcelona   | Spain       |
| 2014        | Noah       | Mexico City | Mexico      |

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

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.