Filtering Numerical Data
Knowing how to filter your results would bring you far in analysing data sets.
In SQL, the clause allows you to filter numerical records (ie. integer or floating point).
Comparison and Logical Operators
The following table shows the various operators you can use for filtering numerical records (ie. integer or floating point):
| Operator | Definition | Example in Query | Interpretation |
|---|---|---|---|
| = | Equals to | field_name is equal to 2 | |
| !=, <> | Not equals to | field_name is not equal to 5 | |
| <, > | Less than, more than | , | field_name is less than 5, field_name is more than 10 |
| <=, >= | Less than or equal to, more than or equal to | , | field_name is equal to or less than 5, field_name is equal to or more than 2 |
| BETWEEN … AND … | Integer is within range of two values (inclusive) | field_name is between 100 and 124 (inclusive) | |
| NOT BETWEEN … AND … | Integer is not within range of two values (inclusive) | field_name is not between 100 and 124 (inclusive) | |
| IN (…) | Integer exists in a list | field_1 is either in 1, 2, or 3 | |
| NOT IN (…) | Integer does not exist in the list | field_1 is not in 4, 5, or 6 |
Example 1: WHERE with
Bob is analysing the inventory report for the year and wants to pull a report showing the inventory information for product 123 only.
Example 2: WHERE with
A journalist for the Olympics 2020 is writing an article on gold medalists for the past 3 decades and requesting data from the Olympics team.
Example 3: WHERE with and
An accountant is running some sales numbers for the sales department and needs help getting the data containing sales above $10,000, but not more than $20,000 (inclusive).
Watch out! Although similar, using is not going to give you the same results.
But, what are the differences?
- means the total price is more than $10,000, but less than or equal to $20,000.
- means the total price is more than or equal to $10,000, but less than or equal to $20,000.
Alright, it's time for you to test your knowledge!
Practice Questions
Next Lesson
Filtering String-Text Data