IN Operator

The IN operator is generally used to filter rows whose column values are contained within a list of pre-defined matching values.

When we discussed the OR operator, we demonstrated that we can filter for multiple values. The exact query is given below that will return values with Countries specified in the OR operator. 

select customer_id , ship_country , ship_via, freight
from orders
where (ship_country = 'Canada' or ship_country = 'Germany' OR ship_country = 'France' )
| customer_id | ship_country | ship_via | freight |
| ----------- | ------------ | -------- | ------- |
| 2001        | Canada       | 1        | 25.5    |
| 2002        | Germany      | 2        | 18.75   |
| 2003        | France       | 1        | 32.25   |
| 2005        | Canada       | 2        | 22.8    |
| 2007        | Germany      | 3        | 28.9    |
| 2008        | France       | 2        | 19.45   |
| 2010        | Canada       | 3        | 41.2    |
| 2011        | Germany      | 1        | 12.3    |
| 2012        | France       | 2        | 26.85   |
| 2014        | Canada       | 1        | 16.75   |
| 2015        | Germany      | 2        | 33.4    |
| 2016        | France       | 3        | 29.95   |
| 2018        | Canada       | 2        | 37.25   |
| 2019        | Germany      | 3        | 24.7    |
| 2020        | France       | 1        | 42.15   |

An alternative method to get to the same results would be to use the IN operator. Below, is the query that will return the same results as the above but using the IN operator instead. 

select customer_id , ship_country , ship_via, freight
from orders
where ship_country IN ('Canada', 'Germany', 'France')
| customer_id | ship_country | ship_via | freight |
| ----------- | ------------ | -------- | ------- |
| 2001        | Canada       | 1        | 25.5    |
| 2002        | Germany      | 2        | 18.75   |
| 2003        | France       | 1        | 32.25   |
| 2005        | Canada       | 2        | 22.8    |
| 2007        | Germany      | 3        | 28.9    |
| 2008        | France       | 2        | 19.45   |
| 2010        | Canada       | 3        | 41.2    |
| 2011        | Germany      | 1        | 12.3    |
| 2012        | France       | 2        | 26.85   |
| 2014        | Canada       | 1        | 16.75   |
| 2015        | Germany      | 2        | 33.4    |
| 2016        | France       | 3        | 29.95   |
| 2018        | Canada       | 2        | 37.25   |
| 2019        | Germany      | 3        | 24.7    |
| 2020        | France       | 1        | 42.15   |

As to be expected, the output is of all records whose Country matches the pre-defined list of countries within the INinlinecode> operator.

This concludes our discussion on the IN operator. In the next section, we will look at the negation operator NOT.