In this blog, we’ll discuss how to filter in SQL. We’ll explain and show some examples utilizing filtering conditions such as IN, NOT IN, LIKE, and NOT LIKE.
These filtering conditions are used to filter the output from the data. Instead of selecting them using multiple lines of commands, it will be easier for you to access the data.
Using IN And NOT IN Operators To Filter Data In SQL
In this example, we’ll be getting the data from IDs 1, 5 or 7. Most people would use OR conditions.
If you want to get the data from more IDs, you’ll have to write multiple OR conditions. However, you can use the IN operator to get the data you want instead of using OR conditions many times.
Using this filtering condition will give us the same output, which is the data from IDs 1, 5 or 7. On the contrary, if you want to extract the data from all except from the IDs 1, 5, and 7, you would most likely use the OR condition and <> (NOT EQUAL operator).
Instead of writing long commands, we can use the NOT IN operator.
With this filtering condition, you’ll extract the data except for IDs 1, 5, and 7.
Using these conditions will save you plenty of time, and at the same time, is a much better way to write instead of using multiple OR conditions.
Using LIKE and NOT LIKE Operators To Filter Data In SQL
LIKE and NOT LIKE are similar to the IN and NOT IN operators. Instead of extracting the data itself, it will get the part of a string. Keep in mind that you can use wildcards or special characters like per cent (%) and underscore (_).
The % means it will match all the records with the given string. If we match 86 and %, the first two digits should be 86. So, it’ll find any values that start with 86.
But if we place % on both sides of 86, it can come from any location. It can come from the start, the end, or the middle. So, it’ll find any values that have 86 at the start, middle, or end.Â
On the other hand, _ matches only 1 character. If you use 86 and _ (86_), it will look for any values that have 86 on the 1st and 2nd position like 860, 861, 862, and so on and so forth. If you use _ and 86 (_86), it will look for any values with 86 on the 2nd and 3rd positions.
In this example, we’ll use the LIKE operator to get the data that has the string, Mark. We simply need to put Mark in between % to have an output that contains Mark.Â
Similarly, if we don’t want to extract the data that contains Mark, we can use the NOT LIKE operator. Then put Mark in between %.
This time, the output will not contain Mark.
Using The SQL Filtering Conditions In SSMS
First, we’ll open our SSMS (SQL Server Management Studio).
Next, we’ll load the table that we’ll be using. Take note that the database that we’re using is adventureworks2012, which can be found on the Microsoft Documentation website.
Then, click Execute.
After that, you’ll see the output on the Results tab.
Then, we’ll filter the output based on PersonType. We’ll use the select distinct command. We’ll highlight the command and click Execute.
Then you’ll see the output showing the PersonType.
Next, we’ll extract the records from PersonType IN, or SP, or SC. For this one, we’ll use the OR condition. We’ll highlight the command, and click execute.
You’ll then see that the number of rows is 19,254.
However, instead of using the OR condition, we’ll use the IN operator. So, we’ll type the command and highlight it. Then click execute.
This will give us the same number of rows, which is 19,254.
If we want to extract all the data except for IN, SP, and SC, we’ll use the NOT IN operator. So let’s type the command and highlight it. Then click execute.
This will give us all the data except for the ones that are from IN, SP, and SC.
Next, we’ll use the LIKE operator to extract the data that contains Rob from the FirstName column. Let’s use the command, highlight it, and click Execute.
As a result, we’ll see that the output now contains Rob in the FirstName column. Since we used the %, it will show all data that have the string Rob.
Finally, we’ll use the NOT LIKE operator. In this example, we want to extract the data that doesn’t contain Rob. So we’ll type the code, highlight it, and then click Execute.
This will give us the output that doesn’t show any record that contains Rob in the FirstName column.
***** Related Links *****
Basic SQL Table Operations
Common SQL Functions: An Overview
SQL SELECT Statement: An Introduction
Conclusion
To summarize, we’ve discussed how to filter in SQL using IN, NOT IN, LIKE, and NOT LIKE operators. You’ve learned their usage and their differences, and in what situation you can use them.
These operators are more efficient than using the OR condition. You can use these operators to filter and extract the data you want from your database instead of using longer commands, so make sure to utilize these filtering conditions properly.
All the best,
Hafiz
