How to filter data in SQL? WHERE, LIKE, and BETWEEN Example

In SQL, you can filter data using the WHERE clause in your queries. The WHERE clause allows you to specify a condition that must be met for a row to be included in the result set. You can use basic conditional operator, logical operator to filter based upon any condition as well as LIKE operator filter based upon any pattern. You can also filter on rang using IN and BETWEEN clause whose examples we will see in this article. 

You may be thinking why is the filtering of data needed? Because you utilize the WHERE clause when changing data with DELETE and UPDATE statements, if you've been following my Database Fundamentals posts, you've already seen it. 

It's also been used to restrict the values returned from a SELECT operation multiple times in this series, which is also called server side filtering or database side filtering as it save bandwidth when transferring data from server to client.

The JOIN criteria and the WHERE clause criteria are the most common areas where individuals get stuck using T-SQL. This happens because they don't fully comprehend what the filters and operators they're utilizing will accomplish. They end up returning too much data because they didn't employ or misapplied the WHERE clause.

They also filter out much too much information. Keep in mind that there are many more functions than those covered in this series. While these basic operators will meet the majority of your demands, they will not meet all of them. 

Don't be scared to look up new functions in the Microsoft SQL Server manual. With that in mind, let's take a closer look at how the WHERE clause works to assist you filter data and preventing some of the more serious problems.

Here's a basic syntax for filtering data in SQL:

SELECT column1, column2, ...

FROM table_name

WHERE condition;

3 Ways to Filter Rows in SQL? WHERE, LIKE, and BETWEEN Example

Now, let's see the three main ways to filter rows in a SQL query by using WHERE, LIKE, and BETWEEN clauses. While WHERE and HAVING are used for filtering, the LIKE and BETWEEN clause provides them the conditions on which you can filter rows in SQL Query 

1. Filtering data in SQL using WHERE Clause

So far, almost all of the operators in the WHERE clause have been equality operators. In a previous blog article, you tried using the greater than an operator. You have a wide range of operators to choose from. 

You may also use logic to combine operations to improve your ability to control exactly what information is returned.

What if you only wanted to locate persons whose first names began with the letter E? You could just pick all of the data and perform an ORDER BY on the first name, then scroll down to where E should be and check what was there, but you've wasted a lot of time. Instead, you may utilize a new LIKE operator.

Here's a breakdown of how you can use the WHERE clause to filter data:

1. Filtering using basic Comparison Operators:

=: Equal to

<> or !=: Not equal to

<: Less than

>: Greater than

<=: Less than or equal to

>=: Greater than or equal to

Example : SELECT * FROM employees WHERE salary > 50000;

2. Filtering using Logical Operators:

AND: Both conditions must be true

OR: At least one condition must be true

NOT: Negates a condition

Example: 

SELECT * FROM orders WHERE (status = 'Shipped' AND total_amount > 1000) OR (status = 'Processing');

3. Pattern Matching with LIKE:

%: Represents zero or more characters

_: Represents a single character

Example:

SELECT * FROM products WHERE product_name LIKE 'App%';

4. Filtering based on a List of Values:

Use the IN operator to specify a list of values.

Example:

SELECT * FROM customers WHERE country IN ('USA', 'Canada', 'UK');

5. Checking for NULL Values:

Use IS NULL or IS NOT NULL to check for NULL values.

Example:

SELECT * FROM orders WHERE ship_date IS NULL;

6. Range Conditions:

Use the BETWEEN operator for a range of values.

Example:

SELECT * FROM products WHERE price BETWEEN 50 AND 100;

These are just some examples of how you can filter data in SQL. The WHERE clause provides a powerful mechanism to tailor your queries to specific criteria, allowing you to retrieve only the data that meets your specified condition

Let's create another table from scratch and start filtering data. Create an employee table with the following fields and enter some dummy data. you can enter any dummy data you like. Here, we have entered the data as shown below:

2. LIKE operator in SQL

Now, let's query the employees whose notes have 'Education' contained in them. how will you query it? will you manually check them? that is quite boresome. Let's use the like operator.

SELECT * FROM [Employees] WHERE Notes like '%Education%';

The results would be something like this :

There is a slew of additional functions and wild cards that may be used with the LIKE operator to do a variety of pattern matching tasks.

3. IN operator in SQL

The IN clause is another approach to filter information in the WHERE clause. This is used when you have a tiny list of values and want to return data if it matches any of them. This query will search all of the IN operator's values for any matches.


SELECT * FROM [Employees] WHERE EmployeeID IN (1,5,7);


The results would be something like this :

4. BETWEEN operator in SQL

The BETWEEN operator is another approach to retrieving particular information. Because you can seek a range of values by using BETWEEN to establish upper and lower boundaries like this, it's typically used to locate dates or integers.

SELECT * FROM [Employees] WHERE EmployeeID BETWEEN 5 and 7;

The results would be something like this :






This will produce two rows of information if you run it on the current data set. The important thing to remember is that they are all-inclusive ranges. A match is defined as any value that matches or exceeds the first value and any value that matches or is less than the second value.

If you want exclusive ranges, combine the larger than and less than operators. You can also use the NOT operator to invert the selection so that only values outside the given range are returned.

5. Logical operators(AND / OR) in SQL

All of these operators may be combined using logical structures like AND, OR, and the usage of parenthesis. For example, let's say we want a list of employees whose birth is between 1952 and 1962 along with having a 'BA' in their Notes. how would it be done? Let's see.

SELECT * FROM [Employees] 
WHERE BirthDate 
BETWEEN '1952-01-01' and '1962-12-31' 
and Notes LIKE '%BA%';

The results would be something like this :





This will narrow down the options to only those that fulfill both requirements. You may, however, alter the rationale. Using the OR clause, you may search for data combinations.

SELECT EmployeeID FROM [Employees] 
WHERE BirthDate 
BETWEEN '1952-01-01' and '1962-12-31' OR Notes LIKE '%BA%';

That's all about how to filter data in SQL using WHERE, BETWEEN, LIKE, and other clauses. All of this only scrapes the surface of what T-SQL can do when it comes to data filtering. The principles and functions of a WHERE clause will be expanded in the following blog article in the series.

Other SQL and Database Articles you may like:

• Difference between UNION and UNION ALL in SQL
• MySQL vs NoSQL comparison
• Difference between Self and Equi join in SQL
• 10 Examples of ALTER command in SQL
• What are T-SQL Window Functions?
• How to use Stored Procedure in SQL?
• 4 Examples of CASE expression in SQL Server
• How to create and rollback transaction in database?
• How to convert String to Date in SQL?
• How to use WHERE and HAVING clause in SQL
• How to find top 10 Records in SQL

Thanks for reading this far. If you have any questions or feedback, please ask in comments, happy to answer any query you may have or doubts.