Majority of the time when selecting data from the database, we want to narrow down the result set based on certain criteria. This is where a WHERE clause can help us.
We will be discussing the Where clause and the syntax to use it.
Where Clause Statements
Where clause specifies the criteria to narrow down.
We use the following syntax to use a Where clause.
Select [Column Names | *]
Where [Column Name] [operator] [criteria]
We narrow down our result set with the criteria.
We can use any number of criteria to further narrow down our result set by using the AND or OR condition, with the following syntax.
Select [Column Names | *]
Where [Column Name] [operator] [criteria]
AND | OR [Column Name] [operator] [criteria]
This will all make more sense when we actually make use of these query statements in SSMS.
Operator
The operators that we can use are:
=
<, >, >=, <=
Between (inclusive) - for dates and numbers.
An example of how we would construct a query statement using the Where clause is as follows:
Syntax:
Select firstName
From Person.Person
Where firstName = 'John'
When we have a criteria that is a string we need to enclose it in quotation marks. This can be either double quotes or single quotes, however remember you cannot mix the two.
'John" - This will be invalid.
In SSMS lets start using the Where clause in our query statements.
As we can see we get a bunch of results back with people whose first name is John. But this of course is too much data if we were looking for a specific John. We can narrow down the query statement even further by using adding more criteria.
This time we are looking for a person with the first name as John and the last name as Ford. We use the AND operator to help narrow down this distinction.
We have narrowed down the query to find the person with the first name as John and the last name as Ford. But what if we wanted to find the person with the first name of John or the last name of Ford.
In this case we use the OR operator:
This time we get the results back of every person with the first name of John or with the last name of Ford.
We can use as many criteria as we want.
We can now look at the other operators available to us. Let’s start with the More Than operator.
Back in SSMS, let’s create a new query window and experiment with the More Than operator. These operators are usually used with numbers and dates, however they can also be used with sting values.
Note that we pull all the specified data, however 2000 was not included in the result set. This is because we queried for more than 2000. If we want to include 2000 in the result set, we specify More Than or Equal to:
We can also use this syntax to see the result set that has a criteria Less Than 2000:
As we expected to see, we get the results of every data point less than 2000. If we want to include 2000 in the result set then we use Less Than or Equal to:
Between
The between criteria uses the startValue and endValue and returns the result set that is between those two values, including those two values.
For instance we want to see the data of Person.Person table with the BusinessEntityID between 2000 and 2021, we use the following syntax:
Select *
From Person.Person
Where BusinessEntityID between startValue and endValue
We can see this in action in SSMS.
We could have gotten the same result set by using the AND operator as well, lets see how we could have constructed it.
Between in a sense removes the need to use AND and it simplifies the query statement a bit.
Thats the basics on how we can narrow things in SQL, I hope you enjoyed this discussion, and for more on SQL you know Where to find me.
Top comments (1)
I am not to sure about that, I will need to look into that. The article seems to suggest it. Thanks for mentioning it