If you don't already have SQL Server Management Studio open, go ahead and open it now and select the
Battery Co database and we'll open a new query window.
In this lecture we're going to look at using the in between and light clauses with the select statement
to further refine our search parameters.
The in clause determines if a value is included in a list of values between.
Determines if a value is within a range, and like determines if a value matches a given string pattern.
Let's look at how they're used.
In a recent assignment, we used a Where clause with relational operators to filter results for employees
who lived in certain states.
It's okay if there are a few states that this can soon become unwieldly if we have to keep adding states
to the list, and that is where the in clause comes in.
The in clause allows you to determine if a value is in a list.
Here's an example of using the in clause with chars or var chars.
So this query gives us similar results, but is much more compact and easy to use.
It's very easy to just add a state to the list and it's much more compact.
And here's another example.
This time we're going to use the in clause with a list of numbers.
So you can use most data types, but everything in the list needs to be the same data type.
So here we're looking for a set of vendors where a vendor ID is in the list.
And here's a third example where we are looking for order dates within a list of date times.
Notice that dates in SQL are treated like strings and we can just execute this.
But what if we need to see if a value lies within a range?
This is where the between clause can come in handy.
Here's an example checking to see if an order date is between two dates.
This is a really efficient way to get this done.
We can also use the between clause to check to see if a price is between two price points.
And finally, we can even use the between statement using strings.
Here we are filtering a list of employees whose last name lies between Barker and Chen.
So this is a quick way to pick out a group of employees.
Now let's take a look at some examples using the like keyword.
The light clause determines if a value matches a string pattern, like uses wildcard characters to determine
if each string matches a pattern.
We use a percent sign to indicate any string of zero or more characters, an underscore to look for
a single character, and we can use square brackets to find if the string contains characters in a range
or in a set.
So let's take a look at a few examples.
Here's an example of using like to find a list of employees whose last name begin with the characters
ba and are any length longer than at least two characters.
So executing this, we only have two employees whose last name start with those letters.
Now, here's one that's a little tricky.
What we want to do is find inventory items that are triple A batteries.
But we don't want quadruple a batteries or double A batteries.
And this query will work.
You might think, well, we could just do this and look for a, a, a with a space at the end.
But that doesn't work.
And that is because the percent character can be any character, including that fourth A.
So four A's meets this condition and that's why it lists your four A batteries.
So if you're just looking for the string a, a a, then you have to make sure that you put your space
before and after it, just like is in the result set.
So sometimes when you're doing like you have to do a little bit of experimentation to find out the exact
string pattern that you want to use to get your desired results.
Now let's look at an example where we're looking for specific characters.
This query looks for customers whose last name contains an M at the very start of the name, and then
any character which is your underscore.
And then N is going to be the third letter of the last name.
And then there can be any length string with any characters after that.
So if we execute this, you can see that we have three customers that meet this.
If we don't have the percent sign, then we don't have any customers who have a last name that is exactly
three characters long, with an M at the front and an N at the back.
Here's an example.
Using ranges, this query will select all customers whose last name begins in letters A through F inclusive.
And so if we run this, then here are all our customer names.
And you can see that it is A through F inclusive.
So it includes the A's and includes the F's.
And for our final example we can reverse that using the not character.
And so this query will list all customers whose last name begins with letters that are not in the range
of A through F, and looking at these, they begin with G and go all the way to Z.
Next up is an assignment where you'll be doing some exercises using in between and like clauses with
your SQL select statements.
I'll see you in the next assignment.