WHERE Clause
The
To use the where clause, we will need to assign an operator that
evaluates the logic that you are implementing to filter your data. Some
common operators for the
- =: Equals to
- !=: Not Equals to
- >: Greater than
- >= : Greater than or Equals to
- <: Less than
- <= : Less than or Equals to
Below is our source table for this exercise:
| order_id | customer_id | ship_country | ship_via | freight | order_date |
| -------- | ----------- | ------------ | -------- | ------- | ---------- |
| 3001 | 2001 | Canada | 1 | 25.5 | 2023-01-15 |
| 3002 | 2002 | Germany | 2 | 18.75 | 2023-01-20 |
| 3003 | 2003 | France | 1 | 32.25 | 2023-01-25 |
| 3004 | 2004 | USA | 3 | 45 | 2023-02-01 |
| 3005 | 2005 | Canada | 2 | 22.8 | 2023-02-05 |
| 3006 | 2006 | UK | 1 | 15.6 | 2023-02-10 |
| 3007 | 2007 | Germany | 3 | 28.9 | 2023-02-15 |
| 3008 | 2008 | France | 2 | 19.45 | 2023-02-20 |
| 3009 | 2009 | Spain | 1 | 35.75 | 2023-02-25 |
| 3010 | 2010 | Canada | 3 | 41.2 | 2023-03-01 |
| 3011 | 2011 | Germany | 1 | 12.3 | 2023-03-05 |
| 3012 | 2012 | France | 2 | 26.85 | 2023-03-10 |
| 3013 | 2013 | Italy | 3 | 38.5 | 2023-03-15 |
| 3014 | 2014 | Canada | 1 | 16.75 | 2023-03-20 |
| 3015 | 2015 | Germany | 2 | 33.4 | 2023-03-25 |
| 3016 | 2016 | France | 3 | 29.95 | 2023-03-30 |
| 3017 | 2017 | Netherlands | 1 | 21.6 | 2023-04-01 |
| 3018 | 2018 | Canada | 2 | 37.25 | 2023-04-05 |
| 3019 | 2019 | Germany | 3 | 24.7 | 2023-04-10 |
| 3020 | 2020 | France | 1 | 42.15 | 2023-04-15 |Using the WHERE Clause
Suppose we wanted to query the orders table and only look at the
information from a specific country say, Germany. Here is how we can
implement filtering using the
select customer_id , ship_country , freight
from orders
where ship_country = 'Germany'| customer_id | ship_country | freight |
| ----------- | ------------ | ------- |
| 2002 | Germany | 18.75 |
| 2007 | Germany | 28.9 |
| 2011 | Germany | 12.3 |
| 2015 | Germany | 33.4 |
| 2019 | Germany | 24.7 |As we should expect, we can the returned output will return the specified columns with all records whose ship_country is Germany.
A few things to consider is that these operators do work on specific data types. The example above evaluated against TEXT/STRING to match ship_country. It would not make sense to use a greater than evaluation in such a case. Broadly speaking, you should be careful to use the appropriate operators for the filtering that you are trying to perform.
In the example below, I use the greater than or equal to operator (<=) to filter for all orders with freight values greater or equal to 17. Below is the query to execute this:
select customer_id , ship_country , freight
from orders
where freight <= 17| customer_id | ship_country | freight |
| ----------- | ------------ | ------- |
| 2006 | UK | 15.6 |
| 2011 | Germany | 12.3 |
| 2014 | Canada | 16.75 |As we can see, all the output contains records where the total invoice value less or equal to 17.
This concludes our discussion on using the WHERE clause. In the coming sections, we explore other operators available for filtering in the where clause and how to do more advanced filtering.