On the back of my previous medium post. We will be learning how to do some basic filtering with SQL. Filtering through your database can prove useful when you want a specific subset of data instead of having to look over millions of data points.
Here is some of what we will learn today:
- Use the WHERE clause.
- Use the BETWEEN clause.
- Explain the concept of a NULL value.
Why filter?
- You can be specific about the data you want to retrieve.
- Reduce the number of records you retrieve.
- Increase query performance.
- Reduce strain on client-side applications.
- Governance limitations.
Here is an example of filtering.
SELECT column_name
FROM table_name
WHERE column_name operator value;You might be thinking about what an operator value is. If you are familiar with some basic concepts of math or programming you would know these as comparison operators or boolean operators.
Here are a few:
- = Equal.
- <> / != Not equal.
- > Greater than.
- < Less than.
- >= Greater than or equal to.
- <= Less than or equal to.
- BETWEEN between an inclusive range.
- IS NULL is a null value.
Filtering on a Single Condition
Here is how we can filter on a single condition.
SELECT
product_name,
unit_price,
supplier_id
FROM products
WHERE product_name = 'Tofu',This query would pull three column names defined in the query where the product name is equal to ‘Tofu’.
However, maybe we do not know the exact product_name we are after. In this case, we may want to filter products that are above a certain value. We can achieve this using this query:
SELECT
product_name,
unit_price,
supplier_id
FROM products
WHERE unitprice >= 75;This would return all the columns specified where the unit price is greater than or equal to 75.
Checking for Non-Matches
Perhaps you know that you want to query all of the products besides one or more particular products. We can do this using the following query:
SELECT
product_name,
unit_price,
supplier_id,
FROM products
WHERE product_name <> 'Alice Mutton';This would bring up all the results besides results where the product name is equal to ‘Alice Mutton’.
Filtering with a Range of Values
We can also filter with a range of values as shown below:
SELECT
product_name,
supplier_id,
unitsinstock,
FROM products
WHERE unitsinstock BETWEEN 15 and 80;You might have guessed it, but this filters the column names specified where units in stock are in a range between 15 and 80.
Filtering No Value
We might also want to find NULL values in our database. We can do this as follows:
SELECT
product_name,
supplier_id,
unitsinstock,
FROM products
WHERE product_name IS NULL;This produces results where the product_name has no data.
