Sunday, February 20, 2022

How to use WHERE , LIKE, and BETWEEN in SQL for Filtering Rows? Example Tutorial

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 these 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.
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.

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.

Let's create a table and observe this. 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.

No comments:

Post a Comment